Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add link to specific Excel worksheet in HTML Outlook message | Links and Linking in Excel | |||
How to load specific range of data from html into excel? | Excel Programming | |||
Hyperlink from HTML to a specific Excel workshhet | Excel Programming | |||
Hyperlink from HTML to a specific Excel Worksheet | Excel Programming | |||
From HTML open a specific worksheet within a excel file | Excel Programming |