PDA

View Full Version : Data missing in file downloaded using URLDownloadToFile



optimusprime
08-29-2012, 05:33 PM
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
08-29-2012, 06:43 PM
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
09-03-2012, 11:10 AM
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
09-03-2012, 12:31 PM
Why don't you use:


Sub snb()
Workbooks.Open "http://www.snb-vba.eu/bestanden/__userform_invoercontrole.xls"
End Sub

optimusprime
09-04-2012, 12:04 PM
Hi Kenneth,

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

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
09-04-2012, 01:10 PM
My code worked fine for me.

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

snb
09-05-2012, 12:39 AM
As did for me:


Sub snb()
Workbooks.Open "http://home.aaahawk.com/~khobson/data/Excel/HelloWorld.xls"
End Sub