Macro | General Excel Questions & Answers | Excel Forum • My Online Training Hub

Macro

Harry Fisher

New Member

Members

Forum Posts: 1

Member Since:
April 27, 2014

Offline

February 13, 2017 - 9:45 am

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

Catalin Bombea

Iasi, Romania

Admin

Forum Posts: 403

Member Since:
November 8, 2013

Offline

February 19, 2017 - 6:06 pm

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.