URLDownloadToFile - Data missing in downloaded file

Question

Using URLDownloadToFile in VBA, I am trying to download a file. The problem is that a blank file is getting downloaded. Any idea why the data is missing?

Option Explicit 

Private Declare Function URLDownloadToFile Lib "urlmon" _ 
Alias "URLDownloadToFileA" (ByVal pCaller As Long, _ 
ByVal szURL As String, ByVal szFileName As String, _ 
ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long 

Dim Ret As Long 

Sub Sample()

Dim strURL As String 
Dim strPath As String 

strURL = "https://abc.abcabc.com/cmif-ku/reports/2012/byOwningEntity/Excel/myfilename.xls" 

strPath = "C:\Temp\myfilename.xls" 

Ret = URLDownloadToFile(0, strURL, strPath, 0, 0) 

If Ret = 0 Then 
    MsgBox "File successfully downloaded" 
Else 
    MsgBox "Unable to download the file" 
End If

End Sub

Two things `1)` Share the `strURL` that you are using. `2)` You are missing a "\" in `strPath` — Siddharth Rout, Sep 04 '12 at 22:31
"Blank file" means an actual empty Excel file or no file at all? — Tim Williams, Sep 04 '12 at 23:20
Is it possible that the returned content isn't an Excel file but either a redirect or some other type of content? — Tim Williams, Sep 04 '12 at 23:33
Hi Siddharth, I took this code from ur blog, good work, please help me fix it. I edited the strURL in above code. I cant give the original link as its confidential. — user1647155, Sep 05 '12 at 12:33
Tim, An excel sheet is getting downloaded, with no data. A blank excel sheet is getting downloaded instead of one with data. — user1647155, Sep 05 '12 at 13:25
Then it seems like you're using the wrong URL. What happens if you select Open in Excel and paste your URL into the "File Name" textbox? Or from the Immediate pane in the VB editor: `Workbooks.Open "yourURLhere"` ? — Tim Williams, Sep 06 '12 at 18:22

score 2 · Answer 1 · edited Dec 14 '14 at 19:39

I have a similar issue. I use the following code but got an "overflow" message:

Sub downloadFile()
    Dim targetFile As String, targetUrl As String, returnVal As Integer
    target = "http://www.ishares.com/us/products/239454/ishares-20-year-treasury-bond-etf/1395165510757.ajax?fileType=xls&fileName=iShares-20-Year-Treasury-Bond-ETF"
    strSavePath = "C:\testdownload.txt"
    returnVal = URLDownloadToFile(0, target, strSavePath, 0, 0)
    If returnVal = 0 Then
        Debug.Print "Download ok!"
    Else
        Debug.Print "Error"
    End If
End Sub

score 1 · Answer 2 · answered Sep 10 '16 at 18:25

1

you got an overflow because you used a integer to collect a long value. The urldownloadtofile returns a long value. If your download had succeeded you would have received a "0". Then your code would've worked.

answered Sep 10 '16 at 18:25

Nirmal

11
1
2

score 0 · Answer 3 · edited Sep 01 '23 at 15:26

Hope this may be helpful to others. Check the following:

Are you using MS Office 64 bit or 32 bit.
Declaration for 64 bit is

Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" _
      Alias "URLDownloadToFileA" ( _
        ByVal pCaller As LongPtr, _
        ByVal szURL As String, _
        ByVal szFileName As String, _
        ByVal dwReserved As LongPtr, _
        ByVal lpfnCB As LongPtr _
      ) As Long

While in 32 Office it is

Private Declare Function URLDownloadToFile Lib "urlmon" _
      Alias "URLDownloadToFileA" ( _
        ByVal pCaller As Long, _
        ByVal szURL As String, _
        ByVal szFileName As String, _
        ByVal dwReserved As Long, _
        ByVal lpfnCB As Long _
      ) As Long

The result variable must be of type "Long".
Target URL Path must be correct and target file or data must be there

URLDownloadToFile - Data missing in downloaded file

3 Answers3

Linked