none
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!
    • Edited by JackH2 Monday, September 07, 2009 10:49 PM Clarification
    Monday, September 07, 2009 10:46 PM

Answers

  • 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
    • Marked as answer by JackH2 Wednesday, September 09, 2009 6:27 AM
    Tuesday, September 08, 2009 8:10 AM

All replies

  • 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
    • Marked as answer by JackH2 Wednesday, September 09, 2009 6:27 AM
    Tuesday, September 08, 2009 8:10 AM
  • 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!
    Tuesday, September 08, 2009 10:35 PM
  • 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.
    Wednesday, September 09, 2009 12:09 AM
  • 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:?
    Wednesday, September 09, 2009 1:17 AM
  • 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
    Thursday, December 03, 2009 8:21 PM
  • 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

    Tuesday, January 26, 2010 2:40 PM
  • Try appending the username and password to the URL. This does not always work, but is a quick and dirty way of getting it done.

    http://username:password@myurl.com/myfile.csv

    ..and thank you for the code JFK555 I've used it in my excel document to pull an excel doc off the internet (website requires username and password, so I've used my above example)
    • Edited by KahaliTao Wednesday, October 23, 2013 4:09 PM add detail
    Wednesday, October 23, 2013 4:07 PM
  • That's exactly what I was looking for. Good old Google.

    Is it possible to put the file back using a similar method after I downloaded it and made some alterations ?

    Kind Regards.

    Tuesday, October 29, 2013 10:47 AM
  • When you use the URLDownloadToFile Api, though we specify a path for destination path, the files is downloaded to the active directory. Check this Article in officetricks to know more about this. - http://officetricks.com/download-file-from-website-using-excel/

    Hope this helps.


    • Edited by Kumarapush Friday, July 25, 2014 8:18 AM Nil
    Friday, July 25, 2014 8:17 AM