How to load specific range of html into Excel?

E

Eric

For excel 2003, does anyone have any suggestions on how to load specific
range of html into Excel? For example, I would like to load following link
for HKB into sheet(HKB), because the page is so long, and I cannot import the
whole page into excel with limited rows.
http://www.hkex.com.hk/eng/stat/dmstat/dayrpt/dqe100302.htm#HKB

I would like to load starting from
"CLASS HKB - HSBC HOLDINGS PLC"
.... [all texts are included]
until the following text is found, then stop loading, and the following text
is not included.
"CLASS HKG - HONG KONG & CHINA GAS"

Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric
 
J

joel

I can't work on this now but will look at it later if nobody els
helps. If you view the source of the webpage using the IE menu View
Source you will sae te data you are looking for has the following tag

<A NAME="HKB">
So you simply have to find the TAG "A" with the NAME property "HKB".
 
R

ron

For excel 2003, does anyone have any suggestions on how to load specific
range of html into Excel? For example, I would like to load following link
for HKB into sheet(HKB), because the page is so long, and I cannot importthe
whole page into excel with limited rows.http://www.hkex.com.hk/eng/stat/dmstat/dayrpt/dqe100302.htm#HKB

I would like to load starting from
"CLASS HKB - HSBC HOLDINGS PLC"
... [all texts are included]
until the following text is found, then stop loading, and the following text
is not included.
"CLASS HKG - HONG KONG & CHINA GAS"

Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

Eric...Try the following. Note that the Microsoft Forms 2.0 Object
Library must be referenced...Ron

Dim mystring As New DataObject

Sub test()
' Open IE to desired website
Set ie = CreateObject("InternetExplorer.Application")

With ie
.Visible = True
.Navigate "http://www.hkex.com.hk/eng/stat/dmstat/dayrpt/
dqe100302.htm#HKB "
.Top = 50
.Left = 530
.Height = 400
.Width = 400

Do Until Not ie.Busy And ie.ReadyState = 4
DoEvents
Loop

' Get innertext for desired item and assign to clipboard
' NOTE: Tools-References: Microsoft Forms 2.0 Object Library must be
referenced

my_var = ie.document.all.Item("HKB").innertext
mystring.SetText my_var
mystring.PutInClipboard
ActiveSheet.PasteSpecial Format:="Text"
End With

ie.Quit
Range("A1").Select
End Sub
 
J

joel

The code below extract the data in to individual lines


Sub HKEY()

URL
"http://www.hkex.com.hk/eng/stat/dmstat/dayrpt/dqe100302.htm#HKB"


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 A = IE.document.getElementsByTagName("A")

CR = Chr(13)
LineFeed = Chr(10)
RowCount = 1
For Each itm In A
If UCase(itm.Name) = "HKB" Then
Data = itm.innertext
Do While Data <> ""
If InStr(Data, CR) > 0 Then
InputLine = Trim(Left(Data, InStr(Data, CR) - 1))
Data = Trim(Mid(Data, InStr(Data, LineFeed) + 1))
Else
InputLine = Data
Data = ""
End If
Range("A" & RowCount) = InputLine
RowCount = RowCount + 1
Loop
End If

Next itm


IE.Quit
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top