VBA to scrap data from URL

[lugui]

In the course of learning to scrap data from URL, I have been using Web Query function. However, when I try to get stock option data from this site https://www.hkex.com.hk/eng/stat/dmstat/dayrpt/dqe181123.htm , I end up only getting the header and not the content.

In particular , I would like to get the stock option data for "TCH" using VBA.

Any help would be appreciated


Regards

lugui

 

[Worf]

Welcome to the Board

Sub Scrapper()
Dim IE As Object, ws As Worksheet, r As Range, rend As Range, cp$
cp = "TCH"                              ' desired company
Set ws = Sheets("Sheet5")               ' where page data will go
With ws
    .Cells.ClearContents
    .Activate
    [a1].Activate
End With
Set IE = CreateObject("InternetExplorer.Application")
With IE
    .Visible = True
    .navigate "https://www.hkex.com.hk/eng/stat/dmstat/dayrpt/dqe181123.htm"
    Do Until .readyState = 4: DoEvents: Loop
End With
IE.ExecWB 17, 0                         ' select all
IE.ExecWB 12, 2                         ' copy
ws.PasteSpecial "Texto", False, False   ' for English version use Text instead
Set r = ws.Cells.Find("class " & cp, ws.[a1], xlValues, xlPart, xlByRows, xlNext, False)    ' data starts here
Set rend = ws.Cells.Find("total put", r, xlValues, xlPart, xlByRows, xlNext, False)         ' data ends here
Sheets("summary").Cells.ClearContents
ws.Range(r, rend).Copy Sheets("summary").[a1]                                               ' copy desired data
End Sub