如何用VBA从HTML抓取赛事数据？求代码完善方案

Hey there! As someone who’s fumbled through VBA web scraping as a beginner, let’s break this down step by step to get you exactly the match data you need. I’ll share a refined code example with explanations to avoid common pitfalls.

Prerequisites First #

Before diving into code, make sure you enable the necessary references in the VBA Editor:

1. Open the VBA Editor (Alt + F11)
2. Go to Tools > References
3. Check these two boxes:
   • Microsoft HTML Object Library
   • Microsoft XML, v6.0 (or the latest version available)

Full Working Code #

This code will fetch the webpage, loop through all game_content divs, extract data from their nested game_table elements, and write the results to an Excel sheet:

Sub ScrapeFootballMatchData()
    Dim xmlHttp As Object
    Dim htmlDoc As MSHTML.HTMLDocument
    Dim gameContentDivs As MSHTML.IHTMLElementCollection
    Dim singleGameContent As MSHTML.HTMLDivElement
    Dim gameTable As MSHTML.HTMLDivElement
    Dim league, homeTeam, finalScore, awayTeam, halfTimeScore As String
    Dim nextRow As Integer
    
    ' Initialize starting row for Excel output
    nextRow = 2
    Set xmlHttp = CreateObject("MSXML2.XMLHTTP.6.0")
    Set htmlDoc = New MSHTML.HTMLDocument
    
    ' Replace this URL with your target webpage address
    xmlHttp.Open "GET", "YOUR_TARGET_WEBSITE_URL", False
    xmlHttp.send
    
    ' Load the webpage content into the HTML parser
    htmlDoc.body.innerHTML = xmlHttp.responseText
    
    ' Grab all divs with class "game_content"
    Set gameContentDivs = htmlDoc.getElementsByClassName("game_content")
    
    ' Handle case where no game content is found
    If gameContentDivs.Count = 0 Then
        MsgBox "No game content elements found on the page.", vbExclamation
        Exit Sub
    End If
    
    ' Loop through each game content block
    For Each singleGameContent In gameContentDivs
        ' Find the nested game_table div inside the current game_content
        Set gameTable = singleGameContent.getElementsByClassName("game_table")(0)
        
        ' Skip this block if no game_table exists
        If Not gameTable Is Nothing Then
            ' Use helper function to safely extract text (avoids crashes if elements are missing)
            league = GetElementText(gameTable, "league") ' Replace with actual class name for league
            homeTeam = GetElementText(gameTable, "home_team") ' Replace with actual class name for home team
            finalScore = GetElementText(gameTable, "final_score") ' Replace with actual class name for final score
            awayTeam = GetElementText(gameTable, "away_team") ' Replace with actual class name for away team
            halfTimeScore = GetElementText(gameTable, "half_time_score") ' Replace with actual class name for half-time score
            
            ' Write data to Excel (adjust sheet name if needed)
            With ThisWorkbook.Sheets("Sheet1")
                .Cells(nextRow, 1).Value = league
                .Cells(nextRow, 2).Value = homeTeam
                .Cells(nextRow, 3).Value = finalScore
                .Cells(nextRow, 4).Value = awayTeam
                .Cells(nextRow, 5).Value = halfTimeScore
            End With
            
            nextRow = nextRow + 1
        End If
    Next singleGameContent
    
    ' Clean up objects to free memory
    Set xmlHttp = Nothing
    Set htmlDoc = Nothing
    Set gameContentDivs = Nothing
    Set singleGameContent = Nothing
    Set gameTable = Nothing
    
    MsgBox "Scraping done! Check Sheet1 for your match data.", vbInformation
End Sub

' Helper function: Safely get text from an element by class name (returns "N/A" if element is missing)
Function GetElementText(parentElement As MSHTML.HTMLDivElement, targetClass As String) As String
    Dim targetElements As MSHTML.IHTMLElementCollection
    Set targetElements = parentElement.getElementsByClassName(targetClass)
    
    If targetElements.Count > 0 Then
        GetElementText = Trim(targetElements(0).innerText)
    Else
        GetElementText = "N/A"
    End If
End Function

Key Explanations #

• XMLHTTP Object: Fetches the webpage without opening a browser (faster and more efficient than using Internet Explorer).
• Helper Function GetElementText: Prevents your code from crashing if a specific element (like half-time score) is missing from a match block. It returns "N/A" as a placeholder instead.
• Element Traversal: We first grab all game_content divs, then dig into each one to find its game_table child—this ensures we only extract data from the correct nested structure.
• Excel Output: Data is written starting from row 2 on Sheet1 (adjust the sheet name and starting row as needed).

Important Notes #

1. Replace Class Names: Make sure the class names in the code (like "league", "home_team") match exactly with the ones in your target HTML. Use your browser’s developer tools (F12) to inspect the elements and get the correct class names.
2. Dynamic Content: If the webpage loads data dynamically (e.g., via JavaScript), XMLHTTP won’t capture it. In that case, you’ll need to use the Internet Explorer object or a tool like Selenium Basic for VBA.
3. Anti-Scraping Measures: Some websites block automated requests. If you get errors, try adding a user-agent header to your XMLHTTP request or adding small delays between requests.

内容的提问来源于stack exchange，提问作者Error 1004