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.

How to Use Excel VBA for Web Scraping with Chrome (With Easy Steps)

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.

Step-01: Install Selenium

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

Firstly, go to the GitHub website.

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

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

Next, a Setup window will appear.
Select Next.

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.

Step-02: Download Chrome Driver

Here, I will download the Chrome Driver.

To begin with, go to the ChromeDriver site to download it.

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)\.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

🔎 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.

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.

Download Practice Workbook

You can download the practice workbook from here.

Scraping Web Data Using VBA.xlsm

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. If you have any questions, feel free to let me know in the comment section below.

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.