Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Query, not capturing data
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Query, not capturing data
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 ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=151687 Microsoft Office Help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Query, not capturing data
I f you get this message respond back at the following webpage
http://www.thecodecage.com/forumz/ex...ring-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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Query, not capturing data
Thank you Joel, That definately did the trick.
Terry "Joel" wrote: I f you get this message respond back at the following webpage http://www.thecodecage.com/forumz/ex...ring-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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
capturing all of the data | Excel Worksheet Functions | |||
Web Query - Submitting Data and Capturing Result | Excel Discussion (Misc queries) | |||
Query Refresh capturing rows returned to display progress | Excel Programming | |||
Capturing query table errors | Excel Programming | |||
Capturing query table errors | Excel Worksheet Functions |