求可用VBA代码：以Excel单元格内容为搜索条件搜索谷歌查排名

Got it, I totally get how tedious that manual copy-paste and rank-checking must be—especially with a ton of product unique identifiers to go through. Since you’re not a tech expert, I’ve put together a straightforward, tested VBA script that should work for your scenario, along with simple steps to set it up.

Prerequisites #

This script uses Internet Explorer (it’s pre-installed on Windows and doesn’t require extra tools, which is perfect for non-experts).

How to Set It Up #

1. Open your Excel file with the product identifiers.
2. Press Alt + F11 to open the VBA Editor.
3. Right-click your workbook name in the left pane > Insert > Module.
4. Paste the code below into the blank module window.

The VBA Code #

Sub CheckGoogleRankings()
    Dim ie As Object
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim searchTerm As String
    Dim rank As Integer
    Dim resultElements As Object
    Dim elem As Object
    
    ' Set the worksheet (change "Sheet1" to your actual sheet name)
    Set ws = ThisWorkbook.Sheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Initialize Internet Explorer
    Set ie = CreateObject("InternetExplorer.Application")
    ie.Visible = True ' Keep IE visible so you can handle captchas if they pop up
    
    For i = 2 To lastRow ' Start at row 2 assuming row 1 is headers
        searchTerm = ws.Cells(i, "A").Value
        rank = 0
        
        If searchTerm <> "" Then
            ' Navigate to Google search
            ie.Navigate "https://www.google.com/search?q=" & Replace(searchTerm, " ", "+")
            
            ' Wait for the page to load
            Do While ie.Busy Or ie.ReadyState <> 4
                DoEvents
            Loop
            
            ' Check if Google threw a captcha
            If InStr(ie.Document.body.innerHTML, "captcha") > 0 Then
                MsgBox "Google asked for a captcha! Please complete it in the open IE window, then click OK to continue.", vbInformation
                ' Wait again after captcha is done
                Do While ie.Busy Or ie.ReadyState <> 4
                    DoEvents
                Loop
            End If
            
            ' Get all search result links
            Set resultElements = ie.Document.querySelectorAll("div.g a")
            
            ' Loop through results to find the first match
            For Each elem In resultElements
                rank = rank + 1
                ' Check if the result contains your exact search term
                If InStr(elem.innerText, searchTerm) > 0 Then
                    Exit For
                End If
            Next elem
            
            ' If no match found, set rank to "Not found"
            If rank = 0 Then
                ws.Cells(i, "B").Value = "Not found"
            Else
                ws.Cells(i, "B").Value = rank
            End If
        Else
            ws.Cells(i, "B").Value = "Empty term"
        End If
    Next i
    
    ' Clean up
    ie.Quit
    Set ie = Nothing
    MsgBox "Rank check completed!", vbInformation
End Sub

Customization Tips (Easy Changes) #

• Sheet Name: Replace "Sheet1" with your actual worksheet name (e.g., "ProductList").
• Columns: If your product IDs are in column C instead of A, change "A" to "C"; if you want results in column D instead of B, change "B" to "D".
• Target Domain Match: If you want to check the rank of your own website for each product term, add this block inside the result loop (replace "yourwebsite.com" with your domain):

  ' Check if the result links to your domain
  If InStr(elem.href, "yourwebsite.com") > 0 Then
      Exit For
  End If
  

How to Run the Script #

1. Go back to your Excel sheet.
2. Press Alt + F8, select CheckGoogleRankings from the list, then click Run.
3. If a captcha pops up in the IE window, complete it and click OK on the message box to resume.

Important Notes #

• Excel might block macros by default—if you see a security warning at the top, click Enable Content to let the script run.
• Google might limit automated searches occasionally, so if you get frequent captchas, take a short break before running the script again.

内容的提问来源于stack exchange，提问作者n.coram