I have an Excel data file with names of people that I would like to search for in Google. The format would be "obituary last name first name town NJ". How could I make a macro to do this? Thanks
Hi Harry,
There are many ways to perform a google search from excel.
You can use Query tables, to get the results into a sheet, then follow the links to go to the results (with a code that will process the results sheet to identify the links). But once you open a link, you will find that it's almost impossible to extract specific items automatically, because each result will have a totally different page structure.
Here is an example, which will take data from active sheet cells A1, B1 and C1 to create the google search string (the results will be returned to Response sheet, you need to create it before running the code):
Sub GetGoogleSearchData()
Dim NewWks As Worksheet
Dim Kwd As String
Kwd = [A1] & "+" & [B1] & "+" & [C1]
Set NewWks = ThisWorkbook.Worksheets("Response")
NewWks.UsedRange.Delete
With NewWks.QueryTables.Add("URL; http://www.google.com/search?q =" & Kwd, NewWks.Cells(1, 1))
.Name = "search?q=" & Kwd
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebDisableDateRecognition = False
.Refresh False
End With
End Sub
Or, you can create an Internet Explorer session and navigate to the string you created from excel:
Sub GoogleSearch()
Dim ie As Object
Dim strKeyword As String
Set ie = CreateObject("InternetExplorer.Application")
strKeyword = [A1] & "+" & [B1] & "+" & [C1]
ie.navigate "http://www.google.com/search?q=" & strKeyword
ie.Visible = True
Do Until ie.readyState = 4: DoEvents: Loop
End Sub
Sending Http requests might be a good solution, but Google restricts search automation, as stated in their Terms of Service.
The best way is to use the Navigate method from Internet explorer, then you will be able to process the HTML page with responses.
