vba get html table value into excel sheet

**xyz987** · 03-11-2024, 01:41 AM

Hi sirs
I want to get get html table vale to excel form this website, but i can't get it, And i ask chatgpt it suggest code like list, but still can't get value! Is this website defen or my office version too old not support or neet to plug in vba module? my office is 2010 version, please help & thanks

website: 台泥 (1101) 除權除息財報分析
website refer pic: https://ibb.co/q1thnqq
code (chatgpt) :

Sub ImportDataFromWebsite_1()
Dim URL As String
Dim HTTPRequest As Object
Dim HTMLDoc As Object
Dim TableElement As Object
Dim TableRow As Object
Dim TableColumn As Object
Dim RowIndex As Integer
Dim ColumnIndex As Integer
Workbooks.Add
'Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="C:\stock\analysis\test.xlsx"
Windows("test.xlsx").Activate
ActiveSheet.Name = "Sheet1"
'ActiveWorkbook.Save '(vb = No)
' Specify the URL of the webpage containing the table
URL = "台泥 (1101) 除權除息 財報分析"
' Create a new instance of the XML HTTP Request object
Set HTTPRequest = CreateObject("MSXML2.XMLHTTP")
' Open the HTTP request and send it
HTTPRequest.Open "GET", URL, False
HTTPRequest.send
' Check if the request was successful (status code 200)
If HTTPRequest.Status = 200 Then
' Create a new HTML document object
   Set HTMLDoc = CreateObject("HTMLFile")
' Load the response text into the HTML document
   HTMLDoc.body.innerHTML = HTTPRequest.responseText
' Find the table element by looping through all tables
   For Each TableElement In HTMLDoc.getElementsByTagName("table")
      If TableElement.className = "tb-stock text-center tbBasic" Then
         RowIndex = 1
         ' Loop through each row in the table
         For Each TableRow In TableElement.getElementsByTagName("tr")
            ColumnIndex = 1
            ' Loop through each cell in the row
            For Each TableColumn In TableRow.getElementsByTagName("td")
               ' Write the cell value to Excel
               ThisWorkbook.Sheets("Sheet1").Cells(RowIndex, ColumnIndex).Value = TableColumn.innerText
               ColumnIndex = ColumnIndex + 1
            Next TableColumn
            RowIndex = RowIndex + 1
         Next TableRow
         Exit For ' Exit loop once table is found
      End If
   Next TableElement
   If TableElement Is Nothing Then
      MsgBox "Table not found!"
   End If
Else
   MsgBox "Failed to retrieve data from the website!"
End If
' Clean up objects
Set HTTPRequest = Nothing
Set HTMLDoc = Nothing
End Sub

**Aussiebear** · 03-11-2024, 02:30 AM

Welcome to VBAX xyz987. Sorry but i am somewhat reluctant to test your code given the url's. Hopefully someone with more confidence will come along shortly.

**xyz987** · 03-11-2024, 02:48 AM

Originally Posted by Aussiebear

Welcome to VBAX xyz987. Sorry but i am somewhat reluctant to test your code given the url's. Hopefully someone with more confidence will come along shortly.

?? sorry, my english not very well, your mean is the url or my code any problem?? this website is only an taiwan's normal stock information website. any thanks for your reply

**jdelano** · 03-11-2024, 03:41 AM

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