0

This question is very similar to Using VBA in Excel to Google Search in IE and return the hyperlink of the first result

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

enter image description here

Community
  • 1
  • 1
Vlad Terin
  • 1
  • 1
  • 2

1 Answers1

1

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
Seby
  • 123
  • 9