Web Query, not capturing data

  • Thread starter Thread starter tmacdonald
  • Start date Start date
T

tmacdonald

Ok, here's my issue I need to download data from a website
(http://nymex.greenfutures.com/markets/rj-csf.html), in the past i would
download/link the file to a csv file or an MS Excel workbook then export the
needed data. I believe that it is the fact that this page utilizes XHTML.

How can I collect the data off the page so I can put it into Excel

Any help would be welcomed.

If i have posted this in the wrong forum please let me know.

Terry
 
I f you get this message respond back at the following webpage

http://www.thecodecage.com/forumz/excel-vba-programming/151687-web-query-not-capturing-data.html

The microsoft help is also posted at WWW.THECODECAGE.com

The microsoft webpage often stops sending e-mail message out. I responded
to your request on friday but apparently you didn't get notified. The
webpage data is inside a frame that gets loaded from a 2nd URL. You can't
get download the data from the 1st URL you have to go to the 2nd URL to
actual extract data. the code below will automatically get the data.


Sub greenfutures()

URL = "http://nymex.greenfutures.com/markets/rj-csf.html"

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True


'get web page
IE.Navigate2 URL
Do While IE.readystate <> 4 Or _
IE.busy = True
DoEvents
Loop

Set IFrame = IE.document.getelementsbytagname("IFRAME")

src = IFrame.Item(0).src


'get web page
IE.Navigate2 src
Do While IE.readystate <> 4 Or _
IE.busy = True
DoEvents
Loop

'wait until all the data is returned.
Do
Set Table = IE.document.getelementsbytagname("Table")
Set Data = Table(1)
Loop While Data Is Nothing

With Sheets("sheet1")
RowCount = 1
For Each IRow In Data.Rows
ColCount = 1
For Each cell In IRow.Cells
..Cells(RowCount, ColCount) = cell.innertext
ColCount = ColCount + 1
Next cell
RowCount = RowCount + 1
Next IRow
End With

IE.Quit
End Sub
 
Back
Top