vba get html table value into excel sheet

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) :

Code:

---

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

---

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.

Quote:

---

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

This worked for me:

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

Code:

---


    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

---

here is the file, if you'd like to start with it.

how about Power Query? Almost No code at all.

Quote:

---

Originally Posted by jdelano

here is the file, if you'd like to start with it.

---

thank you very much

You're welcome, happy to help.

.. and here is from Power Query (No code).

Attachment 31401

thank you very much!:yes