Mashhura Jahan
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.
Table of Contents
hide
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.
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.
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.
- 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.
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.
- 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.
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.
- 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.
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.
- 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.
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.
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.
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.