Web Query, not capturing data

[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

 

[joel]

The data on the page is in a frame which the webpage gets from a second
URL. the code below goes to the 2nd URL and puts the data on Sheet1.


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

 

[Joel]

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

 

[tmacdonald]

Thank you Joel, That definately did the trick.

Terry