Automate Google Search via Excel

Dear Forum,

I am in need of expediting a task which I have to do almost on a daily basis, I am an analyst and have to research on different topics depending on the nature of each project..

Presently, I am working on a project dealing with some chemicals and checking for different Buyers and Suppliers..

I get the names of these Chemicals in an plain Excel Sheet one below the other and I have to research on each chemical and its different buyers and suppliers ( i.e. Dealers)

Now I have to everytime go in the Excel Sheet and then open a New Window - GOOGLE to perform the same...

I would appreciate if there could be some easier way if possible through VBA which is my perennial weakness..

Please find the attached file for your perusal, I have just put 10 names however I get about 35-45 names on a daily basis and therefore its very tedious..

Hello e4excel,

I have added two macros to your workbook to automate the process. Now whenever a cell in column "C" changes to "Search", the contents of cell "B" in the same row are used as the Google search term.

The second macro starts Internet Explorer, opens to Google's search page, fills it in with data from the cell, and clicks the search button. Each search is opened in the same window. You can then use the back and forward buttons to review your searches.

Sheet2 Worksheet_Change() Macro Code

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub
    
    If Not Intersect(Target, Range("C2:C" & Rows.Count)) Is Nothing Then
       ThisWorkbook.GoogleSearch Target.Offset(0, -1).Text
    End If
    
End Sub

ThisWorkbook Module Code

'Written: July 21, 2010
'Author:  Leith Ross
'Summary: Opens Internet Explorer to Google's search page. It then automatically inserts
'         the search term and clicks the search button. New searchs are opened in the
'         same window.

Public ieApp As Object

Sub GoogleSearch(ByVal SearchTerm As String)

  Dim ieDoc As Object
  Dim InputBox As Object
  Dim SearchButton As Object
   
    If TypeName(ieApp) = "Object" Or TypeName(ieApp) = "Nothing" Then
        Set ieApp = CreateObject("InternetExplorer.Application")
        ieApp.Navigate2 "www.google.com", 2048
    End If
  
      While ieApp.ReadyState <> 4
        DoEvents
      Wend
      
      ieApp.Visible = True
      
      Set ieDoc = ieApp.Document
    
        Set InputBox = ieDoc.getElementsByName("q")
        Set SearchButton = ieDoc.getElementsByName("btnG")
        
        InputBox.Item(0).Value = SearchTerm
        SearchButton.Item(0).Click
        
      Set ieDoc = Nothing
      
End Sub

Hello e4excel,

Can you post a copy of your file in Excel 2003 format?

Hello e4excel,

There were no macros in the workbook. I have added them back in. Be sure to enable macros in Excel 2007. If you don't nothing will happen. Click here if you aren't sure how do this.

Maybe this might help

Does this work?

Dear RoyUk,

I am extremely happy to have got his working, however there's only one more thing if i can ask is..

By default we use GOOGLE for everything but there are times when we also use another site called www.alibaba.com..

I took the liberty to change that in your code it did open the file but could not paste the Search Keyword and also I got an error!!!!

What I did is to temporarily comment the below:

Option Explicit



Can I add a Different Search Like Google and also Alibaba both in the dropdown..so that I can use both their functionalities

Sub GetResults(r As Range)
    Dim IE As Object
    Dim frm As Object
    Dim srch As Object
    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = True
   ' IE.Navigate "http://www.google.com"
    IE.Navigate "http://www.alibaba.com" ' New Line

    Do While IE.Busy: DoEvents: Loop
    Do While IE.ReadyState <> 4: DoEvents: Loop
    Set frm = IE.Document.forms("f")
    Set srch = frm.Document.all("q")
    srch.Value = r.Value
    frm.submit
End Sub

However, I am getting an Error 91:

Object variable or With block Variable not Set


Can I have both the Search Sites in use as both are useful to me! by putting them in the Drop-down as below:

GOOGLE Search
Alibaba Search

instead of just Search...?

Hello e4excel,

I did some more reading on the Explorer code and I believe that this code will work through Internet Explorer 7. I added to your drop down list. You can search by Google or Alibaba. The macro will open a new tab in the same window. The drop down also includes "Suppliers" and "Manufacturers". Even if this code doesn't run, it will still be a good template to draw from.

Sheet2 Worksheet_Change() Event Code

Private Sub Worksheet_Change(ByVal Target As Range)

  Dim I As Long
  Dim SearchTerms As String
  
    If Target.Cells.Count > 1 Then Exit Sub
    
    If Not Intersect(Target, Range("C2:C" & Rows.Count)) Is Nothing Then
       SearchTerms = Target.Offset(0, -1).Text
       I = InStr(1, Target, "+")
       If I Then SearchTerms = SearchTerms & " " & Right(Target, Len(Target) - I)
       
       Select Case Target.Text
         Case Is = "Search Google", "Search Google + Suppliers", "Search Google + Manufacturers"
           InternetSearch SearchTerms, "www.google.com"
         Case Is = "Search Alibaba", "Search Alibaba + Suppliers", "Search Alibaba + Manufacturers"
           InternetSearch SearchTerms, "www.alibaba.com"
       End Select
    End If
    
End Sub

Module Macro Code

Public ieApp As Object

Sub InternetSearch(ByVal SearchTerm As String, ByVal SearchEngine As String)

  Dim ieDoc As Object
  Dim InputBox As Object
  Dim SearchButton As Object
  Dim URL As String
   
    
    If TypeName(ieApp) = "Object" Or TypeName(ieApp) = "Nothing" Then
       Set ieApp = CreateObject("InternetExplorer.Application")
       ieApp.Visible = True
       GoSub OpenNewTab
    End If
      
      Set ieDoc = ieApp.Document
      
      If ieDoc.Domain <> "" And ieDoc.Domain <> SearchEngine Then GoSub OpenNewTab
      
        Select Case ieDoc.Domain
          Case Is = "www.google.com"
            Set InputBox = ieDoc.getElementsByName("q")
            Set SearchButton = ieDoc.getElementsByName("btnG")
              InputBox.Item(0).Value = SearchTerm
              SearchButton.Item(0).Click
          Case Is = "alibaba.com"
            Set InputBox = ieDoc.getElementById("SearchTextIdx")
            Set SearchButton = ieDoc.getElementById("searchSubmit")
              InputBox.Value = SearchTerm
              SearchButton.Click
        End Select
        
      Set ieDoc = Nothing
      
Exit Sub

OpenNewTab:
       While ieApp.Busy
         DoEvents
       Wend
       
       ieApp.Navigate SearchEngine, 2048
       
       While ieApp.Busy
         DoEvents
       Wend
Return

End Sub