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.
- 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.
- 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.
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.