Excel 2007: use VBA to download & save CSV from URL?

Question

Hi, I'm trying to use Excel 2007's VBA to download a CSV from an HTTP URL, http://www.somesite.com/file.csv, and save it to a local address, e.g. C:\file.csv

I've tried the following two solutions:

URLDownloadToFile API - http://social.msdn.microsoft.com/Forums/en-US/isvvba/thread/beb6fa0e-fbc8-49df-9f2e-30f85d941fad/

http://social.msdn.microsoft.com/Forums/en-US/isvvba/thread/dfdebcfc-1796-4c1f-b897-724607f2a749/

For some reason, neither work for me -- they execute without brekaing, but do not download any files (I've tried GIFs, HTMLs, and CSVs).

Is there another way to download & save a web-based CSV to a local drive using only a VBA macro? Does someone have example code handy for an alternate method?

Thank you very much!

Answer 1

The following requires Internet Explorer but will download a file in VBA:

Dim myURL As String
myURL = "http://www.somesite.com/file.csv"

Dim WinHttpReq As Object
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", myURL, False
WinHttpReq.Send

myURL = WinHttpReq.ResponseBody
If WinHttpReq.Status = 200 Then
    Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    oStream.Write WinHttpReq.ResponseBody
    oStream.SaveToFile ("C:\file.csv")
    oStream.Close
End If

Answer 2

Thanks for the code.

I tried your solution as a Sub, and on the "oStream.SaveToFile ("C:\file.csv")" line, I get the error "Run-time error '3004': Write to file failed."

Do you know what's wrong and how to fix it? Thanks!

Answer 3

Are you using Vista? If so, you may not be able to save a file to 'C:\'. Try saving the file to your 'Documents' folder.

Answer 4

Ah, that does the trick!

Thank you very much!

Out of curiosity, is there a sensible reason Vista won't let VBA write directly to C:?

Answer 5

Vista restricts write-access to the root of the system drive (C: in most cases), and requires UAC.  I don't think VBA codes such as this one supports triggering UAC prompts and thus the write would fail.

---

beBoy

Answer 6

JFK555, thanks nice code.
I have another concern while using it since my file is stored on an intranet area which require authentification, so your code save the page of authentification and not the file itself.

Do you know a solution either to :
- sent the user/password through the VBA code ? I tried

WinHttpReq.SetCredentials "myusername", "mypassword", HTTPREQUEST_SETCREDENTIALS_FOR_SERVER

but it does not work

- use existing IE sessions which are already authenticated to save this file (my prefered solution if it exist) ?

Thanks for your help