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.

score 66 · Accepted Answer · edited Apr 10 '19 at 10:02

66

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

edited Apr 10 '19 at 10:02

Rafiki

604
6
19

answered Jul 26 '13 at 09:13

Ole Henrik Skogstrøm

6,353
10
57
89

1

Thanks--very cool. My only issue is that then anybody who gets to the VBA of your file has your password. Any tips for getting around that or encrypting it in some way? Thanks again! – rryanp Apr 11 '14 at 03:31
2

No problem :) You are correct, it's not good practice to store passwords in your code. In Ruby I always use environment variables, you can probably do something similar in VBA. In excel you can encrypt files so that users can't se your code. I have never tried this but try this link: http://www.vbaexpress.com/forum/showthread.php?914-how-to-encrypt-vba-code-on-the-fly – Ole Henrik Skogstrøm Apr 11 '14 at 19:11
4

What‘s the “myURL = ...responseBody” (right before the If) for? Seems unnecessary… – Lucas Werkmeister Jun 17 '14 at 20:37
2

Can we directly open the oStream in excel using workbook.open ?? – ramses1592 Dec 02 '15 at 07:53
4

Thanks for this. Thought I'd add that if you have `Option Explicit` specified, you also need to declare oStream: `Dim oStream As Object` – Joe Oct 13 '17 at 09:56
Thanks for your post. Still, quite often it saves broken files (and, when I do it in a loop - those broken files usually have the same size, but not necessary). Any idea why this can happen? – sberezin Oct 26 '20 at 15:19

score 11 · Answer 2 · edited Feb 24 '15 at 20:43

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!

This is helpful, *but*, I had to add a reference to Microsoft WinHTTP Services to get this to work. (In the VBA editor: Tools/References/Microsoft WinHTTP Services, version 5.1). Don't know if that was obvious to other people or not. — DRC, May 27 '18 at 15:25
@DRC i know your comment is a year old at this point, but I wrote this almost 3 years prior. We had imported some other things in our scripts at the time, but im happy you got some help! — Cole Busby, Feb 15 '19 at 23:27

AndrewK · Answer 3 · 2019-12-27T00:06:22.137

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)) > 0
        Exit Function
    End If

Failed:
    DownloadFileB = False
End Function

When answering an old question, your answer would be much more useful to other StackOverflow users if you included some context to explain how your answer helps, particularly for a question that already has an accepted answer. See: [How do I write a good answer](https://stackoverflow.com/help/how-to-answer). — David Buck, Dec 27 '19 at 00:04

score 0 · Answer 4 · answered Jan 26 '21 at 14:10

I was struggling for hours on this until I figured out it can be done in one line of powershell:

invoke-webrequest -Uri "http://myserver/Reports/Pages/ReportViewer.aspx?%2fClients%2ftest&rs:Format=PDF&rs:ClearSession=true&CaseCode=12345678" -OutFile "C:\Temp\test.pdf" -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.

score 0 · Answer 5 · answered Nov 23 '21 at 19:09

Public Sub Test_DownloadFile()
 Dim URLStr As String, DLPath As String, UName As String, PWD As String, DontOverWrite As Boolean
 URLStr = "http.."
 DLPath = Environ("USERPROFILE") & "\Downloads\TEST.PDF"
 UName = ""
 PWD = ""
 DontOverWrite = False
 Call DownloadFile(URLStr, DLPath, UName, PWD, DontOverWrite)
End Sub

Public Sub DownloadFile(ByVal URLStr As String, ByVal DLPath As String, Optional ByVal UName As String, Optional ByVal PWD As String, Optional DontOverWrite As Boolean)
 On Error GoTo Failed

 Dim WinHttpReq As Object
 Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
 WinHttpReq.Open "GET", URLStr, False, UName, PWD
 WinHttpReq.send

If WinHttpReq.status = 200 Then
    Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    oStream.Write WinHttpReq.responseBody
    Dim OWrite As Integer
    If DontOverWrite = True Then
     OWrite = 1
    Else
     OWrite = 2
    End If
    oStream.SaveToFile DLPath, OWrite
    oStream.Close
    Debug.Print "Downloaded " & URLStr & " To " & DLPath
    Exit Sub
End If
Failed:
 Debug.Print "Failed to DL " & URLStr
End Sub

AndrewK · Answer 6 · 2019-12-27T00:08:11.253

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)) > 0)
    Exit Function

Failed:
End Function

how is this used? i ran it like Call DownloadFileA("mylink.csv", ProjectNetworkLoadPath), no errors and no file downloaded — lalachka, Jul 09 '21 at 09:43

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

6 Answers6

Linked

Related