Excel VBA CodeTutorials

Perform Google Search on Internet Explorer using Excel VBA

3 Mins read

First time in my life I have come across to create this type of automation and finally, I got success.

How to search data in Google using Excel VBA

Excel VBA Macros for Non-Coders

Do you find VBA Macros scary? Did you miss any job opportunity because of it? Get started today with our eBook guide on using – Excel VBA Macros. 140 pages of rich visuals. Download now.

For Example:

  • Write “Apple” in the Google search box
  • Press Enter↵
  • It will show you the statistics that how many records it has populated and also how much time it has taken to fetch the records.
Picture-1 Google Search

We will do the same using Excel VBA. Isn’t it interesting?

I also didn’t have any idea about it. But by googling I got the idea and learned that you can do almost anything with excel VBA & wanted to share it with you. If you open any page in IE (Internet Explorer) and you press F12 then you will get the HTML of that page.

All you need to do is you will have to take the id of the specific box. First Open Google in IE and then press F12. You will move to another HTML page. The page will look like below ⇓

Picture-2 HTML Page
(Picture 2) Google search using Excel VBA

Now Press on the arrow (Red circle in Picture 2). And you are forced to go back to the Google page. Then place your on the Google search box and then click on the box. As soon as you click on the box again you will be on the HTML page and this time, you will get everything off that box like name, title, id, etc.

Picture-3 HTML Box
(Picture 3) google search using Excel VBA

Now we need to use the id (“lst-ib”) in our code. And in this way, you will get the id of any DOM object. Sometimes “id” is not available and then we need to use a name in our code. Now you will come to our excel file. We have some inputs in our excel file. All we need to do is we need to open Google and will have to fetch the statistics of the search result. Our input in the excel file is like the below picture

Picture-4 Input in Excel File
(Picture 4) google search using Excel VBA

Now we will take them one by one keyword and search the keyword in Google and after opening the other page we will fetch the result.

Please follow the below steps to get the result:

  1. Open the VBA page by pressing ALT + F11
  2. Go to Insert and then Module
  3. Copy the below code and paste it into the Module
  4. Go to Tools and then Reference and select all the references as shown in the Picture
  5. Run the code by pressing F5 or from the Run button
Picture-5 Refrence Box
(Picture 4) google search using Excel VBA

Here is the code

Sub SearchGoogle()
Dim ie As Object
Dim form As Variant
Dim button As Variant
Dim LR As Integer
Dim var As String
Dim var1 As Object
LR = Cells(Rows.Count, 1).End(xlUp).Row
For x = 2 To LR
var = Cells(x, 1).Value
Set ie = CreateObject(“internetexplorer.application”)
ie.Visible = True
With ie
    .Visible = True
    .navigate "http://www.google.co.in"
    While Not .readyState = READYSTATE_COMPLETE
Wend
End With
    'Wait some to time for loading the page
While ie.Busy
    DoEvents
Wend
Application.Wait (Now + TimeValue("0:00:02"))
ie.document.getElementById(“lst - ib”).Value = var
    'Here we are clicking on search Button
Set form = ie.document.getElementsByTagName(“form”)
Application.Wait (Now + TimeValue("0:00:02"))
Set button = form(0).onsubmit
form(0).submit
    'wait for page to load
While ie.Busy
DoEvents
Wend
Application.Wait (Now + TimeValue("0:00:02"))
Set var1 = ie.document.getElementById(“resultStats”)
Cells(x, 2).Value = var1.innerText
ie.Quit Set ie = Nothing Next
End Sub

Your code will look like below:

Picture-6 Coding
(Picture 6) google search using Excel VBA

You will notice that the code will open your IE browser in a new window and then search all the keywords one by one and fetch the result and paste the result into an excel sheet when it is done it will close the browser.

Finally when it is done then go back to the excel sheet and it will find that it has copied the search result and pasted in desired cells.

Picture-7 Answers in Excel Cells
(Picture 7) SEARCH RESULTS USING EXCEL VBA

What I have done is I have created an object called Internet Explorer and then navigated the page to Google. I waited for some time there until and unless this page comes to a ready state and then finally I used the ids to fetch the records.

Hope you have learned something new from my blog. Please go through the code and try to understand the logic. Also please let us know your feedback. Your comment will help to write some interesting blogs like this. You can learn more about our Advanced Excel VBA tutorials.

Excel VBA Macros for Non-Coders

Do you find VBA Macros scary? Did you miss any job opportunity because of it? Get started today with our eBook guide on using – Excel VBA Macros. 140 pages of rich visuals. Download now.

Related posts
AlteryxTutorials

Alteryx Hotkeys - Alteryx Keyboard Shortcuts

2 Mins read
Top 50+ Alteryx Shortcuts for Windows. Alteryx is popularly known as a Self-Service Analytics tool. Business users can build their data workflows…
AlteryxTutorials

Step By Step Guide to Learn Alteryx

6 Mins read
Alteryx Learning Path: The growth in technology has resulted in growth in understanding. In today’s world, humans – fellow businessmen know the…
Excel VBA CodeTutorials

VBA Code to Clean the Date Format

1 Mins read
When it is useful? Most of the time the most annoying problem is when the data is taken from ERP or other…

2 Comments

Comments are closed.