Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

If you are looking for a way to scrape data from a website, this article will be helpful for you. The focus of this article is to explain how you can use Excel VBA for web scraping with Chrome.


Download Practice Workbook

You can download the practice workbook from here.


Step-by-Step Procedures to Use Excel VBA for Web Scraping with Chrome

Here, I have taken an article from ExcelDemy as the dataset. I will scrape the following table from the website. Here, I will use Excel VBA for web scraping with Chrome.

Dataset fot Using Excel VBA for Web Scraping with Chrome


Step-01: Install Selenium

In this first step, I will install the Selenium library.

  • Secondly, click on the marked portion in the following image to download SeleniumBasic.

Install Selenium for Web Scraping with Chrome using VBA in Excel

  • After that, double-click on the downloaded file to install SeleniumBasic.

  • Next, a Setup window will appear.
  • Select Next.

Setup Window for Selenium Basic for Scraping Web with Chrome in Excel VBA

  • Afterward, select I accept the agreement.
  • Then, select Next.

  • Then, another window will appear.
  • Click on the marked drop-down option.
  • Next, select Compact installation.

  • Afterward, select Next.

  • Now, you will be able to see the path of the Install folder. Remember the path. You will need it later.
  • Next, select Install.

  • Then, Selenium will be installed.
  • Finally, select Finish.

Read More: How to Import Table from Website to Excel (2 Easy Ways)


Step-02: Download Chrome Driver

Here, I will download the Chrome Driver.

Download Chrome Driver gor Web Scraping With Chrome in Excel VBA

  • Before downloading, you will need to know the Chrome version you are using.
  • To check that, click on the Three Dot button.
  • Then, select Help.
  • After that, select About Google Chrome.

  • Now, you will be able to see the version.

  • After that, download the ChromeDriver according to your Chrome version. Here, I downloaded the version I need.

  • This will lead you to the download link.
  • Then, download the driver you need. Here, I downloaded the one for Windows operating system as I am using Windows.

  • Next, select the downloaded zip file.
  • Then, right-click on the selected file.
  • After that, select Extract to chromedriver_win32(1)\.

Extracting Chrome Driver for Web Screaping with Chrome in Excel VBA

  • Now, a folder will be created.
  • Open that folder.

  • Then, select the file.
  • Afterward, copy the file by pressing Ctrl + C on your keyboard.

  • Then, go to the following folder. Here, I selected the path of my installed folder, you will have to select yours.
C:\Users\HP\AppData\Local\SeleniumBasic
  • Next, press Ctrl + V to paste the ChromeDriver.

Read More: How to Use Web Scraping with Excel VBA (3 Suitable Examples)


Step-03: Refer to Selenium Library in VBA

Now, you will have to refer to the Selenium Library in VBA.

  • In the beginning, go to the Developer tab.
  • Then, select Visual Basic.

Refer to Selenium Library in VBA for Web Scraping with Chrome

  • Consequently, the Visual Basic editor window will open.
  • Then, select Tools.
  • Next, select References.

  • Now, the References dialog box will appear.
  • Select Selenium Type Library.
  • In the end, select OK.


Step-04: Inspect Data Structure from Web

Before writing the code you must inspect the elements of the web and observe how the data are structured.

  • To do that, right-click on the table from where you want to scrape data.
  • Then, select Inspect.

Inspect Data Structure from Web for Scraping Web with Chrome

  • After that, you will be able to see how the data is structured and you will get to know about the class, name, and tag of the data. You will need these pieces of information later while writing the VBA code.

Read More: How to Scrape Data from a Website into Excel (2 Easy Methods)


Step-05: Write VBA Code

In this step, I will write the VBA code for scraping web with Chrome in Excel.

  • Firstly, select the Insert tab.
  • Secondly, select Module.

Write VBA Code for Scraping Web with Chrome in Excel VBA

  • Thirdly, a Module will open.
  • Then, write the following code in that 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?

  • Here, I created a Sub Procedure named scraping_web.
  • Then, I declared a variable named chrm as Selenium.ChromeDriver.
  • Next, I declared two more variables as Integer.
  • Afterward, I used the Application.ScreenUpdating property to speed up the macro.
  • Then, I set chrm as New Selenium.ChromeDriver.
  • Next, I used chrm.Start to start the Chrome browser.
  • Further, I used chrm.Get to open the web page I want. Here, I provided the URL of the page I want. You will have to use it according to your needs.
  • Then, I used For Next Loop to get the data from the table.
  • Next, I used FindElementByClass and FindElementsByTag methods to get the table elements.
  • After that, I used the Application.Wait method to pause running the macro for a specified amount of time.
  • Finally, I ended the Sub Procedure.
  • Next, Save the code and go back to your worksheet.

