My first time posting, hopefully someone who is better at VBA than me is willing to help me out...
I know there are a lot of forum topics out there about URLDOwnloadToFile, but I haven't found anything addressing the issue I'm having. The code is running quite happily without any errors, but it isn't downloading the file. Without any errors I'm having a difficult time diagnosing the issue. I'm getting a very odd return value, however. Here's my code...
Purpose of this code is to retrieve a CSV file from a website depending on the location the user selects (thus the random select case block with only one option so far).
What I've figured out so far:
-I copied the value of myLink into a webbrowser to make sure that was correct, and it downloaded properly.
-verfied my file path by copying and pasting into windows explorer, it came up
-tried running it at home in case it was a firewall issue (can't change the firewall @ work), still didn't work with firewall off
-oddest: the value of done is -2146697203, which quite befuddles me
-set the return type as string rather than as long since the microsoft documentation seems to indicate a string will be returned, but this just caused excel to crash.
Possible issue source?:
I looked at Microsoft's documentation of this library function here, and wonder if the issue is with parameter pCaller, the rest seem fairly straightforward. This code is being called by a subroutine which is called from the click event of an activex control. I've been trying to wrap my head around the description of pCaller and just have no idea what to do with it besides set it to zero like all the examples I've found online.
Other info:
Excel 2007
Windows 7 professional @ work, Windows 7 home @ home
Any help would be much appreciated, I've been puzzling over this for the better part of a day with no progress and no idea what else to try.
~Mathchick
I know there are a lot of forum topics out there about URLDOwnloadToFile, but I haven't found anything addressing the issue I'm having. The code is running quite happily without any errors, but it isn't downloading the file. Without any errors I'm having a difficult time diagnosing the issue. I'm getting a very odd return value, however. Here's my code...
Code:
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
Function downloadWeatherData(location As String)
Dim done
Dim myLink As String
Dim locationNum As String
Select Case location
Case "AK - BARROW W POST-W ROGERS ARPT [NSA - ARM]"
locationNum = "723230"
End Select
myLink = "http://rredc.nrel.gov/solar/old_data/nsrdb/1991-2005/data/tmy3/" & locationNum & "TY.csv"
done = URLDownloadToFile(0, "myLink", "C:\Users\***myname****\Documents\DownloadTest", 0, 0)
'Test.
If done = 0 Then
MsgBox "File has been downloaded!"
Else
MsgBox "File not found!"
End If
End FunctionPurpose of this code is to retrieve a CSV file from a website depending on the location the user selects (thus the random select case block with only one option so far).
What I've figured out so far:
-I copied the value of myLink into a webbrowser to make sure that was correct, and it downloaded properly.
-verfied my file path by copying and pasting into windows explorer, it came up
-tried running it at home in case it was a firewall issue (can't change the firewall @ work), still didn't work with firewall off
-oddest: the value of done is -2146697203, which quite befuddles me
-set the return type as string rather than as long since the microsoft documentation seems to indicate a string will be returned, but this just caused excel to crash.
Possible issue source?:
I looked at Microsoft's documentation of this library function here, and wonder if the issue is with parameter pCaller, the rest seem fairly straightforward. This code is being called by a subroutine which is called from the click event of an activex control. I've been trying to wrap my head around the description of pCaller and just have no idea what to do with it besides set it to zero like all the examples I've found online.
Other info:
Excel 2007
Windows 7 professional @ work, Windows 7 home @ home
Any help would be much appreciated, I've been puzzling over this for the better part of a day with no progress and no idea what else to try.
~Mathchick
Last edited:
