Number of Google Results from Excel

Question

Given a column of strings in Excel, is there an easy way to return the number of results from a Google search for each of the strings? I am looking for a way to convert a list of text data from Excel into Google search results.

Answer 1

Idea From here (but modified):

Public Sub ExcelGoogleSearch()

Dim searchWords As String

With Sheets("Sheet1")
RowCount = 1
Do While .Range("A" & RowCount) <> ""
searchWords = .Range("A" & RowCount).Value

' Get keywords and validate by adding + for spaces between
searchWords = Replace$(searchWords, " ", "+")

' Obtain the source code for the Google-searchterm webpage
search_url = "http://www.google.com/search?hl=en&q=" & searchWords & "&meta="""
Set search_http = CreateObject("MSXML2.XMLHTTP")
search_http.Open "GET", search_url, False
search_http.send
results_var = search_http.responsetext
Set search_http = Nothing

' Find the number of results and post to sheet
pos_1 = InStr(1, results_var, "resultStats>", vbTextCompare)
pos_2 = InStr(3 + pos_1, results_var, ">", vbTextCompare)
pos_3 = InStr(pos_2, results_var, "<nobr>", vbTextCompare)
NumberofResults = Mid(results_var, 1 + pos_2, (-1 + pos_3 - pos_2))
Range("B" & RowCount) = NumberofResults
RowCount = RowCount + 1
Loop
End With
End Sub

HTH

Answer 2

This code needs updating because Google's changed their source code slightly. Here is a code that works as of Nov. 11, 2013 for anyone who needs it (slight other modification, the macro ignores the 1st row so you can have column headers and search results are converted to values so they're ready to manipulate/sort with Excel.

Public Sub ExcelGoogleSearch()

Dim searchWords As String

With Sheets("Sheet1")
RowCount = 2
Do While .Range("A" & RowCount) <> ""
searchWords = .Range("A" & RowCount).Value

' Get keywords and validate by adding + for spaces between
searchWords = Replace$(searchWords, " ", "+")

' Obtain the source code for the Google-searchterm webpage
search_url = "https://www.google.com/search?hl=en&q=" & searchWords & "&meta="""
Set search_http = CreateObject("MSXML2.XMLHTTP")
search_http.Open "GET", search_url, False
search_http.send
results_var = search_http.responsetext
Set search_http = Nothing

' Find the number of results and post to sheet
pos_1 = InStr(1, results_var, "div id=" & Chr(34) & "resultStats", vbTextCompare) + 21
If pos_1 = 21 Then
NumberofResults = 0
Else
pos_2 = InStr(pos_1, results_var, "result", vbTextCompare) - 1
NumberofResults = Val(Replace(Replace(Mid(results_var, pos_1, pos_2 - pos_1), ",", ""), "About", ""))
End If
Range("B" & RowCount) = NumberofResults
RowCount = RowCount + 1
Loop
End With
End Sub

Answer 3

The code above works wonders. On the other hand, it crashes if the searched string brings 0 results. A simple If case solves this. Just fyi.

Public Sub ExcelGoogleSearch()

Dim searchWords As String

With Sheets("Sheet1")
RowCount = 1
Do While .Range("A" & RowCount) <> ""
searchWords = .Range("A" & RowCount).Value

' Get keywords and validate by adding + for spaces between
searchWords = Replace$(searchWords, " ", "+")

' Obtain the source code for the Google-searchterm webpage
search_url = "http://www.google.com/search?hl=en&q=""" & searchWords & """&meta="""
Set search_http = CreateObject("MSXML2.XMLHTTP")
search_http.Open "GET", search_url, False
search_http.send
results_var = search_http.responsetext
Set search_http = Nothing

' Find the number of results and post to sheet
pos_1 = InStr(1, results_var, "resultStats>", vbTextCompare)

If pos_1 = 0 Then
  NumberofResults = 0
Else
  pos_2 = InStr(3 + pos_1, results_var, ">", vbTextCompare)
  pos_3 = InStr(pos_2, results_var, "<nobr>", vbTextCompare)
  NumberofResults = Mid(results_var, 1 + pos_2, (-1 + pos_3 - pos_2))
End If

Range("B" & RowCount) = NumberofResults
RowCount = RowCount + 1
Loop
End With
End Sub