Selenium and Excel

[armsy]

I’m trying to get information from a website table using Excel and Selenium.
I have managed to get everything working, however there’s over 600 rows of data and each row has 23 columns.
This is taking ages to import.
I am wondering if it’s possible to extract only the data that I actually need.
The basic premise is I’m running a Fantasy League and I’m getting the weekly players scores from the website.
There’s a lot of information I don’t need and a lot of players aren’t used.
What I was thinking, would it be possible to have a list of the players that are used and then loop through them and get the relevant information from the website.
If you think it’s possible I can provide more information.
There is a problem however in that when I was getting the element information, it was different on each browser. So I had to step through the code and copy selectors from the Excel controlled website.
I hope all this makes sense!

 

[ExcelToDAX]

I would use the ESPN API to extract the players information and pull it via Power Query. For example sports.core.api.espn.com/v2/sports/football/leagues/nfl/athletes/2576336 pulls information for a specific NFL player. You can go to the Teams API to get all Player ID's and then have then generate a list of the players that you want.

 

[armsy]

I would use the ESPN API to extract the players information and pull it via Power Query. For example sports.core.api.espn.com/v2/sports/football/leagues/nfl/athletes/2576336 pulls information for a specific NFL player. You can go to the Teams API to get all Player ID's and then have then generate a list of the players that you want.

Thanks for that tip, however it’s not a NFL Fantasy League, it’s for the English Premier League.

 

[armsy]

So this is the code from the first bit of the first row in the table,


What I am looking for is to be able to search for element containing the name (in this case. E. Haaland)

I will then need to navigate up to it's parent <div> This is a little bit in the first column containing the name
Then it's Grand Parent <div class="sc-iawIMh ... first-col"> this is the first column in the row
Then it's Great Grand Parent <div class="sc-dhWmbD sc-bDafuw .......ezqNgh"> This is the Row Element
Then from there get the information from the 3rd and 7th columns in the row..
Then repeat this for the next name in the range until I have all the player information I need

Every other row and column for the rest of the table have the same identifiers.

This is the code I have so far ...

' Get Excel workbook and worksheet references
    Dim excelApp As Excel.Application
    Dim wb As Excel.Workbook
    Dim ws As Excel.Worksheet
    Set excelApp = Excel.Application
    Set wb = excelApp.ActiveWorkbook
    Set ws = wb.Sheets("Sheet1")
    
    ' Define the data in column A
    Dim lastRow As Long
    lastRow = ws.Cells(ws.rows.Count, "A").End(xlUp).Row
    
    ' Loop through each player name in column A
    Dim i As Long
    For i = 1 To lastRow
        Dim playerName As String
        playerName = ws.Cells(i, 1).Value
        
        ' Find player name element
        Dim playerNameElement As Selenium.WebElement
        On Error Resume Next
        Set playerNameElement = Chrm.FindElementByXPath("//*[contains(text(),'" & playerName & "')]")
        On Error GoTo 0
        
        ' If player name element is found
        If Not playerNameElement Is Nothing Then
            ' Navigate to its great-grandparent
            Dim playerRowElement As Selenium.WebElement
            Set playerRowElement = playerNameElement.FindElementByXPath("../../../..")
            
            ' Extract the player value from the 3rd Column
            Dim playerValue As String
            playerValue = playerRowElement.FindElementByXPath("div[3]").Text
            
            ' Extract the player points from the 7th Column
            Dim playerPoints As String
            playerPoints = playerRowElement.FindElementByXPath("div[7]").Text
            
            ' Write the extracted data to the worksheet
            ws.Cells(i, 2).Value = playerValue
            ws.Cells(i, 3).Value = playerPoints
        Else
            ' If player name element is not found
            ws.Cells(i, 2).Value = "Player information not found"
            ws.Cells(i, 3).Value = "Player information not found"
        End If
    Next i

Is this the correct aproach?
Is what I want to do even possible?