Querytables WebTables equivalent for InternetExplorer.Application.Document.all()="HTMLTable"

[dtshedd]

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.

 

[Joel]

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

 

[dtshedd]

much thanks

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 getitems
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







- Show quoted text -

 

[dtshedd]

much thanks













- 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

 

[aadenning]

Works great! One question though.

This code works great! I was wonder how I can popualte the information in excel like its displayed in the HTML table?

When I import the table it populates a single value row by row.