VBA Selenium loop through each table-CodePudding

Using Selenium for Excel, I need to loop through each table in the webpage to extract each table. There are usually 7 to 10 tables on the page.

The current code I have extracts the first table, nine time's over (based on count). How can I extract each table once?

Dim bot As New ChromeDriver, tbl As Selenium.TableElement, LRow As Long

Const sURL As String = "https://www.racingandsports.com/form-guide/thoroughbred/australia/sale/2021-10-06/race-tips"

With bot
    .Get sURL

    Set tbl = .FindElementById("rank_table").AsTable
    r = .FindElementsById("rank_table").Count
    For i = 1 To r

    Sheets("Import").Select
    LRow = Cells(Rows.Count, 1).End(xlUp).Row

    tbl.ToExcel ThisWorkbook.Sheets("Import").Range("A" & LRow   2)
Next i
End With

CodePudding user response：

You can use css id selector and gather with a method returning WebElements rather than WebElement e.g.

Dim tables As Selenium.WebElements

Set tables = .FindElementsByCss("#rank_table")

Then For Each tbl that collection, and use tweak your existing code to write out each table to next free row ( any empty row gap in between tables).

You could also use the .FindElementsById("rank_table") as it appears that method exists, though I have never used it.

CodePudding user response：

Why your code is copying the first table for 9 times?

In that page, there are total 9 elements are having id value as rank_table
You have mentioned Set tbl = .FindElementById("rank_table").AsTable outside of the for loop and as there are multiple id's with same value it picks the first element always if we don't specify anything.
In your loop, you are pasting firstly copied table for 9 times.

Updated code:

Dim bot As New ChromeDriver
Dim tbl As Selenium.TableElement
Dim LRow As Long

Const sURL As String = "https://www.racingandsports.com/form-guide/thoroughbred/australia/sale/2021-10-06/race-tips"

With bot
    .Get sURL
    r = .FindElementsById("rank_table").Count
    For i = 1 To r
    Set tbl = .FindElementByXPath("(//table[@id='rank_table'])[" & i & "]").AsTable
    Sheets("Import").Select
    LRow = Cells(Rows.Count, 1).End(xlUp).Row
    tbl.ToExcel ThisWorkbook.Sheets("Import").Range("A" & LRow   2)
Next i
End With

What changes I did to make it work?

Created a dynamic xPath to iterate through all the tables.
Called the .FindElementByXPath("xPath").AsTable inside the loop.