Data missing in file downloaded using URLDownloadToFile

[optimusprime]

Hi All,

In VBA Using URLDownloadToFile I am downloading an excel file. The file is getting downloaded successfully but all data is missing. I mean a blank sheet is getting downloaded. Any idea what's the problem?

Thanks

[Kenneth Hobs]

Welcome to the forum!

Without seeing the code or a url with the file to download to test, it is hard to help. You might want to check out some examples here like: http://www.vbaexpress.com/forum/showthread.php?t=43118

[optimusprime]

Hi Kenneth,

I have tried the code in the link you gave me. The File gets downloaded successfully. But the data is missing (Sheet is Blank). What could have possibly gone wrong, Can you please help?

Thank You.

[snb]

Why don't you use:

[VBA]
Sub snb()
Workbooks.Open "http://www.snb-vba.eu/bestanden/__us...ercontrole.xls"
End Sub
[/VBA]

[optimusprime]

Hi Kenneth,

Here is the code I am using
[VBA]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

'~~> URL of the Path
strURL = "My URL to download the file goes here"

'~~> Destination for the file
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[/VBA]

The problem is that the file is getting downloaded, but an empty sheet. Data is missing. Any idea whats going wrong?

Thank You.

[Kenneth Hobs]

My code worked fine for me.

[VBA]Option Explicit

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

Declare Function DeleteUrlCacheEntry Lib "Wininet.dll" _
Alias "DeleteUrlCacheEntryA" ( _
ByVal lpszUrlName As String) As Long

Const ERROR_SUCCESS As Long = 0
Const BINDF_GETNEWESTVERSION As Long = &H10
Const INTERNET_FLAG_RELOAD As Long = &H80000000
Const FILE_ATTRIBUTE_TEMPORARY = &H100

Function DownloadFile(sSourceURL As String, _
sLocalFile As String) As Boolean
'Dim sLocalFile As String

'Download the file. BINDF_GETNEWESTVERSION forces
'the API to download from the specified source.
'Passing 0& as dwReserved causes the locally-cached
'copy to be downloaded, if available. If the API
'returns ERROR_SUCCESS (0), DownloadFile returns True.
DeleteUrlCacheEntry sSourceURL
DownloadFile = URLDownloadToFile(0&, _
sSourceURL, _
sLocalFile, _
BINDF_GETNEWESTVERSION, _
0&) = ERROR_SUCCESS
End Function

Sub Test_DownloadFile()
Dim fn As String, url As String
'fn = Environ("temp") & "\ken.pdf"
'url = "http://test2.hyattselectreports.lraqa.com/hyattselect_report_generation/PL1.php?r=aW5zcF9pZD0zNDMxNCZkaXNwbGF5PXRydWUmbGFuZ3VhZ2U9ZGVmYXVsdA=="
'DownloadFile url, fn
'Shell "cmd /c " & fn, vbNormalFocus

fn = Environ("temp") & "\ken.xls"
url = "http://home.aaahawk.com/~khobson/data/Excel/HelloWorld.xls"
DownloadFile url, fn
Workbooks.Open fn
End Sub

[/VBA]

[snb]

As did for me:

[VBA]
Sub snb()
Workbooks.Open "http://home.aaahawk.com/~khobson/dat...HelloWorld.xls"
End Sub
[/VBA]