Downloading Files through VBA

[sassriverrat]

I'm borrowing these two code from another user on here.

I'm trying to download some smaller files (not overly big) via a very slow to slow satellite connection (wx dependent). The first code (FileAPI) through a "Download File Process Failed" and the second code broke at "WinHttpReq.send"

Latency 626ms
download .35-.76mb/s
upload bursts of 2.5mb/s

Any ideas why or is there a better way to do this?
Code to be modified once it works on this first link....
Thanks!

Option ExplicitDeclare PtrSafe 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


Sub DownloadFileAPI()
Dim strURL As String
Dim LocalFilePath As String
Dim DownloadStatus As Long


    strURL = "https://nauticalcharts.noaa.gov/publications/coast-pilot/files/cp1/CPB1_WEB.pdf"
    LocalFilePath = "C:\Users\bridge\Desktop"
    DownloadStatus = URLDownloadToFile(0, strURL, LocalFilePath, 0, 0)
    If DownloadStatus = 0 Then
        MsgBox "File Downloaded. Check in this path: " & LocalFilePath
    Else
        MsgBox "Download File Process Failed"
    End If
End Sub




Sub DownloadFile()
Dim WinHttpReq As Object
Dim oStream As Object
Dim myURL As String
Dim LocalFilePath As String


myURL = "https://nauticalcharts.noaa.gov/publications/coast-pilot/files/cp1/CPB1_WEB.pdf"
LocalFilePath = "C:\Users\bridge\Desktop"


Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", myURL, False, "", ""  '("username", "password")
WinHttpReq.send


If WinHttpReq.Status = 200 Then
    Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    oStream.Write WinHttpReq.responseBody
    oStream.SaveToFile LocalFilePath, 2 ' 1 = no overwrite, 2 = overwrite
    oStream.Close
End If
End Sub

 

[bobsan42]

As far as I can tel both methods work fine. No debug messages.
You only mistake so far is that you must provide a local filename with the path, for example:

...
    LocalFilePath = Environ("Userprofile") & "\Desktop\" & "222.pdf"
...

 

[sassriverrat]

So I did

LocalFilePath = Environ("Bridge") & "\Desktop\" & "Coast Pilot 1.pdf"

where Coast Pilot 1 is to be the name of the file. VBA goes into a "loading" state that then becomes a not responding....could this be if the .pdf is wrong or could it be the slow internet? Nothing showing up on my desktop

 

[sassriverrat]

It actually downloaded this time. however, i don't know where- not to be found on the desktop....nor downloads, documents, or the other usual spots. Ideas?

 

[sassriverrat]

Or I get it continuously failing

 

[bobsan42]

For me it worked the first time - no problems.
but I believe you made a mistake. USERPROFILE is an environment variable which contains the path to your user profile folder (usually C:\users\bridge or whatever your username is).
So you should not change it. Use this:

    LocalFilePath = Environ("Userprofile") & "\Desktop\" & "222.pdf"

or simply hardcode it to see if it works at all, or the download fails:

    LocalFilePath = "C:\[COLOR=#333333]Coast Pilot 1[/COLOR].pdf"

Environ("Bridge") will return an empty string if you don't have an environment variable called BRIDGE.
Open a command window (cmd.exe) and type SET then press Enter to see all defined environment variables.

 

[sassriverrat]

very interesting in the download- so I took the original code and went to another machine on the same network (environ "general" on this one) and it was slow- per internet) but worked perfectly. I checked the first machine just to make sure I wasn't brain farting- it's "bridge"

So now creating a series of macros for this- I have roughly 25 macros (25 different downloads) to do.

So next question-
1. Do you know how to get excel to timestamp when it runs this download?
2. I want to do weekly updates of this (to become these) downloads. Each week, because they have the same thing, I'd like to keep the same name. I'd also like to make sure the old file isn't deleted until the new one is fully downloaded (in case the download fails, for example). One idea I had was possibly to have excel create a new folder each week with week#/52 and then place the most recent download in the newest file but I don't need all the extra downloads. Might you have a better approach?

Note there's an update date on the website these are extracted from but I'm not sure how Excel could pull that date: https://nauticalcharts.noaa.gov/publications/coast-pilot/index.html

Thank you for your help!

 

[bobsan42]

download the file adding date and time to the filename:

dim tstamp as string
tstamp = Format(Now,"yyyymmdd hhnnss")
LocalFilePath = Environ("Userprofile") & "\Desktop\" & tstamp & " Coast pilot 1.pdf"

once the download completes successfully you can rename the file, delete the old one, etc.

 

[sassriverrat]

Now this will only update when the code runs, correct? It won't update every time the sheet runs.

Thank you sir!

 

[bobsan42]

Correct.
You are welcome