Learn How to Create and Control Chrome Objects with VBA

VBA stands for Visual Basic for Applications, which is a programming language that allows you to automate tasks in Excel and other Microsoft Office applications. You can use VBA to create macros, which are sequences of commands that can be executed by a single click or a keyboard shortcut. You can also use VBA to create user-defined functions, which are custom formulas that can perform complex calculations or operations.

One of the advantages of VBA is that it can interact with other applications, such as web browsers, through the use of objects. An object is a component of a program that has properties (attributes) and methods (actions). For example, a worksheet is an object that has properties like name, cells, range, etc., and methods like activate, copy, paste, etc. You can access and manipulate the properties and methods of an object using the dot (.) operator. For example, Worksheets("Sheet1").Name returns the name of the worksheet named “Sheet1”, and Worksheets("Sheet1").Activate activates the worksheet named “Sheet1”.

To interact with Google Chrome, you need to create an object that represents the Chrome browser. You can do this by using the CreateObject function, which takes the name of the application as an argument. For example, Set chrome = CreateObject("Chrome.Application") creates an object named chrome that represents the Chrome browser. You can then use the properties and methods of the chrome object to control the browser, such as navigating to a URL, clicking a button, entering a text, etc.

Procedures

To create VBA on excel to search values on Google using Chrome browser, you need to follow these steps:

1. Enable the Developer tab in Excel, which allows you to access the Visual Basic Editor and other tools for working with VBA. You can do this by going to File > Options > Customize Ribbon and checking the Developer box in the right pane.
2. Open the Visual Basic Editor by clicking the Visual Basic button in the Developer tab or pressing Alt + F11. This is where you write and edit your VBA code.
3. Insert a new module by going to Insert > Module. A module is a container for your VBA code. You can name your module by changing the (Name) property in the Properties window.
4. Write a sub procedure, which is a block of code that performs a specific task. You can name your sub procedure by typing Sub followed by the name of your procedure and ending with End Sub. For example, Sub SearchGoogle() creates a sub procedure named SearchGoogle.
5. Declare the variables that you will use in your code, such as the Chrome object, the worksheet object, the range object, the search value, etc. You can declare a variable by using the Dim keyword followed by the name of the variable and the data type. For example, Dim chrome As Object declares a variable named chrome as an object. You can also use the Set keyword to assign an object to a variable. For example, Set chrome = CreateObject("Chrome.Application") assigns the Chrome object to the chrome variable.
6. Loop through each cell in column A of the worksheet that contains the values that you want to search on Google. You can use the For Each loop to iterate over a collection of objects, such as a range of cells. You can use the Range property of the worksheet object to specify the range that you want to loop through. For example, For Each cell In Worksheets("Sheet1").Range("A1:A10") loops through each cell in the range A1:A10 of the worksheet named “Sheet1”.
7. For each cell, navigate to the Google search page using the Navigate method of the Chrome object. You can use the Value property of the cell object to get the value of the cell. You can also use the & operator to concatenate strings, such as the Google search URL and the search value. For example, chrome.Navigate "https://www.google.com/search?q=" & cell.Value navigates to the Google search page with the value of the cell as the query.
8. Wait for the page to load using the ReadyState property of the Chrome object. You can use the Do While loop to repeat a block of code until a condition is met. You can use the <> operator to check for inequality. For example, Do While chrome.ReadyState <> 4 repeats the loop until the ReadyState property of the chrome object is equal to 4, which means that the page is fully loaded.
9. Save the page as a PDF file using the SaveAs method of the Chrome object. You can use the folderPath variable to specify the folder where you want to save the PDF files. You can also use the cell.Value variable to name the PDF file according to the search value. For example, chrome.SaveAs folderPath & cell.Value & ".pdf" saves the page as a PDF file with the name of the search value in the specified folder.
10. Close the Chrome browser using the Quit method of the Chrome object. For example, chrome.Quit closes the browser.

Explanation

The following code is a comprehensive explanation of the VBA on excel to search values on Google using Chrome browser, including the basics and comments.

Sub SearchGoogle()
    ' Declare the variables
    Dim chrome As Object ' The Chrome object
    Dim ws As Worksheet ' The worksheet object
    Dim cell As Range ' The cell object
    Dim folderPath As String ' The folder path
    
    ' Set the Chrome object
    Set chrome = CreateObject("Chrome.Application")
    
    ' Set the worksheet object
    Set ws = Worksheets("Sheet1") ' Change the name of the worksheet as needed
    
    ' Set the folder path
    folderPath = "C:\Users\YourUsername\Desktop\PDFs\" ' Change the folder path as needed
    
    ' Loop through each cell in column A
    For Each cell In ws.Range("A1:A" & ws.Cells(ws.Rows.Count, 1).End(xlUp).Row)
        ' Navigate to the Google search page with the cell value as the query
        chrome.Navigate "https://www.google.com/search?q=" & cell.Value
        
        ' Wait for the page to load
        Do While chrome.ReadyState <> 4
            DoEvents ' Allow other processes to run
        Loop
        
        ' Save the page as a PDF file with the name of the cell value in the folder path
        chrome.SaveAs folderPath & cell.Value & ".pdf"
    Next cell
    
    ' Close the Chrome browser
    chrome.Quit
End Sub

Example

To illustrate how the VBA on excel works, let’s consider a scenario where you have a list of company names in column A of the worksheet named “Sheet1”, and you want to search each company name on Google and save the results as PDF files in the folder named “PDFs” on your desktop. The following table shows an example of the worksheet and the folder.

To use the VBA on excel, you need to do the following steps:

1. Enable the Developer tab in Excel and open the Visual Basic Editor.
2. Insert a new module and name it “SearchGoogle”.
3. Copy and paste the code from the comprehensive explanation section into the module.
4. Change the name of the worksheet and the folder path as needed. In this example, the worksheet name is “Sheet1” and the folder path is “C:\Users\YourUsername\Desktop\PDFs”.
5. Run the sub procedure by clicking the Run button in the Visual Basic Editor or pressing F5. Alternatively, you can assign a keyboard shortcut or a button to the sub procedure in Excel.
6. Wait for the VBA on excel to finish running. You should see the Chrome browser opening and closing for each company name in column A, and the PDF files being saved in the folder named “PDFs” on your desktop.

Result

The result of the VBA on excel is a set of PDF files that contain the Google search results for each company name in column A. The PDF files are named according to the company names and are saved in the folder named “PDFs” on your desktop. The following table shows an example of the PDF files and their contents.