Scrape Information from HTML using VBA

Question



                
                    
                        
                        International
                        UEFA Europa League
                    
                    
                        
                            F21:45
                        
                        
                            
                                3
                            
                            Marseille
                        

                                                            
                                    
                                        0-3                                    
                                



                        
                            Atletico Madrid
                            
                                0-1
                                2
                            
                        
                        
                                                        
                            
                                
                            
                                x
                                                                Head To Head

Guys,

I'm new in scraping information from websites via VBA so help is needed. i want to loop through div class="game_content" and in each div class="game_table" scrap the below info:

FROM title="UEFA Europa League" GET "UEFA Europa League"
FROM title="Marseille livescore">Marseille GET "Marseille"
FROM title="Marseille-Atletico Madrid livescore" class="">0-3 GET 0-3
FROM title="Atletico Madrid livescore">Atletico Madrid GET Atletico Madrid
FROM 0-1 GET 0-1

My post includes the FIRST class="game_table FROM class="game_content"

My piece of code:

Sub InternetConnection()

    Dim IE As InternetExplorer
    Dim HTMLDocument As HTMLDocument
    Dim GameList As IHTMLElement
    Dim Games As IHTMLElementCollection
    Dim Game As IHTMLElement

    'Open Internet Explorer
    Set IE = New InternetExplorer
        IE.Visible = True
        IE.Navigate ("https://azscore.com/en/yesterday")            
        Do While IE.readyState <> READYSTATE_COMPLETE   
            Application.StatusBar = "Loading..."
        Loop

   Set HTMLDocument = IE.document   
   Set GameList = HTMLDocument.getElementsByClassName("game_content")(0)
   Set Games = GameList.Children       

    For Each Game In Games
        If Game.className = "game_table" Then
        End If
    Next

End Sub

Any help will be appreciate.

QHarr · Accepted Answer

Required references via tools > references

HTML Object Library
MS Internet Controls.

Note:

Different output as now run on a different day against the website.

① Here is a version that parses out just those items:

Option Explicit

Public Sub PrintMatchInfo()
    Application.ScreenUpdating = False
    Dim IE As InternetExplorer, doc As HTMLDocument

    Set IE = New InternetExplorer

    With IE
        .Visible = True
        .Navigate2 "https://azscore.com/en/yesterday"

        While .Busy Or .readyState < 4: DoEvents: Wend

        Set doc = .document
    End With

    Dim games As Object, i As Long, j As Long, counter As Long
    counter = 1

    Set games = doc.getElementsByClassName("game_table")

    For i = 0 To games.Length - 1

        For j = 0 To games(i).getElementsByClassName("game_detailsCol").Length - 1
           With ThisWorkbook.Worksheets("Sheet2")
               .Cells(counter, 1) = Split(games(i).innerText, Chr$(10))(3)
               Dim tempArr() As String
               tempArr() = Split(GetKeyDetails(ReplacedString(games(i).getElementsByClassName("game_detailsCol")(j).innerText)), Chr$(126))
               .Cells(counter, 2).Resize(1, UBound(tempArr) + 1) = tempArr
               counter = counter + 1
            End With
        Next j

    Next i

    Application.ScreenUpdating = True

End Sub
Public Function ReplacedString(ByVal str As String) As String
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")

    With regex
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = "\s{2,}"
    End With
    ReplacedString = regex.Replace(str, Chr$(126)) '<== or other unexpected character
End Function


Public Function GetKeyDetails(ByVal inputString As String) As String
    Dim regex As Object, tempString As String
    Set regex = CreateObject("VBScript.RegExp")

    With regex
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = "(^[A-Z](?:\d|[01]\d|2[0-3]):[0-5]\d~)(\d?~?)+" '"^[A-Z](?:\d|[01]\d|2[0-3]):[0-5]\d~(\d~)?(\d~)?|x\~Head To Head"
    End With

    tempString = regex.Replace(inputString, vbNullString)

    Dim tempArr() As String

    tempArr = Split(tempString, Chr$(126))

    Dim keyDetails(), outputString As String, detail As Long
    keyDetails = Array(0, 1, 2, 3)

    For detail = LBound(keyDetails) To UBound(keyDetails)
        outputString = outputString & Chr$(126) & tempArr(keyDetails(detail))
    Next detail

    GetKeyDetails = Trim$(outputString)

End Function

Output:

② Version with all the info:

Code:

Option Explicit
Public Sub PrintAllMatchInfo()
    Application.ScreenUpdating = False
    Dim IE As InternetExplorer, doc As HTMLDocument

    Set IE = New InternetExplorer

    With IE
        .Visible = True
        .Navigate2 "https://azscore.com/en/yesterday"

        While .Busy Or .readyState < 4: DoEvents: Wend

        Set doc = .document
    End With

    Dim games As Object, i As Long, j As Long, counter As Long
    counter = 1

    Set games = doc.getElementsByClassName("game_table")

    For i = 0 To games.Length - 1

        For j = 0 To games(i).getElementsByClassName("game_detailsCol").Length - 1
           With ThisWorkbook.Worksheets("Sheet1")
               .Cells(counter, 1) = Split(games(i).innerText, Chr$(10))(3)
               .Cells(counter, 2) = ReplacedString(games(i).getElementsByClassName("game_detailsCol")(j).innerText)
               counter = counter + 1
            End With
        Next j

    Next i

    Application.ScreenUpdating = True

End Sub
Public Function ReplacedString(ByVal str As String) As String
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")

    With regex
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = "\s{2,}"
    End With
    ReplacedString = regex.Replace(str, " ") '<== or other unexpected character
End Function

Output:

Pattern for your follow-up question: "^\w+\/(\d+~)+"

Scrape Information from HTML using VBA

Answers (1)

① Here is a version that parses out just those items:

② Version with all the info:

Related Questions