How to download a table to excel?

Question

I would like to download data from a website to Excel in order to create my own statistics. I tried power query but couldn't get everything I wanted. For example, here is a specific URL: https://www.betexplorer.com/football/algeria/ligue-2/ On this page, the next matches section is downloaded, but not the standings. And only a part of the results, because under the results there is an inscription "show all results" that you have to click on to reveal all the results. Can someone help me with how I can download the data on the page with an Excel macro?
Someday I would have to download more similar data, which is very slow manually, so I would like to automate it. (I use excel 2013.) Thank you in advance for your help!

I tried power query but couldn't get everything I wanted.

Answer 1

Try this :

Sub FetchWebData()
    Dim ie As Object
    Dim html As Object
    Dim doc As Object
    Dim tbody As Object
    Dim tr As Object
    Dim cell As Object
    Dim button As Object
    Dim rowIndex As Integer

    ' CSS selector
    Const selector As String = "#home-page-left-column > section > div:nth-child(4) > div > table > tbody"

    ' Create a new Internet Explorer instance
    Set ie = CreateObject("InternetExplorer.Application")
    ie.Visible = False
    ie.navigate "https://www.betexplorer.com/football/algeria/ligue-2/"

    ' Wait for the page to load completely
    Do While ie.readyState <> 4
        DoEvents
    Loop

    ' Get the HTML document
    Set html = ie.document

    ' Use the CSS selector to select the desired content
    On Error Resume Next
    Set tbody = html.querySelector(selector)
    On Error GoTo 0

    ' Check if the tbody is found
    If Not tbody Is Nothing Then
        ' Clear the worksheet
        Sheets("Sheet1").Cells.Clear

        ' Loop through the rows and cells to populate the worksheet
        rowIndex = 1
        For Each tr In tbody.getElementsByTagName("tr")
            For Each cell In tr.getElementsByTagName("td")
                ' Check if the cell contains a button with the data-odd attribute
                If cell.getElementsByTagName("button").Length > 0 Then
                    Set button = cell.getElementsByTagName("button")(0)
                    If button.hasAttribute("data-odd") Then
                        Sheets("Sheet1").Cells(rowIndex, cell.cellIndex + 1).Value = button.getAttribute("data-odd")
                    Else
                        Sheets("Sheet1").Cells(rowIndex, cell.cellIndex + 1).Value = cell.innerText
                    End If
                Else
                    Sheets("Sheet1").Cells(rowIndex, cell.cellIndex + 1).Value = cell.innerText
                End If
            Next cell
            rowIndex = rowIndex + 1
        Next tr
        MsgBox "Data fetched successfully!"
    Else
        MsgBox "No content found for the given selector."
    End If

    ' Clean up
    ie.Quit
    Set ie = Nothing
    Set html = Nothing
    Set tbody = Nothing
End Sub

Alernativly you can use:

Const selector As String = "#js-leagueresults-all > div > div > table > tbody"

and:

ie.navigate "https://www.betexplorer.com/football/algeria/ligue-2/results"