The number of Google results from Excel

Question

Given the row column in Excel, is there an easy way to return the number of Google search results for each row? I am looking for a way to convert a list of text data from Excel to 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 

NTN

Answer 2

This code needs to be updated as Google has slightly modified the source code. Here is the code that has been working since November 11, 2013 for everyone who needs it (a small other modification, the macro ignores the 1st row so that the column headers and search results are converted to values ​​so that they are ready to be manipulated / sorted in 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 above code works wonders. On the other hand, it crashes if the search string yields 0 results. A simple case resolves 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