Downloading Files through VBA

sassriverrat

Well-known Member
Joined
Oct 4, 2018
Messages
645
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!

Code:
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
 
Last edited:

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,364
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:
Code:
...
    LocalFilePath = Environ("Userprofile") & "\Desktop\" & "222.pdf"
...
 

sassriverrat

Well-known Member
Joined
Oct 4, 2018
Messages
645
So I did
Code:
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

Well-known Member
Joined
Oct 4, 2018
Messages
645
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

Well-known Member
Joined
Oct 4, 2018
Messages
645

ADVERTISEMENT

Or I get it continuously failing
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,364
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:
Code:
    LocalFilePath = Environ("Userprofile") & "\Desktop\" & "222.pdf"
or simply hardcode it to see if it works at all, or the download fails:
Code:
    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.
 
Last edited:

sassriverrat

Well-known Member
Joined
Oct 4, 2018
Messages
645

ADVERTISEMENT

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!
 
Last edited:

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,364
download the file adding date and time to the filename:
Code:
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

Well-known Member
Joined
Oct 4, 2018
Messages
645
Now this will only update when the code runs, correct? It won't update every time the sheet runs.

Thank you sir!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,962
Messages
5,599,065
Members
414,281
Latest member
Engjamal2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top