Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Querytables WebTables equivalent for InternetExplorer.Application.Document.all()="HTMLT able"
I would like to use InternetExplorer.Application.Document.all()
="HTMLTable" to retrieve tables on web pages, however I am interested in specific tables. With Querytables you could use .WebSelectionType = xlSpecifiedTables and .WebTables = "1,2" to designate which tables to retrieve. Anyway to do this with using the other method? I prefer to not use querytables as this loads the data into a spreadsheet, which I really do not need; I am trying to use excel to convert web page tables into a flatfile text file. I'd like to keep the data in memory. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Querytables WebTables equivalent for InternetExplorer.Application.
See the code below. A table is a tag which you can get wtih
getelementsbytagname. Tables is an array starting at 0. The query table number starts at 1 so you have to adjust the table number by 1. To get items in the table use rows and cells like below. Sub WebQuery() URL = " http://sg.finance.yahoo.com" 'use BN4.SI StockName = InputBox("Enter Stock Initials : ") Request = "/q/hp?s=" Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True 'get web page IE.Navigate2 URL & Request & StockName Do While IE.readystate < 4 or IE.Busy = True DoEvents Loop Set Table = IE.document.getelementsbytagname("Table") RowCount = 1 For Each Row In Table(23).Rows Colcount = 1 For Each cell In Row.Cells MyStr = cell.innertext For i = 1 To Len(MyStr) Range("A" & RowCount) = Mid(MyStr, i, 1) Range("B" & RowCount) = Asc(Mid(MyStr, i, 1)) RowCount = RowCount + 1 Next i Cells(RowCount, Colcount) = cell.innertext Next cell RowCount = RowCount + 1 Next Row End Sub " wrote: I would like to use InternetExplorer.Application.Document.all() ="HTMLTable" to retrieve tables on web pages, however I am interested in specific tables. With Querytables you could use .WebSelectionType = xlSpecifiedTables and .WebTables = "1,2" to designate which tables to retrieve. Anyway to do this with using the other method? I prefer to not use querytables as this loads the data into a spreadsheet, which I really do not need; I am trying to use excel to convert web page tables into a flatfile text file. I'd like to keep the data in memory. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Querytables WebTables equivalent for InternetExplorer.Application.
much thanks
On Aug 23, 10:49*am, Joel wrote: See the code below. *A table is a tag which you can get wtih getelementsbytagname. *Tables is an array starting at 0. The query table number starts at 1 so you have to adjust the table number by 1. *To get items in the table use rows and cells like below. Sub WebQuery() URL = "http://sg.finance.yahoo.com" 'use BN4.SI StockName = InputBox("Enter Stock Initials : ") Request = "/q/hp?s=" Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True 'get web page IE.Navigate2 URL & Request & StockName Do While IE.readystate < 4 or IE.Busy = True * *DoEvents Loop Set Table = IE.document.getelementsbytagname("Table") RowCount = 1 For Each Row In Table(23).Rows * *Colcount = 1 * *For Each cell In Row.Cells * * * MyStr = cell.innertext * * * *For i = 1 To Len(MyStr) * * * * * Range("A" & RowCount) = Mid(MyStr, i, 1) * * * * * Range("B" & RowCount) = Asc(Mid(MyStr, i, 1)) * * * * * RowCount = RowCount + 1 * * * Next i * * * Cells(RowCount, Colcount) = cell.innertext * *Next cell * *RowCount = RowCount + 1 Next Row End Sub " wrote: I would like to use *InternetExplorer.Application.Document.all() ="HTMLTable" to retrieve tables on web pages, however I am interested in specific tables. With Querytables you could use * *.WebSelectionType = xlSpecifiedTables and * * .WebTables = "1,2" to designate which tables to retrieve. *Anyway to do this with using the other method? I prefer to not use querytables as this loads the data into a spreadsheet, which I really do not need; I am trying to use excel to convert web page tables into a flatfile text file. I'd like to keep the data in memory.- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Querytables WebTables equivalent for InternetExplorer.Application.
On Aug 23, 1:02*pm, wrote:
much thanks On Aug 23, 10:49*am, Joel wrote: See the code below. *A table is a tag which you can get wtih getelementsbytagname. *Tables is an array starting at 0. The query table number starts at 1 so you have to adjust the table number by 1. *To get items in the table use rows and cells like below. Sub WebQuery() URL = "http://sg.finance.yahoo.com" 'use BN4.SI StockName = InputBox("Enter Stock Initials : ") Request = "/q/hp?s=" Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True 'get web page IE.Navigate2 URL & Request & StockName Do While IE.readystate < 4 or IE.Busy = True * *DoEvents Loop Set Table = IE.document.getelementsbytagname("Table") RowCount = 1 For Each Row In Table(23).Rows * *Colcount = 1 * *For Each cell In Row.Cells * * * MyStr = cell.innertext * * * *For i = 1 To Len(MyStr) * * * * * Range("A" & RowCount) = Mid(MyStr, i, 1) * * * * * Range("B" & RowCount) = Asc(Mid(MyStr, i, 1)) * * * * * RowCount = RowCount + 1 * * * Next i * * * Cells(RowCount, Colcount) = cell.innertext * *Next cell * *RowCount = RowCount + 1 Next Row End Sub " wrote: I would like to use *InternetExplorer.Application.Document.all() ="HTMLTable" to retrieve tables on web pages, however I am interested in specific tables. With Querytables you could use * *.WebSelectionType = xlSpecifiedTables and * * .WebTables = "1,2" to designate which tables to retrieve. *Anyway to do this with using the other method? I prefer to not use querytables as this loads the data into a spreadsheet, which I really do not need; I am trying to use excel to convert web page tables into a flatfile text file. I'd like to keep the data in memory.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - can you tell me the difference between Do While IE.readystate < 4 AND IE.Busy = True? I have seen similar macros which launch IE and have one or the other, so I assumed they were essentially equivalent, however the macro seems to be more robust when using both like you have illustrated. I tried to find some information online but could not find a definition for these two properties Thanks again Dan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I move a document from "recent items" to "documents" | New Users to Excel | |||
Retrieving info from "child" to a "parent" document | Excel Discussion (Misc queries) | |||
Solution "Your changes could not be saved" "The document may be read-only or encrypted" | Excel Discussion (Misc queries) | |||
CreateObject("InternetExplorer.Application") | Excel Programming | |||
"Querytables.add" in a loop; result is insert in a new row each t | Excel Programming |