Monday, September 07, 2009 10:46 PMHi, 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/
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
Tuesday, September 08, 2009 8:10 AMThe 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 10:35 PMThanks 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!
Wednesday, September 09, 2009 12:09 AMAre 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 1:17 AMAh, 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:?
Thursday, December 03, 2009 8:21 PMVista 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.
Tuesday, January 26, 2010 2:40 PMJFK555, 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_SERVERbut 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