VBA Selenium loop through each table

user_10803615 0
Viewed 829

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
selenium vba excel loops
2 Answers

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.

user_62412350

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.
user_77316230
Related