Excel VBA - Download CSV file from URL - boards.ie
Boards.ie uses cookies. By continuing to browse this site you are agreeing to our use of cookies. Click here to find out more x
Post Reply  
 
 
Thread Tools Search this Thread
09-01-2013, 15:22   #1
em2
Registered User
 
Join Date: Oct 2012
Posts: 12
Excel VBA - Download CSV file from URL

Hi,

I am trying to code a VBA macro in Excel that will automatically download the csv file when clicking on the CSV icon on the following website:

http://www.banxico.org.mx/SieInternet/consultarDirectorioInternetAction.do?accion=consultarCuadro&idCuadro=CF102&sector=6&locale=en

I have been able to download the page itself into Excel but not the actual csv file. Would anyone be able to help me or point me in the right direction?

Thanks
em2 is offline  
Advertisement
09-01-2013, 15:56   #2
srsly78
Registered User
 
Join Date: Oct 2010
Posts: 5,839
Well since you have it already in excel you can just go sheet .saveas(format_CSV) or something very similar.
srsly78 is offline  
09-01-2013, 16:06   #3
em2
Registered User
 
Join Date: Oct 2012
Posts: 12
Hi,

I only have the actual page which gives 3 days data but I need all the data in the CSV file which isn't displayed on the webpage...
em2 is offline  
09-01-2013, 16:13   #4
srsly78
Registered User
 
Join Date: Oct 2010
Posts: 5,839
Easy, just hack into the website and steal all their data so.

Serious answer: If the CSV file is not made available then you can't download it. What you can do is download a chunk each day and slowly aggregate these together.
srsly78 is offline  
09-01-2013, 16:23   #5
em2
Registered User
 
Join Date: Oct 2012
Posts: 12
The CSV is available to 'Open' or 'Save' if you click on the CSV link on the website, I just don't know how to code for this in Excel VBA so that a macro will do it automatically...
em2 is offline  
Advertisement
09-01-2013, 16:35   #6
srsly78
Registered User
 
Join Date: Oct 2010
Posts: 5,839
Ah right, well do this so: http://stackoverflow.com/questions/3...tp-post-in-vba

Just emulate whatever http request the save command is doing.
srsly78 is offline  
09-01-2013, 17:20   #7
em2
Registered User
 
Join Date: Oct 2012
Posts: 12
Not sure how to do this...
em2 is offline  
09-01-2013, 17:26   #8
srsly78
Registered User
 
Join Date: Oct 2010
Posts: 5,839
Copy and paste the vba. It shows you how to use the vba http library. You can do a http post the same as what clicking the save button does.

More useful stuff here: http://www.mrexcel.com/forum/excel-q...e-website.html
srsly78 is offline  
09-01-2013, 19:58   #9
em2
Registered User
 
Join Date: Oct 2012
Posts: 12
No luck with any of the links unfortunately, I still don't know how to apply the code to download the file.

This is the code I can see from the website:

"<inputname="formatoCSV"id="formatoCSV"type="image"src="/SieInternet/img/ConsultarDirectorio/formato_CSV.png"onclick="document.consultarDirectorioActionForm.target='_self'; return onClickConsultar() "hl_hint="return '<div class=\'hint\'>Export the selected series &lt;br&gt;in comma-separated values format (CSV).</div>'"onmouseover="hl_show(event); document.getElementById('formatoCSV').alt='';"alt="Export the selected series in comma-separated values format (CSV)."class="botonFormato">"

Apologies if I haven't posted this code correctly to this board!
em2 is offline  
Advertisement
09-01-2013, 20:06   #10
srsly78
Registered User
 
Join Date: Oct 2010
Posts: 5,839
What you are looking for is the http post request that happens when you click the save button. I don't think that is in the snippet you posted. This isn't my area of expertise, maybe a web developer could give you better advice.

Once you do find the post request however, you can use the vba stuff I linked earlier to download the file.
srsly78 is offline  
09-01-2013, 20:17   #11
em2
Registered User
 
Join Date: Oct 2012
Posts: 12
Thanks anyway for your posts.

It could be from here:

"<formname="consultarDirectorioActionForm"method="POST"action="/SieInternet/consultarDirectorioInternetAction.do?accion=consultarSeries"target="_blank"><tableborder="0"width="984"style="padding:0px 20px 0px 20px;background:white; margin: 0 auto"cellpadding="0"cellspacing="0"align="center"><tr><td><tableborder="0"cellpadding="0"cellspacing="0"width="100%"><tr><td><ahref="http://www.banxico.org.mx"><imgid="imagen-encabezado"src="http://www.banxico.org.mx/apps_header.png"height="74"width="1024"border="0"style="margin-bottom: 15px;">"
em2 is offline  
09-01-2013, 20:25   #12
srsly78
Registered User
 
Join Date: Oct 2010
Posts: 5,839
That looks more like it. So just put that http post stuff into the vba, something like below. You will have to finish it off yourself

To get stuff to appear formatted nicely on this forum you gotta put code tags around it. This means code and /code in square brackets.

Disclaimer: Not my area of expertise.

Code:
Dim WinHttpReq As Object
    Set WinHttpReq = CreateObject("WinHttp.WinHttpRequest.5.1")

    Dim strURL As String
    Dim StrFileName As String
    Dim FormFields As String
    
       strURL = "http://put.your.url.here/"
   

       WinHttpReq.Open "POST", strURL, False
      
       ' Set the header
      WinHttpReq.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
   
       FormFields = "_blank" ' this bit comes from the http post target bit I think
    
       WinHttpReq.Send FormFields
   
    
       ' Display the status code and response headers.
       MsgBox WinHttpReq.GetAllResponseHeaders
       
       ' The response should contain the file!!! So save this to disk. 
       SomeBinaryObject WinHttpReq.ResponseText

       SomeBinaryObject.save() ' i totally made this bit up
srsly78 is offline  
09-01-2013, 21:31   #13
em2
Registered User
 
Join Date: Oct 2012
Posts: 12
Still no joy with this code... Can anyone else please help?
em2 is offline  
09-01-2013, 21:34   #14
srsly78
Registered User
 
Join Date: Oct 2010
Posts: 5,839
I basically did 99% of it for you -.-

Which bit do you have trouble with?
srsly78 is offline  
09-01-2013, 21:48   #15
em2
Registered User
 
Join Date: Oct 2012
Posts: 12
All of it, I can't get past the internet page to download the csv file
em2 is offline  
Post Reply

Quick Reply
Message:
Remove Text Formatting
Bold
Italic
Underline

Insert Image
Wrap [QUOTE] tags around selected text
 
Decrease Size
Increase Size
Please sign up or log in to join the discussion

Thread Tools Search this Thread
Search this Thread:

Advanced Search



Share Tweet