Using extracted website data to fill sheet in VBA

[mattadams84]

Another user (Domenic) has kindly helped create code to extract some data from a website (see OP https://www.mrexcel.com/forum/excel-questions/1095752-extracting-data-website-html-using-vba.html)


I originally wanted to extract data from betexplorer.com. I wanted to extract two different pieces of data:

https://www.betexplorer.com/soccer/s...eague-1/stats/

I would like to extract Matches Played and Matches Remaining
I would like to extract Home Goals and Away Goals (per match)


I now have the the code to do that and it is as follows

Option Explicit

Sub GetSoccerStats()


    'Set a reference (VBE > Tools > References) to the following libraries:
    '   1) Microsoft XML, v6.0
    '   2) Microsoft HTML Object Library
    
    Dim xmlReq As New MSXML2.XMLHTTP60
    Dim objDoc As New MSHTML.HTMLDocument
    Dim objTable As MSHTML.htmlTable
    Dim objTableRow As MSHTML.htmlTableRow
    Dim strURL As String
    Dim strResp As String
    Dim strText As String
    Dim rw As Long
    
    strURL = "https://www.betexplorer.com/soccer/south-korea/k-league-1/stats/"
    
    With xmlReq
        .Open "GET", strURL, False
        .send
        If .Status <> 200 Then
            MsgBox "Error " & .Status & ":  " & .statusText
            Exit Sub
        End If
        strResp = .responseText
    End With
    
    Worksheets.Add
    
    objDoc.body.innerHTML = strResp
    
    Set objTable = objDoc.getElementsByClassName("table-main leaguestats")(0)
    
    If Not objTable Is Nothing Then
        rw = 1
        For Each objTableRow In objTable.Rows
            strText = objTableRow.Cells(0).innerText
            Select Case strText
                Case "Matches played", "Matches remaining", "Home goals", "Away goals"
                    Cells(rw, "a").Value = objTableRow.Cells(0).innerText
                    Cells(rw, "b").Value = objTableRow.Cells(1).innerText
                    Cells(rw, "c").Value = objTableRow.Cells(2).innerText
                    rw = rw + 1
            End Select
        Next objTableRow
        Columns("a").AutoFit
    End If
    
    Set xmlReq = Nothing
    Set objDoc = Nothing
    Set objTable = Nothing
    Set objTableRow = Nothing


End Sub

This code works perfectly however i want to take it a step further.

I actually want to run this macro for many different URL's on the same site. I have a worksheet already created that has a list of Football Leagues (in the rows), the columns hold the data.

You can find the file here : https://www.dropbox.com/s/77sol24sty...oals.xlsm?dl=0

This is a file where i will add leagues to the rows as i go. Is it possible to adapt the code that extracts the data so that it can populate the columns in my sheet? I do not need to pull in the names of the data (matches remaining, home goals, away goals etc) as this code does, i only need the figures. The extracted figures would have to populate the columns as per the sheet (so each row contains the data for each league. As you can see there are a few leagues so it would need to loop through each row and then use the corresponding URL for that row.

You will notice that there is a column that contains the word CURRENT. This is to indicate that it should use the URL in the Current URL column. If I change the value to LAST i would like it to use the URL in the Last URL column.

For each league it will be different if I use CURRENT or LAST.

Any help is greatly appreciated.

Kind regards

 

[mattadams84]

This is the link that works to the excel file

https://www.dropbox.com/s/77sol24sty75w5z/Avg Goals.xlsm?dl=0