extract all tables from a site selenium + vba

[cgpa]

hello good afternoon
I would like some help with this code, if possible, it enters the site and extracts data from just one table, how can I copy all the tables on the site?

I'm using the code below

I tried to use the code in the attached image ("/html/body/div[1]") to select them all, but I got an error

Sub ExtractTabs()
Set driver = New ChromeDriver
Dim dest As range
Set dest = range("A1")

driver.Get "https://www2.bmf.com.br/pages/portal/bmfbovespa/lumis/lum-tipo-de-participante-ptBR.asp"

Dim tabel As WebElement

Set tabel = driver.FindElementByXPath("/html/body/div[1]/div[2]/div/table[1]")

If tabel Is Nothing Then
MsgBox "element not found"
Else
tabel.AsTable.ToExcel dest
End If

driver.Quit
End Sub

 

[Anthony47]

Load the following Code into an empty standard module of your vba:

Dim WPage As Object

Sub ExtractTabs()
'
Set WPage = CreateObject("Selenium.WebDriver")
WPage.Start "Chrome"

myUrl = "https://www2.bmf.com.br/pages/portal/bmfbovespa/lumis/lum-tipo-de-participante-ptBR.asp"
Call GetAllTablesArr(myUrl, 1, 1)       'Url, Row, Column

WPage.Quit
Set WPage = Nothing
End Sub




Sub GetAllTablesArr(ByVal myUrl As String, Optional rNum0 As Long = 1, Optional cNum0 As Long = 1)
Dim TBColl As Object
Dim I As Long, j As Long, myTim As Single
Dim RNum As Long, CNum As Long
    
Dim TArr

WPage.Get myUrl
'
myTim = Timer
'
Set TBColl = WPage.FindElementsByTag("table")
RNum = rNum0: CNum = cNum0
'
For I = 1 To TBColl.Count               'Scan delle Tabelle presenti
    TArr = TBColl(I).AsTable.Data
    RNum = RNum + 1
    Cells(RNum, CNum).Value = "## Table " & I
    If (UBound(TArr) * UBound(TArr, 2)) > 0 Then
        Cells(RNum + 1, CNum).Resize(UBound(TArr), UBound(TArr, 2)).Value = TArr
    End If
    RNum = RNum + UBound(TArr) + 1
    DoEvents
Next I
Debug.Print "FINE", RNum, Format(Timer - myTim, "0.00"), myUrl
End Sub

Then start Sub ExtractTabs

The output will be written starting from Row2 /Column2 of the active sheet

 

[cgpa]

It worked great!
Let me ask you a question:
Would it be possible to make 2 adjustments,

first: is it possible to keep the main name of each table instead of "## Table 1, ## Table 2 " etc?

I'll attach an image as an example.

Second :
Would it be possible to keep the same formatting of numbers in the standard of my country?

I will attach an image as an example.

 

[Anthony47]

Lets modify the code for the Sub GetAllTablesArr as follows:

Sub GetAllTablesArr(ByVal myUrl As String, Optional rNum0 As Long = 1, Optional cNum0 As Long = 1)
Dim TBColl As Object, TBCap As Object
Dim I As Long, j As Long, myTim As Single
Dim RNum As Long, CNum As Long
Dim TArr
'
WPage.Get myUrl
'
myTim = Timer
'
Set TBColl = WPage.FindElementsByTag("table")
RNum = rNum0: CNum = cNum0
'
For I = 1 To TBColl.Count               'Scan delle Tabelle presenti
    TArr = TBColl(I).AsTable.Data
    RNum = RNum + 2
    Set TBCap = TBColl(I).FindElementsByTag("Caption")
    If TBCap.Count > 0 Then
        Cells(RNum, CNum).Value = "##" & I & " " & TBCap(1).Text    'Caption, if any
    Else
        Cells(RNum, CNum).Value = "## Table " & I                   'Standard heads
    End If
    If (UBound(TArr) * UBound(TArr, 2)) > 0 Then
        Cells(RNum + 1, CNum).Resize(UBound(TArr) + 5, UBound(TArr, 2) + 1).ClearContents
        Cells(RNum + 1, CNum).Resize(UBound(TArr), UBound(TArr, 2)).NumberFormat = "@"
        Cells(RNum + 1, CNum).Resize(UBound(TArr), UBound(TArr, 2)).Value = TArr
    End If
    RNum = RNum + UBound(TArr) + 1
    DoEvents
Next I
Debug.Print "FINE", RNum, Format(Timer - myTim, "0.00"), myUrl
End Sub

This will use the table "Caption", if available, or will use the default ##Table x; also the data are copied as text, that is how they looks on the web.

The aim of that code is importing the tables, not "presenting them"; so alignment, data conversion, formatting is job for you

 

[cgpa]

it is perfect !!! thank you very much!!!