How to load specific range of html into Excel?
 

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/dmst...100302.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

How to load specific range of html into Excel?
 

I can't work on this now but will look at it later if nobody else
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".


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=192998

http://www.thecodecage.com/forumz

How to load specific range of html into Excel?
 

On Apr 5, 7:26*am, Eric wrote:
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/dmst...100302.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

How to load specific range of html into Excel?
 

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


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=192998

http://www.thecodecage.com/forumz

How to load specific range of html into Excel?
 

On Apr 5, 10:54*am, joel wrote...snip...
The code below extract the data in to individual lines

My code also places the data on individual lines...Ron