Excel VBA: Web Scraping with Chrome (With Easy Steps)

To demonstrate how to use Excel VBA to scrape data from a website using the Chrome browser, we’ll use this article from ExcelDemy as the dataset, and scrape the following table from the website.

Step 1 – Install Selenium

In order to scrape data from websites in Excel, the Selenium library must be installed.

Go to the GitHub website.

Click on the marked portion in the following image to download SeleniumBasic.

Double-click on the downloaded file to install SeleniumBasic.

A Setup window will appear.
Select Next.

Select I accept the agreement.
Select Next.

Another window will appear.

Click on the marked drop-down option.
Select Compact installation.

Click Next.

You will see the path of the Install folder. Make a note of it, we’ll need it later.
Select Install.

Selenium will be installed.

Select Finish.

Step 2 – Download Chrome Driver

To scrape data from Chrome in Excel, we need the Chrome Driver.

Go to the ChromeDriver site to download it.

Before downloading, you will need to know the Chrome version you are using.

To check your version, in Chrome click on the Three Dot button.
Select Help.
Select About Google Chrome.

Make a note of the Chrome version.

Download the ChromeDriver for your Chrome version by clicking on the appropriate link.

This will lead you to the download link.

Download the driver for your operating system.

In your File Explorer, select the downloaded zip file.
Right-click on the selected file.
Select Extract to chromedriver_win32(1)\.

Open the folder that will be created.

Select the file and copy it by pressing Ctrl + C on your keyboard.

Go to the your install folder.

C:\Users\HP\AppData\Local\SeleniumBasic

Press Ctrl + V to paste the ChromeDriver in this folder.

Step 3 – Refer to Selenium Library in VBA

Now, we refer to the Selenium Library in VBA.

Go to the Developer tab.
Select Visual Basic.

The Visual Basic editor window will open.
Select Tools.
Select References.

The References dialog box will appear.
Select Selenium Type Library.
Select OK.

Step 4 – Inspect Data Structure from Web

Before writing the code we must inspect the elements of the website we’ll be scraping and observe how the data are structured.

Right-click on the table from where you want to scrape data.
Select Inspect.

You will be able to see how the data is structured and see the class, name, and tag of the data. These pieces of information will be needed to write the VBA code.

Step 5 – Write VBA Code

Now we have everything we need to write the VBA code for scraping web with Chrome in Excel.

Select the Insert tab.
Select Module.

A Module will open.

Enter the following code in the Module:

Sub scraping_web()
Dim chrm As Selenium.ChromeDriver
Dim row_no, col_no As Integer
row_no = 2
Application.ScreenUpdating = False
Set chrm = New Selenium.ChromeDriver
    chrm.Start
    chrm.Get "https://www.exceldemy.com/excel-vba-translate-formula-language/"
For Each tr In chrm.FindElementByClass("wpsm-table"). _
FindElementByTag("tbody").FindElementsByTag("tr")
    col_no = 2
    For Each td In tr.FindElementsByTag("td")
        Sheet1.Cells(row_no, col_no).Value = td.Text
        col_no = col_no + 1
    Next td
    row_no = row_no + 1
Next tr
Application.Wait Now + TimeValue("00:00:20")
End Sub

How Does the Code Work?

We created a Sub Procedure named scraping_web.
We declared a variable named chrm as Selenium.ChromeDriver.
We declared two more variables as Integer.
We used the Application.ScreenUpdating property to speed up the macro.
We set chrm as New Selenium.ChromeDriver.
We used chrm.Start to start the Chrome browser.
We used chrm.Get to open the web page we want, by providing the URL of the page.
We used a For Next Loop to get the data from the table.
We used the FindElementByClass and FindElementsByTag methods to get the table elements.
We used the Application.Wait method to pause running the macro for a specified amount of time.
We ended the Sub Procedure.

Save the code and go back to your worksheet.

Step 6 – Insert Button and Assign Macro

To conveniently run the code, we’ll insert a Button and assign a Macro to it.

Go to the Developer tab.
Select Insert.
Select Button from Form Controls.

Click and drag your mouse cursor to where you want the button.

The Assign Macro dialog box will appear.

Select scraping_web as the Macro name.
Click OK to assign the Macro to the button.

Right-click on the button.
Select Edit Text.

Edit the text label for the button as desired. Here, we enter Scrape Data.

Our button looks like this:

Step 7 – Run VBA Code

Click on the Button.

The data has been scraped from the table into Sheet 1.

Format the table according to your preference.

Final Output

Alternative Way to Scrape Table Data from Web in Excel

There is an an alternative way of scraping the web using Excel VBA without the Chrome driver.

Steps:

Open the Visual Basic Editor window by following Step 3.
Open a Module by following Step 5.
Enter the following code in the Module:

Option Explicit
Public Sub calling_sub()
    Call clear_sheet
    Call Scraping_Table
End Sub
Private Sub clear_sheet()
    Dim scrape_table As QueryTable
    For Each scrape_table In Sheet2.QueryTables
        scrape_table.Delete
    Next scrape_table
    Sheet2.Cells.Clear
