Download a File from the Internet

 To download a file from the Internet, one option is to use the URLDownloadToFile API, which looks something like this.

#If VBA7 Then
  Private Declare 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
#Else
  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
#End If

You could then use the above in a function like this.

lngResult = URLDownloadFile(0,URL,Filename,0,0) 

However, if you're like me and don't want to use API calls, you can also achieve the above goal by using a HTTPRequest class. 

Here's an example VBA subroutine to download a file from the Internet.

Public Sub DownloadFile(URL As String)
'thedbguy@gmail.com
'3/5/2021
'used to download a file from the Internet
'assumes filename is at the end of the URL
'file is saved in the current project folder
'usage: DownloadFile "http://www.accessmvp.com/thedbguy/img/shrek.jpg"

Dim objHTTP As Object
Dim FileByte() As Byte
Dim strFile As String
Dim intFile As Integer

intFile = FreeFile()
strFile = Mid(URL, InStrRev(URL, "/") + 1)

Set objHTTP = CreateObject("Microsoft.XMLHTTP")

With objHTTP
    .Open "GET", URL, False
    .Send
    If .Status = 200 Then
        FileByte = .responseBody
    End If
End With

Open CurrentProject.Path & "\" & strFile For Binary Lock Read Write As #intFile
    Put #intFile, , FileByte
Close #intFile

Set objHTTP = Nothing

End Sub

The above code does not include any error handlers, so you will have to add that part yourself. Also, the above code was not fully tested on all possible file types. It worked well for me when downloading image and PDF files though. Please let me know if you find a file type that this code was not able to handle.

As usual, thank you for reading and please let me know if you find this information useful.

Good luck with your project.