在 Excel 中使用 VBA 进行 Google 搜索关键字并在 Google 上返回索引页面 [英] Using VBA in Excel to Google Search a keyword and return indexed pages on google

问题描述

这个问题非常类似于在Excel中使用VBA到IE中的Google搜索并返回第一个结果的超链接

仅代替第一个链接 - 有没有办法插入索引页面的数量?例子:

Only instead of a first link - is there any way to insert amount of indexed pages? example:

推荐答案

对原始代码稍作修改，我们就可以捕获 resultStats div 的 innerText.Excel 表中的第一列包含要搜索的字符串.大约 1,660 个结果(0.17 秒)"之类的结果将填充在第二列中.如果您只需要像 1660 这样的数字，可以使用简单的字符串函数(instr、mid 等)来解析并获得所需的结果.

With minor modification to the original code we can capture the innerText of the resultStats div. The first column in the excel sheet has the string to be searched. Results like "About 1,660 results (0.17 seconds)" will be filled in the second column. In case you need only the number like 1660, simple string functions (instr, mid etc) can be used to parse and get the required results.

Sub XMLHTTP_Count()    
    Dim url As String, lastRow As Long
    Dim XMLHTTP As Object, html As Object
    Dim start_time As Date
    Dim end_time As Date

    lastRow = Range("A" & Rows.Count).End(xlUp).Row

    Dim cookie As String
    Dim result_cookie As String

    start_time = Time
    Debug.Print "start_time:" & start_time

    For i = 2 To lastRow

        url = "https://www.google.co.in/search?q=" & Cells(i, 1) & "&rnd=" & WorksheetFunction.RandBetween(1, 10000)

        Set XMLHTTP = CreateObject("MSXML2.XMLHTTP")
        XMLHTTP.Open "GET", url, False
        XMLHTTP.setRequestHeader "Content-Type", "text/xml"
        XMLHTTP.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1; rv:25.0) Gecko/20100101 Firefox/25.0"
        XMLHTTP.send

        Set html = CreateObject("htmlfile")
        html.body.innerHTML = XMLHTTP.ResponseText

    If html.getElementById("resultStats") Is Nothing Then
        str_text = "0 Results"
    Else
        str_text = html.getElementById("resultStats").innerText
    End If
        Cells(i, 2) = str_text
        DoEvents
    Next

    end_time = Time
    Debug.Print "end_time:" & end_time

    Debug.Print "done" & "Time taken : " & DateDiff("n", start_time, end_time)
    MsgBox "done" & "Time taken : " & DateDiff("n", start_time, end_time)
End Sub

这篇关于在 Excel 中使用 VBA 进行 Google 搜索关键字并在 Google 上返回索引页面的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！