Selenium VBA Excel Tutorial (Chrome Web Scraping)

What is Data Scraping Using Selenium?

Selenium is an automation tool that facilitates scraping information from HTML web pages, performing web scraping with Google Chrome. In Excel, the Selenium type library lets VBA drive a real browser instead of the deprecated Internet Explorer engine.

How to Prepare the Excel Macro Before Data Scraping Using Selenium

There are certain prerequisites that must be performed on the Excel macro file before starting the data scraping process.

These prerequisites are as follows:

Step 1) Open an Excel-based macro and access the Developer option of Excel.

Step 2) Select the Visual Basic option under the Developer ribbon.

Step 3) Insert a new module.

Step 4) Initialize a new subroutine and name it test2.

Sub test2()
End Sub

The result in the module would be as follows:

Step 5) Access the Reference option under the Tools tab and reference the Selenium Type Library. This library helps open Google Chrome and facilitates macro scripting.

Now the Excel file is ready to interact with the browser. The next step is to incorporate a macro script that facilitates data scraping in HTML.

How to Open Google Chrome Using VBA

Here are the steps to open Google Chrome using VBA.

Step 1) Declare and initialize the variables in the subroutine as shown below.

Sub test2()
Dim driver As New WebDriver
Dim rowc, cc, columnC As Integer

Step 2) To open Google Chrome using Selenium and VBA, write driver.Start “Chrome” and press F5.

The following would be the code.

Sub test2()
Dim driver As New WebDriver
Dim rowc, cc, columnC As Integer
driver.Start "Chrome"
Application.Wait Now + TimeValue("00:00:20")
End Sub

The module would result as follows:

How to Open a Website in Google Chrome Using VBA

Once you can access Google Chrome using VBA, the next step is to access a website using VBA. This is facilitated by the Get function, where the URL is passed as double quotes.

The module would look as follows:

Press F5 to execute the macro. The following webpage would open in Google Chrome as shown.

Sub test2()
Dim driver As New WebDriver
Dim rowc, cc, columnC As Integer
driver.Start "Chrome"
driver.Get "https://demo.guru99.com/test/web-table-element.php"
Application.Wait Now + TimeValue("00:00:20")
End Sub

Now the Excel macro is ready to perform the scraping tasks. The next step shows how the information can be extracted by applying Selenium and VBA.

How to Scrape Information from a Website Using VBA

Suppose the day trader wants to access the data from the website daily. Each time the day trader clicks the button, it should auto-pull the market data into Excel.

From the above website, it is necessary to inspect an element and observe how the data is structured. Access the source code of the HTML below by pressing Control + Shift + I.

<table class="datatable">
<thead>
<tr>
<th>Company</th>
<th>Group</th>
<th>Pre Close (Rs)</th>
<th>Current Price (Rs)</th>
<th>% Change</th>
</tr>

As you can see, the data is structured as a single HTML table. Therefore, to pull the entire data from the HTML table, you must design a macro that pulls the header information and the corresponding data associated with the table. Perform the tasks below.

Step 1) Formulate a For loop that runs through the HTML header information as a collection. The Selenium driver locates the header information using the FindElementByClass() and FindElementByTag() methods, as shown.

Sub test2()
Dim driver As New WebDriver
Dim rowc, cc, columnC As Integer
rowc = 2
Application.ScreenUpdating = False
driver.Start "chrome"
driver.Get "https://demo.guru99.com/test/web-table-element.php"
For Each th In driver.FindElementByClass("dataTable").FindElementByTag("thead").FindElementsByTag("tr")
cc = 1
For Each t In th.FindElementsByTag("th")
Sheet2.Cells(1, cc).Value = t.Text
cc = cc + 1
Next t
Next th

Step 2) Next, the Selenium driver locates the table data using a similar approach. Write the following code:

Sub test2()
Dim driver As New WebDriver
Dim rowc, cc, columnC As Integer
rowc = 2
Application.ScreenUpdating = False
driver.Start "chrome"
driver.Get "https://demo.guru99.com/test/web-table-element.php"
For Each th In driver.FindElementByClass("dataTable").FindElementByTag("thead").FindElementsByTag("tr")
cc = 1
For Each t In th.FindElementsByTag("th")
Sheet2.Cells(1, cc).Value = t.Text
cc = cc + 1
Next t
Next th
For Each tr In driver.FindElementByClass("dataTable").FindElementByTag("tbody").FindElementsByTag("tr")
columnC = 1
For Each td In tr.FindElementsByTag("td")
Sheet2.Cells(rowc, columnC).Value = td.Text
columnC = columnC + 1
Next td
rowc = rowc + 1
Next tr
Application.Wait Now + TimeValue("00:00:20")
End Sub

Excel can be initialized using the Range attribute or the Cells attribute of the sheet. To reduce the complexity of the VBA script, the collection data is initialized to the Cells attribute of Sheet 2 in the workbook. The Text attribute helps get the text placed under an HTML tag.

Sub test2()
Dim driver As New WebDriver
Dim rowc, cc, columnC As Integer
rowc = 2
Application.ScreenUpdating = False
driver.Start "chrome"
driver.Get "https://demo.guru99.com/test/web-table-element.php"
For Each th In driver.FindElementByClass("dataTable").FindElementByTag("thead").FindElementsByTag("tr")
cc = 1
For Each t In th.FindElementsByTag("th")
Sheet2.Cells(1, cc).Value = t.Text
cc = cc + 1
Next t
Next th
For Each tr In driver.FindElementByClass("dataTable").FindElementByTag("tbody").FindElementsByTag("tr")
columnC = 1
For Each td In tr.FindElementsByTag("td")
Sheet2.Cells(rowc, columnC).Value = td.Text
columnC = columnC + 1
Next td
rowc = rowc + 1
Next tr
Application.Wait Now + TimeValue("00:00:20")
End Sub

The VBA module would look as follows:

Step 3) Once the macro script is ready, assign the subroutine to an Excel button and exit the VBA module. Label the button as Refresh or any suitable name. For this example, the button is labeled Refresh.

Step 4) Press the Refresh button to get the output below.

Step 5) Compare the results in Excel with the results in Google Chrome.

FAQs