Excel VBA To Download File from Website Automatically
Consider we have a list of files (like Video or Music or Tutorial Files) to download from internet from different website.
Or we have a list of files which are updated periodically and we have to download them at certain time interval for analysis. In this case, we can go for manual option, assigning a computer operator to download these files periodically or we 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
How To Download File From Web Server with VBA?
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.
123456789101112131415161718192021222324252627'Declaration of Windows API FunctionPrivate 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 LongSub File_Download_From_Website()'Declare Local Variables to be Used in this Sub Module.Dim InpUrl As StringDim OutFilePath As StringDim DownloadStatus As Long'Read Input Path for the File and Output File Destination PathInpUrl = 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 ThenApplication.Speech.Speak "File Downloaded. Check in this path: " & OutFilePath, TrueMsgBox "File Downloaded. Check in this path: " & OutFilePathElseApplication.Speech.Speak "Download File Process Failed"MsgBox "Download File Process Failed"End IfEnd 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.1234567HRESULT URLDownloadToFile(LPUNKNOWN pCaller,LPCTSTR szURL,LPCTSTR szFileName,_Reserved_ DWORD dwReserved,LPBINDSTATUSCALLBACK lpfnCB);
When we have a huge list of files to be downloaded from a website or if we have to periodically download file from website for analysis (like a Compan’s Annual Earning Report, Stock Quotes etc., which gets refreshed periodically), we can use this option to automate the downloading of file.
- Manual Download:
Download File from Website Using Excel