VBA Download File from URL – using Excel Macro

Excel VBA To Download File from Website  URL

In this article you will get VBA code to download file from any website URL.

Consider your have a list of files to download from internet from different website URLs. This might be a Video, Music, any Media or document like Doc, Pdf, Xlss, Csv, etc.

Also if any file is updated periodically, then you have to download them at certain time interval for data analysis. In this case, we can go for manual option, assigning a computer operator to download these files periodically.

Otherwise, you can choose to go for automating the whole process.

Lets see how to progress with this automation by comparing manual steps involved in it.

1. Manual Download:
   • We have to collect and log the URL link for each file in some document.
   • Browse each website and
   • Click on download file option provided in each of these websites.
2. Automatic Download: Let’s make it simple with Excel.
   • Type the file list to be downloaded from internet and all URLs corresponding to those files into an Excel sheet.
   • Use the VBA Macro code suggested in this article to download file from internet.

Note: This option is to download file from a HTTP internet server. If you have to download from your LAN local share path, you can use the option to copy files from this article.

If the File has to downloaded from a FTP server and not a HTTP URL path, read the below suggested article.

Also Read: Download or upload files from FTP server

Using VBA to Download File From URL Web Server from Excel?

The code snippet explained in this article will work out, if you have the exact file path and URL from the internet server.

Make sure that the URL you are providing is not a HTML page instead it should end with the File name that you are going to download.

Also Read: To download HTML data of Webpage, refer this article.

We are going to use the Windows API command ‘URLDownloadToFile’ for this purpose.

'Declaration of Windows API Function
#If VBA7 And Win64 Then
    Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" _
      Alias "URLDownloadToFileA" ( _
        ByVal pCaller As LongPtr, _
        ByVal szURL As String, _
        ByVal szFileName As String, _
        ByVal dwReserved As LongPtr, _
        ByVal lpfnCB As LongPtr _
      ) As Long
    Private Declare PtrSafe Function DeleteUrlCacheEntry Lib "Wininet.dll" _
      Alias "DeleteUrlCacheEntryA" ( _
        ByVal lpszUrlName As String _
      ) 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
    Private Declare Function DeleteUrlCacheEntry Lib "Wininet.dll" _
      Alias "DeleteUrlCacheEntryA" ( _
        ByVal lpszUrlName As String _
      ) As Long
#End If


Sub File_Download_From_Website()
    'Declare Local Variables to be Used in this Sub Module.
    Dim InpUrl As String
    Dim OutFilePath As String
    Dim DownloadStatus As Long
    
    'Read Input Path for the File and Output File Destination Path
    InpUrl = ThisWorkbook.Sheets(1).Cells(1, 1)
    OutFilePath = ThisWorkbook.Sheets(1).Cells(2, 1)
    
    'Invoke API to download file from the website.
    DownloadStatus = URLDownloadToFile(0, InpUrl, OutFilePath, 0, 0)
    
    'Check return status from the API and Intimate the User accordingly.
    If DownloadStatus = 0 Then
        Application.Speech.Speak "File Downloaded. Check in this path: " & OutFilePath, True
        MsgBox "File Downloaded. Check in this path: " & OutFilePath
    Else
        Application.Speech.Speak "Download File Process Failed"
        MsgBox "Download File Process Failed"
    End If
End Sub

This API will get the URL to download the file from and the destination where the file has to be downloaded.

API Syntax for File Download:

The get a detailed explanation of this API function, please refer the MSDN Library. We have provided a quick reference on the Parameters used by this Function URLDownloadToFile.

HRESULT URLDownloadToFile(
  LPUNKNOWN pCaller,
  LPCTSTR szURL,
  LPCTSTR szFileName,
  _Reserved_  DWORD dwReserved,
  LPBINDSTATUSCALLBACK lpfnCB
);

This kind of code may be used in analysis like a Compan’s Annual Earning Report, Stock Quotes etc., which gets refreshed periodically. This code snippet will save a lot of time in download these files from website URL manually.

More Tips: Download Social Media Profile Data To your PC