Home > Mobile >  Scraping a table from a website Screener.in using VBA
Scraping a table from a website Screener.in using VBA

Time:09-18

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
  • Related