vba get html table value into excel sheet

[xyz987]

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) 除權除息 財報分析 (https://histock.tw/stock/financial.aspx?no=1101&t=2)
website refer pic: https://ibb.co/q1thnqq (https://urldefense.com/v3/__https:/ibb.co/q1thnqq__;!!IEjQjGLiTq_9oQ!JxLS0lEs9ySRX2cRbcJnD-GvoP6jUZUQymjTrpvChqJX1L4Wd3q5QQ4x4Eu7Sq0TDMUIzTR7WT7pT0t3$)
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) 除權除息 財報分析 (https://histock.tw/stock/financial.aspx?no=1101&t=2)"
' 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]

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]

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]

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

[jdelano]

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

[arnelgp]

how about Power Query? Almost No code at all.

[xyz987]

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

[jdelano]

You're welcome, happy to help.

[arnelgp]

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

31401

[xyz987]

thank you very much!:yes