On Jan 31, 2:51*am, Eric wrote:
Does anyone have any suggestions on how to load specific range of data from
html into excel?
For example,http://www.hkex.com.hk/markdata/quot...#short_selling
I would like to load this link into excel, but this page is too long, and
the limitation of row for excel is 66536, so the specific section of html
cannot be loaded, does anyone have any suggestions on how to load the
specific section (short_selling) of html into excel?
Thanks in advance for any suggestions
Eric

Eric,

Here is another way to get the data using XML. The macro
"GetShortData" will put the data onto the ActiveSheet, anchored in A1.

Best,

Matthew Herbert

Sub GetShortData()
Dim strRes As String
Dim strFind1 As String
Dim strFind2 As String
Dim lngPosStart As Long
Dim lngPosEnd As Long
Dim varArr As Variant
Dim Rng As Range
Const c_strURL As String = "http://www.hkex.com.hk/markdata/quot/
d100114e.htm"

strRes = GetXMLHTTP(c_strURL)

strFind1 = "SHORT SELLING TURNOVER - DAILY REPORT"
strFind2 = "PREVIOUS DAY'S ADJUSTED SHORT SELLING TURNOVER"

'get the second instance of strFind1
lngPosStart = InStr(1, strRes, strFind1)
lngPosStart = InStr(lngPosStart + 1, strRes, strFind1)

lngPosEnd = InStr(lngPosStart, strRes, strFind2)
strRes = Mid(strRes, lngPosStart, lngPosEnd - lngPosStart)

varArr = Split(strRes, vbCrLf)

Set Rng = ActiveSheet.Range("A1")
Set Rng = Range(Rng, Rng.Offset(UBound(varArr), 0))

Rng.Value = Application.Transpose(varArr)

End Sub

Function GetXMLHTTP(strURL As String) As String

Dim objXMLHTTP As Object
Dim strText As String

If strURL = "" Then
GetXMLHTTP = ""
Exit Function
End If

Set objXMLHTTP = CreateObject("Microsoft.XMLHTTP")

With objXMLHTTP
.Open "GET", strURL, False
.Send
strText = .responseText
End With

If objXMLHTTP.statusText = "OK" Then
GetXMLHTTP = strText
Else
GetXMLHTTP = ""
End If

End Function