Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 9
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 9,101
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 9
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 9
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I move a document from "recent items" to "documents" John Gerke in Central Oregon New Users to Excel 1 March 2nd 08 09:31 AM
Retrieving info from "child" to a "parent" document create button to unhide rows Excel Discussion (Misc queries) 0 January 21st 07 11:40 PM
Solution "Your changes could not be saved" "The document may be read-only or encrypted" [email protected] Excel Discussion (Misc queries) 0 August 7th 06 06:31 AM
CreateObject("InternetExplorer.Application") shart[_9_] Excel Programming 1 January 2nd 06 06:14 PM
"Querytables.add" in a loop; result is insert in a new row each t Ben Excel Programming 1 March 15th 05 03:41 PM


All times are GMT +1. The time now is 11:56 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"