xyz987
04-15-2024, 09:42 AM
Hi sirs
i want get date from this website "https://tw.stock.yahoo.com/quote/1101.TW/dividend"，but it too many branch at frame.
i try to change low & column, it not correct put in excel table
how to analyze it and if there are three same name table like "table-body-wrapper", if i want to get no.3 how to use getElementsByClassName to get
attach file is all code, thanks
3151731518
Sub Hi_stock()
Workbooks("test1.xlsm").Activate
'Sheets.Add(After:=ActiveSheet).Name = "Dividend"
'Dim HTTPRequest As MSXML2.XMLHTTP
Dim httpRequest As Object
Dim htmlDoc As Object
Dim tables As Variant
Dim name_get As Variant
Dim dividend As Variant
Dim dividend_1 As Variant
Dim colNum As Integer
Dim rowNum As Integer
Set httpRequest = CreateObject("MSXML2.XMLHTTP")
Set htmlDoc = CreateObject("htmlfile")
url_1 = "https://tw.stock.yahoo.com/quote/1101.TW/dividend"
httpRequest.Open "GET", url_1, False
httpRequest.send
'Check if the request was successful (status code 200)
If httpRequest.Status = 200 Then
'Create a new HTML document object
htmlDoc.body.innerHTML = httpRequest.responseText
'get stock name
Set name_get = htmlDoc.getElementsByTagName("div")
For Each tbl In name_get
If tbl.className = "D(f) Ai(c) Mb(6px)" Then
For Each tblCol In tbl.getElementsByTagName("h1")
Cells(1, 1).Value = tblCol.innerText
Debug.Print (tblCol.innerText)
Next tblCol
End If
Next tbl
'get dividend title
Set dividend = htmlDoc.getElementsByClassName("table-header Ovx(s) Ovy(h) W(100%)")
For Each tb_1 In dividend
rowNum = 2
For Each tb_1_1 In tb_1.getElementsByTagName("div")
colNum = 1
For Each tb_1_2 In tb_1_1.getElementsByTagName("div")
Cells(rowNum, colNum).Value = tb_1_2.innerText
colNum = colNum + 1
Next tb_1_2
Next tb_1_1
Exit For
Next tb_1
'get dividend data
Set dividend = htmlDoc.getElementsByClassName("table-body-wrapper")
For Each tb_2 In dividend
'rowNum = 3
For Each tb_2_1 In tb_2.getElementsByTagName("ul")
rowNum = 3
'colNum = 1
For Each tb_2_2 In tb_2_1.getElementsByTagName("li")
colNum = 1
For Each tb_2_3 In tb_2_2.getElementsByTagName("div")
Cells(rowNum, colNum).Value = tb_2_3.innerText
colNum = colNum + 1
Next tb_2_3
rowNum = rowNum + 1
Next tb_2_2
Next tb_2_1
Exit For
Next tb_2
End If
End Sub
i want get date from this website "https://tw.stock.yahoo.com/quote/1101.TW/dividend"，but it too many branch at frame.
i try to change low & column, it not correct put in excel table
how to analyze it and if there are three same name table like "table-body-wrapper", if i want to get no.3 how to use getElementsByClassName to get
attach file is all code, thanks
3151731518
Sub Hi_stock()
Workbooks("test1.xlsm").Activate
'Sheets.Add(After:=ActiveSheet).Name = "Dividend"
'Dim HTTPRequest As MSXML2.XMLHTTP
Dim httpRequest As Object
Dim htmlDoc As Object
Dim tables As Variant
Dim name_get As Variant
Dim dividend As Variant
Dim dividend_1 As Variant
Dim colNum As Integer
Dim rowNum As Integer
Set httpRequest = CreateObject("MSXML2.XMLHTTP")
Set htmlDoc = CreateObject("htmlfile")
url_1 = "https://tw.stock.yahoo.com/quote/1101.TW/dividend"
httpRequest.Open "GET", url_1, False
httpRequest.send
'Check if the request was successful (status code 200)
If httpRequest.Status = 200 Then
'Create a new HTML document object
htmlDoc.body.innerHTML = httpRequest.responseText
'get stock name
Set name_get = htmlDoc.getElementsByTagName("div")
For Each tbl In name_get
If tbl.className = "D(f) Ai(c) Mb(6px)" Then
For Each tblCol In tbl.getElementsByTagName("h1")
Cells(1, 1).Value = tblCol.innerText
Debug.Print (tblCol.innerText)
Next tblCol
End If
Next tbl
'get dividend title
Set dividend = htmlDoc.getElementsByClassName("table-header Ovx(s) Ovy(h) W(100%)")
For Each tb_1 In dividend
rowNum = 2
For Each tb_1_1 In tb_1.getElementsByTagName("div")
colNum = 1
For Each tb_1_2 In tb_1_1.getElementsByTagName("div")
Cells(rowNum, colNum).Value = tb_1_2.innerText
colNum = colNum + 1
Next tb_1_2
Next tb_1_1
Exit For
Next tb_1
'get dividend data
Set dividend = htmlDoc.getElementsByClassName("table-body-wrapper")
For Each tb_2 In dividend
'rowNum = 3
For Each tb_2_1 In tb_2.getElementsByTagName("ul")
rowNum = 3
'colNum = 1
For Each tb_2_2 In tb_2_1.getElementsByTagName("li")
colNum = 1
For Each tb_2_3 In tb_2_2.getElementsByTagName("div")
Cells(rowNum, colNum).Value = tb_2_3.innerText
colNum = colNum + 1
Next tb_2_3
rowNum = rowNum + 1
Next tb_2_2
Next tb_2_1
Exit For
Next tb_2
End If
End Sub