Saving VBA Code for Web Scraping with Chrome in Excel VBA

Read More: Excel VBA to Scrape Table from Website (With Easy Steps)


Step-06: Insert Button and Assign Macro

Here, I will show you how you can insert a Button and assign a Macro to it.

  • In the beginning, go to the Developer tab.
  • Next, select Insert.
  • After that, select Button from Form Controls.

Insert Button and Assign Macro fot Web Scraping with Chrome in Excel VBA

  • Then, click and drag your mouse cursor where you want the button.

  • Next, the Assign Macro dialog box will appear.
  • Then, select scraping_web as the Macro name.
  • Afterward, select OK to assign the Macro to the button.

Assigning Macro to a Button for Web Scraping with Chrome in Excel VBA

  • Then, right-click on the button.
  • Next, select Edit Text.

  • Afterward, edit the text as you want. I wrote Scrape Data.

  • Finally, this is how my button looks.


Step-07: Run VBA Code

Now, I will run the VBA code.

  • To begin with, click on the Button.

Run VBA Code for Web Scraping with Chrome in Excel VBA

  • After that, check Sheet1 and you will see that you have scraped the data from the table to your selected worksheet.

  • Finally, I formatted the table according to my preference.


Final Output

In the following image, you can see the final output.

Final Output of Web Scraping with Chrome in Excel VBA


Alternative Way to Scrape Table Data from Web in Excel

In this section, I will explain an alternative way of scraping the web using Excel VBA without the Chrome driver. Let’s see the steps.

Steps:

  • Firstly, open the Visual Basic Editor window by following Step-03.
  • Secondly, open a Module by following Step-05.
  • Thirdly, write 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

Alternative Way to Scrape Table Data from Web

🔎 How Does the Code Work?

Public Sub calling_sub()
    Call clear_sheet
    Call Scraping_Table
End Sub
  • Here, I created a Public Sub Procedure named calling_sub.
  • Then, I used the Call Statement to call 2 Sub Procedures.
  • Next, I 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
  • Here, I created a Private Sub Procedure named clear_sheet.
  • Then, I declared a variable named scrape_table as QueryTable.
  • Next, I used a For Next Loop to go through the table.
  • Afterward, I used the scrape_table.Delete method to delete objects.
  • Then, I used the Sheet2.Cells.Clear method to clear objects entirely from Sheet2.
  • Lastly, I 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
  • In this part, I created a Public Sub Procedure named Scraping_Table.
  • Then, I declared a variable named url_str as String.
  • Next, I set the url_str as the URL of the website from where I want to scrape data.
  • Afterward, I used the Set statement to determine the scrape_table.
  • Then, I used the QueryTables.Add method to create a new query table.
  • Further, I used the With Statement to execute a series of statements on a single object.
  • Next, I used the WebSelectionType property to specify from where to scrape data.
  • Then, used the WebTables property to specify which table to select.
  • After that, I used the WebFormatting property to specify the formatting I want. Here, I used xlWebFormattingAll as I want to keep the formatting the same as the website.
  • Then, I used the Refresh method to update the external data range.
  • Next, I ended the With Statement.
  • Finally, I ended the Public Sub Procedure.
  • Next, Save the code and go back to your worksheet.

  • Afterward, go to the Developer tab.
  • Then, select Macros.

  • Here, the Macro dialog box will open.
  • Next, select calling_sub as the Macro name.
  • Afterward, select Run.

  • Finally, you will get the table you wanted in your selected worksheet.

Read More: How to Do Web Scraping Without Browser with Excel VBA


Things to Remember

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

Conclusion

So, you have reached the end of my article. Here, I tried to explain how to use Excel VBA for web scraping with Chrome. I hope this article was helpful to you. For more articles stay connected with ExcelDemy. If you have any questions, feel free to let me know in the comment section below.


Related Articles

Mashhura

Mashhura

Hey! Welcome to my profile. Right now, I am doing research on Microsoft Excel. I will be posting articles related to this here. My last educational degree was B.Sc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology. I like to explore new things and find the best and most innovative solutions in every situation.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo