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.

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.

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 -

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