End Sub
Public Sub Scraping_Table()
    Dim url_str As String
    url_str = "https://www.exceldemy.com/excel-vba-translate-formula-language/"
    Dim scrape_table As QueryTable
    Set scrape_table = Sheet2.QueryTables.Add("URL;" & url_str, Sheet2.Range("A1"))
    With scrape_table
        .WebSelectionType = xlSpecifiedTables
        .WebTables = "1"
        .WebFormatting = xlWebFormattingAll
        .Refresh
    End With
End Sub

How Does the Code Work?

Public Sub calling_sub()
    Call clear_sheet
    Call Scraping_Table
End Sub

We created a Public Sub Procedure named calling_sub.
We used the Call Statement to call 2 Sub Procedures.
We ended the Public Sub Procedure.

Private Sub clear_sheet()
    Dim scrape_table As QueryTable
    For Each scrape_table In Sheet2.QueryTables
        scrape_table.Delete
    Next scrape_table
    Sheet2.Cells.Clear
End Sub

We created a Private Sub Procedure named clear_sheet.
We declared a variable named scrape_table as QueryTable.
We used a For Next Loop to go through the table.
We used the scrape_table.Delete method to delete objects.
We used the Sheet2.Cells.Clear method to clear objects entirely from Sheet2.
We ended the Private Sub Procedure.

Public Sub Scraping_Table()
    Dim url_str As String
    url_str = "https://www.exceldemy.com/excel-vba-translate-formula-language/"    
    Dim scrape_table As QueryTable
    Set scrape_table = Sheet2.QueryTables.Add("URL;" & url_str, Sheet2.Range("A1"))
    With scrape_table
        .WebSelectionType = xlSpecifiedTables
        .WebTables = "1"
        .WebFormatting = xlWebFormattingAll
        .Refresh
    End With
End Sub

We created a Public Sub Procedure named Scraping_Table.
We declared a variable named url_str as String.
We set the url_str as the URL of the website from where we want to scrape data.
We used the Set statement to determine the scrape_table.
We used the QueryTables.Add method to create a new query table.
We used the With Statement to execute a series of statements on a single object.
We used the WebSelectionType property to specify from where to scrape data.
We used the WebTables property to specify which table to select.
We used the WebFormatting property to specify the formatting we want. Here, we used xlWebFormattingAll to keep the formatting the same as the website.
We used the Refresh method to update the external data range.
We ended the With Statement.
We ended the Public Sub Procedure.

Save the code and go back to your worksheet.

Go to the Developer tab.
Select Macros.

The Macro dialog box will open.

Select calling_sub as the Macro name.
Click Run.

The specified table is scraped into the selected worksheet.

Things to Remember

Whenever working with VBA, you must save your Excel file as Excel Macro-Enabled Workbook.
You must be connected to the Internet to run these codes.
Your ChromeDriver and Chrome application must be the same version.

Download Practice Workbook

Scraping Web Data Using VBA.xlsm

Get FREE Advanced Excel Exercises with Solutions!

2 Comments

Dennis Ryan Mar 27, 2023 at 6:36 PM

Thank you for your post on “Excel VBA: Web Scraping with Chrome”. It was very helpful. I modified it to get the text from “a” tags and it worked. However, how do you extract the URL from the “a” tag? Thanks.
- Reply
  Alok Paul Mar 29, 2023 at 9:59 AM
  
  Hi Dennis Ryan,
  Thank you very much for reading our articles. You mentioned that you have modified the code to get texts. But you want to get URLs by Web Scraping with Chrome. You can try the following VBA code for that.
  
  VBA Macro:
  
  Sub scraping_web_urls()
  Dim chrm As New ChromeDriver
  Dim objLinks As Object
  Dim objLink As Object
  Dim iRow As Long
  chrm.Get “https://www.exceldemy.com/excel-vba-translate-formula-language/”
  chrm.Wait 4000
  Set objLinks = chrm.FindElementsByTag(“a”)
  iRow = 3
  For Each objLink In objLinks
  ActiveSheet.Cells(iRow, 2).Value = objLink.Attribute(“href”)
  iRow = iRow + 1
  Next objLink
  chrm.Quit
  End Sub
  
  Code Breakdown
  
  Dim chrm As New ChromeDriver, Dim objLinks As Object, Dim objLink As Object, Dim iRow As Long
  1st we declared different variables with type.
  
  chrm.Get “https://www.exceldemy.com/excel-vba-translate-formula-language/”
  Navigate to a specific web link.
  
  chrm.Wait 4000
  Waits for 4000 milliseconds or 40 seconds.
  
  Set objLinks = chrm.FindElementsByTag(“a”)
  Sets the value of the objLinks variable.
  
  iRow = 3
  Set the value of iRow to 3.
  
  For Each objLink In objLinks
  ActiveSheet.Cells(iRow, 2).Value = objLink.Attribute(“href”)
  
  Find each objLink from objLinks. And paste them in the second column of row 3. Here, href is used for getting hyperlinks.
  
  iRow = iRow + 1
  Increase the value of iRow to move to the next row.
  
  chrm.Quit
  This tells the ChromeDriver to quit Google Chrome.
  
  Look at the output. This will extract all the URLs that exist in the given link.
  
  More URLs are present in this sheet.