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.

Solution 1

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

Solution 2

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!

Solution 3

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

Comments

[rryanp]

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!

[Ole Henrik Skogstrøm]

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: vbaexpress.com/forum/…

[Lucas Werkmeister]

What‘s the “myURL = ...responseBody” (right before the If) for? Seems unnecessary…

[ramses1592]

Can we directly open the oStream in excel using workbook.open ??

[Joe J]

Thanks for this. Thought I'd add that if you have Option Explicit specified, you also need to declare oStream: Dim oStream As Object

[DRC]

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.

[Cole Busby]

@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!

[David Buck]

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.

[sberezin]

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?

[lalachka]

how is this used? i ran it like Call DownloadFileA("mylink.csv", ProjectNetworkLoadPath), no errors and no file downloaded