VBA Code for downloading data from a website

odemann · July 23rd, 2010, 8:43 am

Hi all,I am looking into automating a manual process where I go to a website and then copy paste some tables into Excel.Who can help and provide a VBA code that does this for me ?Many thanks in advance.Kim

Cuchulainn · July 23rd, 2010, 9:15 am

Here is a pointer:One way is to use .NET Request/Response classes for HTTP and FTP so that you get a stream of data that you can process. With C# this is possible.Using VBA, .NET almost certainly has COM wrapper which you should be able to call from VBA.One nice option is to write _all_ the app code in C# and call it as an exported COM object (using COM Callable Wrappers) from VBA. This might also have better performance as well.//On the issue of actually pasting data, I do not know if there are ActiveX and Web Browser controls that allow you to do this. But I think the objective of the project is to do away with 'manual intervention'.

odemann · July 23rd, 2010, 11:44 am

Hi Cuchulainn,I was actually hoping just to use VBA as I am not acquainted with C++.Cheers

Cuchulainn · July 23rd, 2010, 12:42 pm

Hi odeman,C# actually, which is easier than C++ But I would expect MS to provide an interop COM interface to the library so that you can use it from VBA as well. This is in principle not a problem but I have not looked into it for this particular case. edit: had a look at VBA addins. There is no COM wrapper for the HTTP/FTP.

newbee · July 26th, 2010, 12:29 pm

Depending on the page, you can just record the macro, and then edit the code.Yes, this opens up the page, so it's not the best way, but if it's something simple it does the job.

odemann · July 26th, 2010, 12:41 pm

Hi newbee,Thanks for this that is really helpful. I thought if you take actions outside of Excel it does not work with the recorder ?I will certainly try this.CheersKim

MHill · July 28th, 2010, 9:11 am

This may work for you. In Excel, select 'Data' menu, 'Import External Data', 'New Web Query'. Set address to http://uk.finance.yahoo.com/q/hp?s=%5EFTSE (or whatever address).Click 'Go', and then click whichever yellow & black arrow points to your table. The arrow will turn into a green tick. Click 'Import'. Choose your data destination & click OK.You should then be able to record a macro eg:Range("A1").QueryTable.Refresh BackgroundQuery:=Falsewhere "A1" was the data destination.

odemann · July 28th, 2010, 10:21 am

Hi MHill,That is an excellent solution! Is there a way to also open and copy over the entire .CSV file that is offert at Yahoo!I am looking at several web sources where some of the will be .CSV files that update on a daily basis and some will be http based.Thanks a lot for your input. Much appreciated.Kim

odemann · July 28th, 2010, 2:20 pm

Hi MHill,Here is a concrete case:I would like to download gas flow data from this link: http://transparency.eon-gastransport.co ... ur=en-GBAs an example we can chose "Bocholtz" and "last 31 days".How can I automate this data download ?Any help is much appreciated.Kim

Hansi · July 28th, 2010, 5:07 pm

QuoteOriginally posted by: odemannHi MHill,Here is a concrete case:I would like to download gas flow data from this link: http://transparency.eon-gastransport.co ... ur=en-GBAs an example we can chose "Bocholtz" and "last 31 days".How can I automate this data download ?Any help is much appreciated.KimFor that one you'll need to send the full web query, get the response and then initiated the download just like you would manually since they don't provide a per option download link. Basically you need to break down the communication between your browser and the site and recreate it so that the right file can be downloaded. This can be done in VBA by exposing the correct DLLs and then writing out the whole query as one big string but it's pretty cumbersome. What I'd recommend instead is to use C# and make it into an XLL to be called from Excel. Much much much easier to do this in C#/.net rather then through VBA. Same basically goes for non-csv enabled content where the result response would need to be parsed for the info (or use the web query method above, which is the same except Excel does the work for you).Now if the csv linking was static or or dynamic based on query (date-referencedays.csv etc) then doing this in VBA would be fine but for what you want, I recommend C#/.Net and it should be easy to set up. C# is in many ways simpler than VBA and exposing the plugin to Excel via, straight interop xll, xlw, exceldna, Addin Express or one of the other C# to Excel plugin makers should be trivial.

brbred · July 29th, 2010, 12:52 pm

Hi, I made a small prototype excel file using InternetExplorer.

MHill · July 29th, 2010, 1:35 pm

New toy to play with! I like it!

odemann · July 29th, 2010, 1:51 pm

Good stuff ! Many thanks for your help brbred !

nicolasito · July 29th, 2010, 2:58 pm

I think the following spreadsheet may give you some good hints: http://www.rnfc.org/rhapsody/class/summ ... ltmFurther, this report may help you as well: http://www.rnfc.org/rhapsody/class/wint ... ts.pdfNico