Solved: Downloading Excel File from Website

[Nick_London]

Hi,

Previously on this site I was kindly helped by a couple of people to download a txt file from a website from within excel. The code is below and works fine.

[VBA]Public Const FilePathX As String = "C:\"
Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" _
(ByVal pCaller As Long, _
ByVal szURL As String, _
ByVal szFileName As String, _
ByVal dwReserved As Long, _
ByVal lpfnCB As Long) As Long[/VBA]

URLDownloadToFile 0, "http://www.federalreserve.gov/releas...st/h6hist9.txt", FilePathX & "exampledata.txt", 0, 0

Does anyone know what the syntax should be to download a data file in excel format from the OECD website?

For example pasting the below line into web broswer takes you to data table that I want to download as an excel file from within excel.

http://stats.oecd.org/wbos/Index.asp...MEI_CLI&lang=e

There is way export the data manually as a excel file (or csv) online by clicking on the export excel icon. But I'm struggling to work out what it should be if I want to go straight to the excel file.

Thanks,

Nick

[lucas]

Maybe just grab the table: see attached

[Kenneth Hobs]

Since they are using JavaScript, SendKeys() would be the only way to Export Excel. If you have Vista, you will have to disable UAC if you want to pursue that route.

[Nick_London]

Gents,

Thanks for your suggestions. Lucas's solution will work for me. Just one further queston. In order to update the table can I just refresh the query or would I have to run the macro from scratch?

Thanks,

Nick

[Nick_London]

Hello All,

I tried downloading the table using the webquery macro below but it no longer seems to work. But when you paste the link onto the website it takes you to the right page and table? Any ideas why it's not working anymore?

Thanks,

Nick

http://stats.oecd.org/wbos/Index.asp...MEI_CLI&lang=e", _

Sub gethtmltable()
Dim objWeb As QueryTable
Dim sWebTable As String
'You have to count down the tables on the URL listed in your query
'This example shows how to retrieve the 2nd table from the web page.
sWebTable = 10
'Sets the url to run the query and the destination in the excel file
'You can change both to suit your needs
Set objWeb = ActiveSheet.QueryTables.Add( _
Connection:="URL;http://stats.oecd.org/wbos/Index.aspx?datasetcode=MEI_CLI&lang=e", _
Destination:=Range("A1"))

With objWeb

.WebSelectionType = xlSpecifiedTables
.WebTables = sWebTable
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Set objWeb = Nothing
End Sub

[lucas]

You have to get the right table:

[VBA]sWebTable = 5[/VBA]

[Nick_London]

Hello Lucas,

Changing the line: sWebTable = 10 to sWebTable = 5 now works.

But I'm slightly confused - a couple of questions.

First, why has the table number changed from 10 to 5? The query with the 10 worked before, I would assumed the table number would stay the same.

Second how did you work out the table number was 5 or 10 the first time? Is it easy to work out?

Thanks,

Nick

[lucas]

Hi Nick, I can only assume that the website made changes that changed the table number and it was just trial and error this time as I was in a hurry. I just started with a 1 and worked up until I found your table.

You can look at the source code and figure it out by finding each instance of <Table>

Just right click on the page and select "view source"