How to get Google Search Results count in Excel?

Everyone who browses internet might have visited "http://www.google.com" atleast once in lifetime. Atleast as of today that is the best search engine available. I know many of the excel users will be curious to play with google search results and excel. So, I will just show you one requirement today and will let you explore the rest. Say, you have a list of values in column A of excel and you want to display the search results count of that list in the corresponding column B. How do you do this?

Everyone who browses internet might have visited "http://www.google.com" atleast once in lifetime. Atleast as of today that is the best search engine available. I know many of the excel users will be curious to play with google search results and excel. So, I will just show you one requirement today and will let you explore the rest. Say, you have a list of values in column A of excel and you want to display the search results count of that list in the corresponding column B. How do you do this? You can definitely go to Google search for the values, check the number of results and paste the result in the adjacent column of the search values. But this is a tedious process if the list is long. So, writing a macro to automate this manual process is the best solution. Check the code below for achieving the desired result.

Code Block

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, "b> of", vbTextCompare)
pos_2 = InStr(3 + pos_1, results_var, ">", vbTextCompare)
pos_3 = InStr(pos_2, results_var, "<", 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

Hope you had fun in getting the Google search results count in excel. Let me know if you have any comments.

By sri kot   Popularity  (4189 Views)