+ Reply to Thread
Results 1 to 24 of 24

Automate Google Search via Excel

  1. #1
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    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..
    Attached Files Attached Files
    Last edited by e4excel; 07-22-2010 at 11:51 AM.

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Automating Google Search through an Excel File!

    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
    Please Login or Register  to view this content.

    ThisWorkbook Module Code
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Automating Google Search through an Excel File!

    Dear Leith,

    I liked the concept however nothings happenning, I dont know why..please check I have done the Enable Macros after opening the file.

    But nothing seemed to work after I use the drop-dow search...

    Please let me know if I am missing anything!

    Warm Regards
    e4excel

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Automating Google Search through an Excel File!

    Hello e4excel,

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

  5. #5
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Automating Google Search through an Excel File!

    Hi Leith,

    Please find the attached file..I think the earlier one too was in 97-2003 however check this out..!

    Thanks for helping..
    Attached Files Attached Files

  6. #6
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Automating Google Search through an Excel File!

    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.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Automating Google Search through an Excel File!

    Quote Originally Posted by Leith Ross View Post
    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.
    Ok I got you , No I posted the same file thought you were not able to open the first file but even after saving the new file nothing has changed..

    What am I supposed to do?

    I am ENabling the Macro button the very first thing after opening the file and then I just change the value of C2 to Search and then nothing happens..

    Is there something which I am missing..?????

  8. #8
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Automating Google Search through an Excel File!

    Hello e4excel,

    It may be the version of Internet Explorer. The macro works with IE 6, but I don't know it it will work successfully with IE7 and up.

  9. #9
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Automating Google Search through an Excel File!

    Quote Originally Posted by Leith Ross View Post
    Hello e4excel,

    It may be the version of Internet Explorer. The macro works with IE 6, but I don't know it it will work successfully with IE7 and up.
    Yes I think you may be right I think I might have upgraded my IE browser to 8 or something..

    IS there a way out to make it work for anything ...

  10. #10
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Automating Google Search through an Excel File!

    Maybe this might help
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  11. #11
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Automating Google Search through an Excel File!

    Dear Roy,

    Yes maybe this is useful too howver I was looking for a personalized one as at work add-ins are a strict no-no..and this customised utility would serve the purpose without any breach..

    Wish i could use this utility which leith had made!

  12. #12
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Automating Google Search through an Excel File!

    Does this work?
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Automating Google Search through an Excel File!

    Dear Gurus,

    Thanks a lot RoyUk, it does work and I am sure Leith's file would work too , however I am not able to check the same due to IE 8 presence..

    But I have added to both your reputations thanks alot, this is indeed going to save a lot of time!

    P.S. RoyUk I hope this file would work irrespective of the Internet Explorer!

  14. #14
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Automating Google Search through an Excel File!

    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:

    Please Login or Register  to view this content.

    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...?
    Last edited by e4excel; 07-22-2010 at 10:08 AM.

  15. #15
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Automating Google Search through an Excel File!

    Dear RoyUk,

    Sorry to bother again , however for faster search sometimes we also use like this :

    i.e. Keyword + GOOGLE + ALI BABA + MANUFACTURERS

    Now the keyword is any value or chemical name in this file appended with the SEarch Engines + An additional Criterion

    Warm regards
    e4excel

  16. #16
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Automate Google Search via Excel

    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
    Please Login or Register  to view this content.

    Module Macro Code
    Please Login or Register  to view this content.
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Automate Google Search via Excel

    Dear leith,

    Thanks for the file again, I have not been able to check the earlier file but this works however it does not work when I select only

    Search Google
    Search Google + Suppliers
    Search Google + Manufacturers
    Search Alibaba

    It does open the Windows but without the keyword..

    Can we send the values in the Search Dynamically instead of hardcoding them so that we can create a list of different browsers.


    I checked your code I really like it but I am no good in VBA but just thought if this was possible..

  18. #18
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Automate Google Search via Excel

    Hello e4excel,

    To bad I don't have a 2007 system with Internet Explorer 8 then I could troubleshoot this better. It works perfectly on my system. Perhaps I didn't understand how you wanted the search terms set up. Internet Explorer can only open 1 search engine at time though either in different tabs or separate instances. Can you provide an example of how you want the terms entered and where they will come from?

  19. #19
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Automate Google Search via Excel

    Quote Originally Posted by Leith Ross View Post
    Hello e4excel,

    To bad I don't have a 2007 system with Internet Explorer 8 then I could troubleshoot this better. It works perfectly on my system. Perhaps I didn't understand how you wanted the search terms set up. Internet Explorer can only open 1 search engine at time though either in different tabs or separate instances. Can you provide an example of how you want the terms entered and where they will come from?
    Dear Leith,

    To tell you very honestly, my requirements changed after I got the wonderful solutions from both of you..
    Initially, I just needed to go on a Search Page with the Keyword already entered, however after that I was told by my superiors to also check the details for suppliers /Manufactures from a site www.alibaba.com and therefore the new requirement of having GOOGLE as well as ALIBABA....

    This requirement was purely based on a everyday neccessity to do research and therefore save time and so we come up with this need of having it as

    i.e. Keyword + GOOGLE + MANUFACTURERS

    i.e. Keyword + ALI BABA + MANUFACTURERS..

    But after looking at your code just thought that can we provide the name of the site to be searched Dynamaically..

    AS this Excel Search is a very good Utility and therefore need not be limited to only GOOGLE..


    CAn we Dynamically pass the site name in the code..

    Lets say, the way you made a list we make a Dynamic List in some other column where we write the site name first as just GOOGLE and not www.google.com the GOOGLE showuldd get fir in the www.XXXXX.com + YYYY.

    Now the YYY can be anything such as Suppliers or even Images...

    Then this file can be made more generic..

    All this is a new development as this is indeed working and am really thankful and grateful to both you stalwarts for your wonderful help...

    Leith, the last file works but does not work for a few conditions as mentioned earlier

    Search Google
    Search Google + Suppliers
    Search Google + Manufacturers
    Search Alibaba

    I am attaching a new file without any coding with some changes mentioned above...
    Attached Files Attached Files

  20. #20
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Automate Google Search via Excel

    Waiting for a response

  21. #21
    Forum Contributor trucker10's Avatar
    Join Date
    07-22-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2003 / 2007 / 2010 prof +
    Posts
    149

    Re: Automate Google Search via Excel

    try to using a Web query without VBA
    Please Login or Register  to view this content.

  22. #22
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Automate Google Search via Excel

    Quote Originally Posted by trucker10 View Post
    try to using a Web query without VBA
    Please Login or Register  to view this content.
    This link did not open in anything so please explain in details as I am not good in VBA!

  23. #23
    Forum Contributor trucker10's Avatar
    Join Date
    07-22-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2003 / 2007 / 2010 prof +
    Posts
    149

    Re: Automate Google Search via Excel

    a Web query is not VBA , I can give you an example from my google maps .Maybe someone can make it for you .
    Mr. SNB has posted this on a Dutch forum ( snb is a member of this forum )
    Attached Images Attached Images

  24. #24
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Automate Google Search via Excel

    Dear Leith,

    I am still waiting for a positive response from your end! As your last post was more towards a feasible solution!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1