How do I download a file using VBA without Internet Explorer

Question

I need to download a CSV file from a website using VBA in Excel  The server also needed to authenticate me since it was data from a survey service   I found a lot of examples using Internet Explorer controlled with VBA for this  However  it was mostly slow solutions and most were also convoluted   Update  After a while I found a nifty solution using Microsoft XMLHTTP object in Excel  I thought to share the solution below for future reference

User · Answer

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

Sub Example()
    DownloadFile$ = "someFile.ext" 'here the name with extension
    URL$ = "http://some.web.address/" & DownloadFile 'Here is the web address
    LocalFilename$ = "C:\Some\Path" & DownloadFile !OR! CurrentProject.Path & "\" & DownloadFile 'here the drive and download directory
    MsgBox "Download Status : " & URLDownloadToFile(0, URL, LocalFilename, 0, 0) = 0
End Sub

Source

I found the above when looking for downloading from FTP with username and address in URL. Users supply information and then make the calls.

This was helpful because our organization has Kaspersky AV which blocks active FTP.exe, but not web connections. We were unable to develop in house with ftp.exe and this was our solution. Hope this helps other looking for info!

User · Answer

I was struggling for hours on this until I figured out it can be done in one line of powershell  invoke-webrequest -Uri  quot http   myserver Reports Pages ReportViewer aspx  2fClients 2ftest amp rs Format PDF amp rs ClearSession true amp CaseCode 12345678 quot  -OutFile  quot C  Temp test pdf quot  -UseDefaultCredentials  I looked into doing it purely in VBA but it runs to several pages  so I just call my powershell script from VBA every time I want to download a file  Simple

User · Answer

A modified version of above solution to make it more dynamic   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  Public Function DownloadFileA ByVal URL As String  ByVal DownloadPath As String  As Boolean     On Error GoTo Failed     DownloadFileA   False      As directory must exist  this is a check     If CreateObject  Scripting FileSystemObject   FolderExists CreateObject  Scripting FileSystemObject   GetParentFolderName DownloadPath     False Then Exit Function     Dim returnValue As Long     returnValue   URLDownloadToFile 0  URL  DownloadPath  0  0       If return value is 0 and the file exist  then it is considered as downloaded correctly     DownloadFileA    returnValue   0  And  Len Dir DownloadPath    gt  0      Exit Function  Failed  End Function

User · Answer

This solution is based from this website  http   social msdn microsoft com Forums en-US bd0ee306-7bb5-4ce4-8341-edd9475f84ad excel-2007-use-vba-to-download-save-csv-from-url  It is slightly modified to overwrite existing file and to pass along login credentials   Sub DownloadFile    Dim myURL As String myURL    https   YourWebSite com  your query parameters   Dim WinHttpReq As Object 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  C  file csv   2   1   no overwrite  2   overwrite     oStream Close End If  End Sub

User · Answer

A modified version of above to make it more dynamic   Public Function DownloadFileB ByVal URL As String  ByVal DownloadPath As String  ByRef Username As String  ByRef Password  Optional Overwrite As Boolean   True  As Boolean     On Error GoTo Failed      Dim WinHttpReq          As Object  Set WinHttpReq   CreateObject  Microsoft XMLHTTP        WinHttpReq Open  GET   URL  False  Username  Password     WinHttpReq send      If WinHttpReq Status   200 Then         Dim oStream         As Object  Set oStream   CreateObject  ADODB Stream           oStream Open         oStream Type   1         oStream Write WinHttpReq responseBody         oStream SaveToFile DownloadPath  Abs CInt Overwrite     1         oStream Close         DownloadFileB   Len Dir DownloadPath    gt  0         Exit Function     End If  Failed      DownloadFileB   False End Function

[api] How do I download a file using VBA (without Internet Explorer)

Examples related to api

Examples related to vba

Examples related to csv

Examples related to download