Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Excel VBA - Download CSV file from URL

Comments

  • Registered Users Posts: 7,157 ✭✭✭srsly78


    Well since you have it already in excel you can just go sheet .saveas(format_CSV) or something very similar.


  • Registered Users Posts: 12 em2


    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...


  • Registered Users Posts: 7,157 ✭✭✭srsly78


    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.


  • Registered Users Posts: 12 em2


    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...


  • Registered Users Posts: 7,157 ✭✭✭srsly78


    Ah right, well do this so: http://stackoverflow.com/questions/3963475/http-post-in-vba

    Just emulate whatever http request the save command is doing.


  • Advertisement
  • Registered Users Posts: 12 em2


    Not sure how to do this...


  • Registered Users Posts: 7,157 ✭✭✭srsly78


    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-questions/67123-visual-basic-applications-save-file-website.html


  • Registered Users Posts: 12 em2


    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 <br>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!


  • Registered Users Posts: 7,157 ✭✭✭srsly78


    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.


  • Registered Users Posts: 12 em2


    Thanks anyway for your posts.

    It could be from here:

    "<form name="consultarDirectorioActionForm" method="POST" action="/SieInternet/consultarDirectorioInternetAction.do?accion=consultarSeries" target="_blank"><table border="0" width="984" style="padding:0px 20px 0px 20px;background:white; margin: 0 auto" cellpadding="0" cellspacing="0" align="center"><tr><td><table border="0" cellpadding="0" cellspacing="0" width="100%"><tr><td><a href="http://www.banxico.org.mx">&lt;img id="imagen-encabezado" src="http://www.banxico.org.mx/apps_header.png&quot; height="74" width="1024" border="0" style="margin-bottom: 15px;">"


  • Advertisement
  • Registered Users Posts: 7,157 ✭✭✭srsly78


    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 :p

    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.
    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
    


  • Registered Users Posts: 12 em2


    Still no joy with this code... Can anyone else please help?


  • Registered Users Posts: 7,157 ✭✭✭srsly78


    I basically did 99% of it for you -.-

    Which bit do you have trouble with?


  • Registered Users Posts: 12 em2


    All of it, I can't get past the internet page to download the csv file


  • Registered Users Posts: 12 em2


    All sorted now thanks with using a Data Connection, thanks for all your help.


  • Registered Users Posts: 2,021 ✭✭✭ChRoMe


    em2 wrote: »
    All sorted now thanks with using a Data Connection, thanks for all your help.

    Please post the full code in case anyone googles this page in the future and is having a similar problem.

    Obligatory

    wisdom_of_the_ancients.png


  • Registered Users Posts: 12 em2


    I didn't use VBA code for the connection part. One of the guys I work with sent me an iqy program that connects to the website to download the information, when the output file is saved, then the rates are refreshed from the macro. I don't know how to get the code from this iqy program.


  • Registered Users Posts: 2,021 ✭✭✭ChRoMe


    em2 wrote: »
    I didn't use VBA code for the connection part. One of the guys I work with sent me an iqy program that connects to the website to download the information, when the output file is saved, then the rates are refreshed from the macro. I don't know how to get the code from this iqy program.

    No worries, that further bit of explanation should be enough to help anyone looking at this in the future :)


Advertisement