I am using the below code to get the table but it notice there is one button with data and not getting on excel, as inspect the data show after clicking on button
please help to get the all table data include the button column
Sub get_table()
Dim ie As Object
Dim url As String
url = "https://www.screener.in/company/COFORGE/consolidated/"
Set ie = CreateObject("internetexplorer.application")
ie.Visible = True
ie.navigate url
Do While ie.Busy = True: DoEvents: Loop
Do Until ie.readyState = 4: DoEvents: Loop
Dim tbl As HTMLTable
Set tbl = ie.document.getElementsByTagName("table")(1)
Dim trcounter As Integer
Dim tdcounter As Integer
trcounter = 1
tdcounter = 1
thcounter = 1
Dim tr As HTMLTableRow
Dim td As HTMLTableCell
Dim th As HTMLTableRow
Dim mySH As Worksheet
Set mySH = ThisWorkbook.Sheets("sheet1")
' this for date
For Each th In tbl.getElementsByTagName("th")
mySH.Cells(tdcounter, thcounter).Value = th.innerText
thcounter = thcounter 1
Next th
' this for table data
For Each tr In tbl.getElementsByTagName("tr")
For Each td In tr.getElementsByTagName("td")
mySH.Cells(trcounter, tdcounter).Value = td.innerText
tdcounter = tdcounter 1
Next td
tdcounter = 1
trcounter = trcounter 1
Next tr
End Sub
CodePudding user response:
You can first check if the <td>
contains button using InStr function, if so then click the button. After clicking all the button in the table, you can read the data and write them into the sheet.
The working example is like this:
Sub LOADIE()
Set ieA = CreateObject("InternetExplorer.Application")
ieA.Visible = True
ieA.navigate "https://www.screener.in/company/COFORGE/consolidated/"
Do Until ieA.readyState = 4
DoEvents
Loop
Set doc = ieA.document
Set tbl = doc.getElementsByTagName("table")(1)
trcounter = 1
tdcounter = 1
thcounter = 1
Dim tr As HTMLTableRow
Dim td As HTMLTableCell
Dim th As HTMLTableRow
Dim mySH As Worksheet
Set mySH = ThisWorkbook.Sheets("sheet1")
' this for date
For Each th In tbl.getElementsByTagName("th")
mySH.Cells(tdcounter, thcounter).Value = th.innerText
thcounter = thcounter 1
Next th
' this for clicking the button
For Each tr In tbl.getElementsByTagName("tr")
For Each td In tr.getElementsByTagName("td")
If InStr(td.innerHTML, "button") > 0 Then
td.getElementsByTagName("button")(0).Click
End If
Next td
tdcounter = 1
trcounter = trcounter 1
Next tr
' this for table data
trcounter = 1
For Each tr In tbl.getElementsByTagName("tr")
For Each td In tr.getElementsByTagName("td")
mySH.Cells(trcounter, tdcounter).Value = td.innerText
tdcounter = tdcounter 1
Next td
tdcounter = 1
trcounter = trcounter 1
Next tr
ieA.Quit
Set ieA = Nothing
End Sub