# How to extract data from website to excel?



## guo1121 (Nov 9, 2007)

Hi,

I need to download some data from a website to excel automatically everyday. I wrote the following VBA code (macro). It opens an internet explorer and makes the selection. However, I don't know how to extract the data in the table to excel. Any help will be appreciated. To run the code, you need to add reference to MicroSoft Internet Controls.

Sub FillInForm()

'Reference MS Internet Controls

Dim IE As InternetExplorer
Set IE = New InternetExplorer

With IE
.Visible = True
.Navigate "http://www.nymex.com/settle_fut_otc.aspx"

'Wait until page is loaded. Warning for endless loop here!
Do Until IE.ReadyState = READYSTATE_COMPLETE: Loop

For Each x In .Document.all
If x.ID = "ctl00_ddlProducts" Then
x.onchange
x.Value = "B4"
End If
Next x
End With

End Sub


----------



## slurpee55 (Oct 20, 2004)

They have used Javascript very thoroughly to keep their page neat - unfortunately, it also prevents the page from displaying all the data. The download would be what you want, but it too lies hidden behind a javascript command.
I tried a mess of possibilities in order to see if I could see where the files were located, but to no avail.
So basically, you would need to interact with the page and select each product for each category and copy the text...would be far easier and quicker just to go there and download the data manually.


----------

