如何通过Excel VBA提取Google搜索中class st内、class f外的文本？

Got it, let's walk through how to solve this problem step by step. You want to pull text from span elements with class st in Google search results, but exclude any content inside elements with class f—here's a solid approach using Excel VBA:

First, Set Up the Required Reference #

Before writing code, make sure you've enabled the HTML parsing library in VBA:

• Open the VBA Editor (Alt + F11)
• Go to Tools > References
• Check the box for Microsoft HTML Object Library and click OK

Full VBA Code Example #

Sub ScrapeGoogleSearchResults()
    Dim xmlHttp As Object
    Dim htmlDoc As HTMLDocument
    Dim stElements As IHTMLElementCollection
    Dim stElement As IHTMLElement
    Dim fElement As IHTMLElement
    Dim outputRow As Long
    Dim searchQuery As String
    
    ' Set your search query here
    searchQuery = "your-search-term-here"
    outputRow = 2 ' Start output at row 2 (assuming row 1 is headers)
    
    ' Initialize XMLHTTP object to fetch the page
    Set xmlHttp = CreateObject("MSXML2.XMLHTTP.6.0")
    With xmlHttp
        .Open "GET", "https://www.google.com/search?q=" & URLEncode(searchQuery), False
        ' Add a User-Agent header to avoid being blocked by Google's anti-scraping
        .setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/118.0.0.0 Safari/537.36"
        .send
    End With
    
    ' Load the response into an HTML document for parsing
    Set htmlDoc = New HTMLDocument
    htmlDoc.body.innerHTML = xmlHttp.responseText
    
    ' Grab all span elements with class "st"
    Set stElements = htmlDoc.getElementsByClassName("st")
    
    ' Loop through each result element
    For Each stElement In stElements
        ' Remove all child elements with class "f" to exclude their content
        For Each fElement In stElement.getElementsByClassName("f")
            fElement.removeNode True ' True = remove the element and all its children
        Next fElement
        
        ' Extract the cleaned text and write to the worksheet
        Sheet1.Cells(outputRow, 1).Value = Trim(stElement.innerText)
        outputRow = outputRow + 1
    Next stElement
    
    ' Clean up objects
    Set xmlHttp = Nothing
    Set htmlDoc = Nothing
    Set stElements = Nothing
    
    MsgBox "Scraping complete! Results saved to Sheet1.", vbInformation
End Sub

' Helper function to URL-encode the search query
Function URLEncode(str As String) As String
    Dim bytes() As Byte
    bytes = StrConv(str, vbUnicode)
    Dim i As Integer
    Dim charCode As Integer
    URLEncode = ""
    For i = 0 To UBound(bytes) Step 2
        charCode = bytes(i)
        Select Case charCode
            Case 48 To 57, 65 To 90, 97 To 122, 45, 46, 95, 126
                URLEncode = URLEncode & Chr(charCode)
            Case Else
                URLEncode = URLEncode & "%" & Hex(charCode)
        End Select
    Next i
End Function

Key Explanations #

• Fetching the Page: We use MSXML2.XMLHTTP to get the Google search results page. Adding a User-Agent header is crucial to avoid being blocked by Google's anti-scraping measures.
• Parsing HTML: The HTMLDocument object lets us easily navigate and manipulate the page's DOM, even with all those nested div tags.
• Excluding Class "f" Content: For each st element, we find all child elements with class f and remove them entirely using removeNode True. This ensures their text doesn't end up in our final result.
• URL Encoding: The helper URLEncode function ensures your search query is formatted correctly for the URL (e.g., spaces become %20).

Important Notes #

• Google's page structure can change over time—if the st or f class names get updated, you'll need to adjust the code accordingly. Use your browser's developer tools (F12) to inspect the current elements.
• Avoid making too many rapid requests to Google, as this can lead to temporary IP blocks. Add a delay between requests if you're scraping multiple queries (use Application.Wait Now + TimeValue("00:00:02") for a 2-second wait).
• Make sure your Excel macro settings allow running unsigned macros (adjust in File > Options > Trust Center > Trust Center Settings > Macro Settings).

内容的提问来源于stack exchange，提问作者Eric Capnu