vba get html table value into excel sheet

[jdelano]

This worked for me:

references: Microsoft XML, 6.0; MS WinHTTP Services 5.1, MS HTML Object Library

    Dim HTTPRequest As MSXML2.XMLHTTP
    Dim url As String
    Dim htmlDoc As HTMLDocument
    Dim tbls As Variant
    Dim tbl As htmlTable
    Dim tblRow As HTMLTableRow
    Dim tblCol As HTMLTableCol
    
    Dim colNum As Integer
    Dim rowNum As Integer
    
    Set HTTPRequest = New MSXML2.XMLHTTP
    Set htmlDoc = New HTMLDocument
    
    ' Open the HTTP request and send it
    url = "https://histock.tw/stock/financial.aspx?no=1101&t=2"
    
    HTTPRequest.Open "GET", url, False
    HTTPRequest.send


    htmlDoc.body.innerHTML = HTTPRequest.responseText
    
    ' extract the table
    Set tbls = htmlDoc.getElementsByTagName("table")
    For Each tbl In tbls
        If tbl.className = "tb-stock text-center tbBasic" Then
            rowNum = 10 ' what row to start placing the table data?
            For Each tblRow In tbl.getElementsByTagName("tr")
            
                colNum = 1
                For Each tblCol In tblRow.getElementsByTagName("th")
                    Sheet1.Cells(rowNum, colNum).Value = tblCol.innerText
                    colNum = colNum + 1
                Next tblCol
            
                colNum = 1
                For Each tblCol In tblRow.getElementsByTagName("td")
                    Sheet1.Cells(rowNum, colNum).Value = tblCol.innerText
                    colNum = colNum + 1
                Next tblCol
                
                rowNum = rowNum + 1
            Next tblRow
            
            ' all the rows in the target table have been read, no need to continue looping tables
            Exit For
        End If
    Next tbl
        
    ' release the objects
    Set HTTPRequest = Nothing
    Set htmlDoc = Nothing