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.

Answer 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