Are you looking for a cost-efficient way to scrape relevant website data? Excel VBA is a good pick for this. With little or no coding experience, this guide would help you learn how to launch Microsoft VBA and scrape website data into an Excel spreadsheet.
In this digital era, websites play major roles in our daily lives as individuals or businesses. You would agree with me that both businesses and self-employed individuals use websites to carry out transactions daily. As such, these websites serve as good sources for generating data for making informed economic, political, health, and demographic decisions. This process of extracting valuable information from websites is known as “web scraping.”
Depending on the dataset you are looking to scrape, several methods exist for collecting data from websites. One example is Excel VBA. It would interest you to know that Microsoft Excel is undoubtedly one of the most used data processing applications across various disciplines around the world.
Whether you are a digital native or an immigrant, you have probably heard of or are familiar with the basic functions of Excel. With Microsoft Excel, you can store, organize, and manipulate data using different functions and formulas. However, one other unique use case of this application is that it allows users to access web pages to extract data from them.
Therefore, in this article, we will be looking at the different steps involved in extracting valuable data from web pages with Excel VBA. But first, let’s get to know what VBA web scraping really is.
What is VBA web scraping?
VBA web scraping is a unique data scraping technique that can be used to automatically gather data from websites and export it to Excel. It’s a web scraping technique that is made possible with the use of external applications such as the Microsoft Edge browser.
However, VBA is an abbreviation for “Visual Basic Application.” This is essentially a Microsoft Corporation programming language. It extends the capabilities of Microsoft Office tools.
As a result, users can utilize this Microsoft programming language to develop advanced functions and complex automation. Perhaps the core reason VBA can be used is to write macros to pull data from websites into Excel.
Advantages and disadvantages of using VBA for web scraping
Since we now know what VBA web scraping is, it is important to highlight some of the advantages and disadvantages of web scraping to Excel with VBA before moving on to the tutorial.
Advantages
1. Complete Automation: In Excel, you can automate almost anything you do. You don’t have to sit all day in front of your computer for long, time-consuming tasks. Excel does the work while you do other things. For instance, when running the VBA script, you don’t have to perform any additional tasks or even interact with the browser.
2. Reliable: Microsoft Excel always executes the task in the same way, so you are sure of a very accurate and consistent result every time. Also, since Microsoft Excel and VBA are developed and maintained by Microsoft, these tools can be upgraded together to the latest version without any challenge.
3. Readily Available: If you already have Microsoft Office installed, you won’t have to install anything else. This is because VBA is a bundle that comes with Microsoft Office. You can use VBA right away in all the Microsoft Office tools.
Disadvantages
1. Works only in Windows: One downside to Excel VBA is that it doesn’t have cross-platform support. It only works in a Windows environment. Also, the library's resources are limited. As such, third-party scraping tools are hard to integrate.
2. It has a steep learning curve: If you must scrape data with VBA, you must learn how to write programs in VBA. However, the VBA programming language is not particularly user-friendly. It is a bit harder than other modern programming languages, such as JavaScript, Go, or Python.
Guide to Extracting Website Data with Excel VBA
For this guide, we will be looking at how to do VBA web scraping with both Microsoft Edge and Google Chrome.
Requirements for extracting data with Excel VBA.
To extract website data using Excel VBA, it is essential to have certain things in place. To make the whole process easier, it is important that we install and set up all the prerequisites for this tutorial.
So, for this guide, we would be using:
Windows 11 OS.
Microsoft 365.
Microsoft Edge.
Google Chrome.
Selenium (for VBA scraping on Google Chrome).
A Basic understanding of HMTL and CSS.
Note: You can also follow this guide using any of the older versions of the Windows operating system and Microsoft Office Suite (make sure to install it if you don’t have MS Office on your computer). Also, we are using the Microsoft Edge browser for this Excel VBA data extraction because the preceding Internet Explorer, which was originally compatible with this data scraping, has been discontinued by Windows.
You should also note that extracting data using Excel VBA can be done with other browsers, which is what we will be doing. However, it would require some additional software. For instance, if you want to use Google Chrome to do VBA web scraping, you will have to install Selenium.
Extracting Website Data with Excel VBA (Microsoft Edge).
Preparing the Excel VBA environment
With all the requirements in place, follow the steps below to set up the development environment in Excel. This is to enable the developer ribbon that gives users access to VBA.
Open Microsoft Excel. Tap the Windows key on your keyboard to get quick access to your applications. If you've been using Microsoft Excel, it'll be listed under the pinned applications. If it's not there, go ahead and click All Apps in the top-right corner. Scroll down to locate Microsoft Excel and click it to open. When it opens, you would see an interface like the one below:
Go ahead to click the File ribbon.
Enable the developer ribbon. Obviously, Microsoft Excel wouldn’t show the developer ribbon by default. You would have to enable it manually. So, after clicking the file ribbon, scroll down to the last option in the sidebar, More…
When you click it, you will see two more options: Feedback and Options. Click on Options.
Select the Customize Ribbon option. Once you click options, a dialog box will pop up. From the side menu, select Customize Ribbon.
Click on the check box beside developer. After confirming the check box has been clicked, you can then click OK.
You should now see the developer ribbon on the menu bar, as shown in the image below.
Add a new Module and References. After activating the above VBA ribbon, we would need to insert a new Module and references. To do this, click the developer ribbon we just activated to open the Visual Basic application. Select Visual Basic to start the process.
To add a new Module, you would see a new window open, as demonstrated below. This is after you have clicked Visual Basic.
Step 1: Click on Insert on the menu, scroll down, and then click Module.
Step 2: The new Module should look like this:
To add new references, select Tools from the top menu. Pick the first option: References.
Step 3: When you click References, a new window will open like the one below. Check the boxes for Microsoft HTML Object Library and Microsoft Internet Controls from the list of available references. When you reference these two files in the Module, it will help in opening Microsoft Edge when you set it up. Once you are done checking the boxes, click OK.
Step 4: Initialize a new subroutine. This is the sub-procedure for VBA web scraping. Type in the following code in the Module as seen below:
Note: You can do this before or after selecting your references. Either way, it would work.
Code:
Sub VBA_Web_Scraping ()
End Sub
That’s all you to set up the development environment. The next step would be to automate Microsoft Edge so it can open a web page.
Automating Microsoft Edge to Open a website.
Before getting into the automation of Microsoft Edge, it is essential to note that Excel VBA initially supported only Internet Explorer-based automation. However, Microsoft discontinued Internet Explorer in July 2022. They did, however, release some updates that enabled the InternetExplorer module to run the Microsoft Edge browser in IE mode.
So, do not be surprised when we write “Internet Explorer” to interact with the Microsoft Edge browser. Besides, the codes we would be writing in the guide are also compatible with older versions of Windows that have Internet Explorer.
Opening Microsoft Edge using Excel VBA.
All right, it’s time to update our Module so that the Edge browser can open a website. For this tutorial, we would be opening https://www.iban.com/country-codes. The steps are as follows:
Step 1: Having activated the internet controls, we would insert the inputs and declare the necessary objects using the code below. You should know that if you haven't set up your reference, you won’t be able to see Internet Explorer. So, if you have not done that, you can refer to the section above to do that quickly.
Step 2: In the above code, we have defined a subroutine called VBA_Web_Scraping(). We have also defined two objects inside the subroutine: the ie and the URL. The code also shows the website address from which we want to scrape data.
Step 3: To make sure that VBA opens Microsoft Edge in IE Mode and navigates the website, add the following set of codes with reference to the website address we have already input.
Code:
Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = True
ie.navigate url
In the code we just wrote, the ie object gives us access to Microsoft Edge. Another thing you would notice is that the browser has been set to be visible so we can see what is happening (ie.Visible = True). This is not a must but it can be very helpful especially if you want to inspect the HTML code of the website you are scraping.
Opening a website in Microsoft Edge using VBA
In the previous section, we demonstrated how to open a website using VBA. When you check the last set of codes, we have used the ie.navigate() function to tell the VBA browser to open the URL we have input. You should see a pop-up like the one below in Internet Explorer. You are free to maximize the browser so that you can navigate properly.
So, the browser is ready for you to inspect the HTML code of the target website.
Scrape data from a website using VBA.
We would now proceed with the proper data scraping. To do that, follow the steps below.
Step 1: Examine the website that you wish to scrape. Nevertheless, some familiarity with HTML and web programming is necessary. However, because website structure changes so frequently, this is a good online scraping technique.
To inspect the webpage, right-click on the page element you want to extract data from. In our case, we want to extract the table data. After right-clicking, select Inspect element. On the inspection window, you should be able to see the data structure, like the class, name, and even tag of the data. Make sure to pay attention to this set of information because you will need it when you start writing the VBA code to scrape the data.
You should see the InternetExplorer dev tools tab at the bottom with the page’s codes.
Step 2: Add the Do While, Do Events, and Do Until VBA loops. These would cause a brief pause to allow the web page to load properly. It would force the Macro to wait until the target page enters Ready State Complete mode.
Code:
Do While ie.Busy: DoEvents: Loop
Do Until ie.readyState = 4: DoEvents: Loop
Step 3: To extract the table data, write the following code below.
The next line of code is targeted at scraping the table data. First, we will declare the HTML elements with a variable tbl. We would also set it to fetch the data of the specific table using the getElementById method. If you look closely at this VBA method, we are scraping via a CSS Id selector.
Scraping HTML table data or any other desired content from a web page by a CSS Id selector is much easier than scraping by a class selector. This is because the Id selector is unique to any element it is assigned to.
Code:
Dim tbl As HTMLTable
Set tbl = ie.document.getElementById("myTable")
At this point, we are ready to send that table data to our worksheet.
Next, we would set two variables that would represent our worksheet column and row.
Code:
Dim rowCounter As Integer
Dim colCounter As Integer
rowCounter = 1
colCounter = 1
Now, we would declare the table elements again, but this time it would be the tr, td, etc. Make sure to re-inspect the HTML code of the table to capture all the necessary elements.
Code:
Dim tr As HTMLTableRow
Dim td As HTMLTableCell
Dim th
We would declare our worksheet quickly to get that out of the way. Notice that we have declared our worksheet as mysh. However, the mysh would also be set as the name of our worksheet, in this case, VBA_Web_Scraping.
Note, when you start up VBA, your worksheet name would be sheet1, sheet2, etc. You can go to change the name by double-clicking or using it like that in default.
Code:
Dim mysh As Worksheet
Set mysh = ThisWorkbook.Sheets("Web Scraping")
Lastly, input the lines of code below to access the content of the table.
Code:
For Each tr In tbl.getElementsByTagName("tr")
'Loop through table header.
For Each th In tr.getElementsByTagName("th")
mysh.Cells(rowCounter, colCounter).Value = th.innerText
colCounter = colCounter + 1
Next th
'Loop through table cells.
For Each td In tr.getElementsByTagName("td")
mysh.Cells(rowCounter, colCounter).Value = td.innerText
colCounter = colCounter + 1
Next td
colCounter = 1
rowCounter = rowCounter + 1
Next tr
The complete VBA code would look like this:
Sub VBA_Web_Scraping()
Dim ie As Object
Dim url As String
url = "https://www.iban.com/country-codes"
Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = True
ie.navigate url
Do While ie.Busy: DoEvents: Loop
Do Until ie.readyState = 4: DoEvents: Loop
'Declaring the table elements.
Dim tbl As HTMLTable
Set tbl = ie.document.getElementById("myTable")
'Declaring variables for our worksheet column and roll.
Dim rowCounter As Integer
Dim colCounter As Integer
rowCounter = 1
colCounter = 1
'Dclaring the table elements again.
Dim tr As HTMLTableRow
Dim td As HTMLTableCell
Dim th
Dim mysh As Worksheet
Set mysh = ThisWorkbook.Sheets("VBA_Web_Scraping")
For Each tr In tbl.getElementsByTagName("tr")
'Loop through table header.
For Each th In tr.getElementsByTagName("th")
mysh.Cells(rowCounter, colCounter).Value = th.innerText
colCounter = colCounter + 1
Next th
'Loop through table cells.
For Each td In tr.getElementsByTagName("td")
mysh.Cells(rowCounter, colCounter).Value = td.innerText
colCounter = colCounter + 1
Next td
colCounter = 1
rowCounter = rowCounter + 1
Next tr
End Sub
Output
When you are done writing your code, press F5 or click on the green play button to run your code.
The outcome of the scraped data should look like the image below in your worksheet.
Extracting Website Data with Excel VBA (Google Chrome).
The first step to scraping website data using Excel VBA and Google Chrome is to install the Selenium library. To do that, follow the brief steps below.
Step 2: Click on the executable file to download it.
Step 3: After downloading, double-click it to start installing SeleniumBasic-2.0.9.0.exe.
Step 4: Click Next when the setup window pops up.
Step 5: Read the license agreement carefully and select I accept the agreement if you are fine with it. You can click Next to move forward.
Step 6: A marked drop-down option would appear in the next window. Click on it and select Compact installation out of the three options of Full installation, Compact installation, and Custom installation. After making your selection, click Next.
Step 7: After clicking next, the window that comes up afterward would display the path (C:\Users\jonah\AppData\Local\SeleniumBasic) of the installation folder. Please be sure to write it down or memorize it because you will need it later. Click Install to install Selenium on your computer.
Step 8: Finally, click Finish to complete the Selenium installation process.
That’s it; you are done with the installation of Selenium. However, we need to also download the ChromeDriver.
Downloading the ChromeDriver
To download the ChromeDriver, you need to know your Google Chrome browser version.
Step 1: To check your Chrome version, click on the three-dot button on the top-right corner of your Google Chrome browser.
Step 2: Scroll down the drop-down list and click Help.
Step 3: Another drop-down list would appear. Click on About Google Chrome to see your browser version.
You should now see your Chrome browser version as shown below.
Now that you know your Google Chrome version, go to the Chrome Driver website here to download it. Make sure to download the Chrome Driver according to your browser version.
You’ll be led to the download link. You'll notice that the ChromeDriver is available for both Windows and Mac. Pick the one that suits your operating system—in our case, we are going with Windows.
After downloading, look for the file in your download folder or any other location you've designated for your downloaded documents. It would be in a zip file. Right-click it and select Extract to chromedriver_win32 to unzip it. A folder would be created with that name.
Open that new folder and select the executable file you see there. Copy the file by pressing Ctrl + C.
Remember the path where the Selenium was installed? Navigate there to paste the “Chrome Driver” by pressing Ctrl + V.
Great! Your Selenium is ready for referencing in Excel VBA.
How to Refer to the Selenium Library in VBA
To refer to the Selenium Library in VBA, follow these steps:
Step 1: Open Microsoft Excel on your computer and click the developer ribbon at the top. After that, select Visual Basic.
Step 2: The VBE (Visual Basic Editor) window would open immediately. After that, click Tools, followed by References to refer to the Selenium library.
Step 3: When the References dialog box opens, select the Selenium Type Library and click OK.
Step 4: Before writing the VBA code, you should inspect the elements of the website you aim to scrape and observe how they structure the data as we did earlier with the IEMode of Microsoft Edge.
Step 5: After thoroughly inspecting the website, you can now head on over to the Excel VBA environment to write the code. To do that, click on Insert and then Module, as we did earlier.
Step 6: When the Module opens, write the following code in the editor.
Code:
Sub VBA_Web_Scraping()
Dim tdl As New WebDriver
'Declaring variables for worksheet column and roll.
Dim rowCounter As Integer
Dim colCounter As Integer
rowCounter = 1
colCounter = 1
Application.ScreenUpdating = False
tdl.Start "chrome"
tdl.Get " https://www.iban.com/country-codes "
For Each th In tdl.FindElementById("myTable").FindElementByTag("thead").FindElementsByTag("tr")
colCounter = 1
For Each t In th.FindElementsByTag("th")
Sheet2.Cells(1, colCounter).Value = t.Text
colCounter = colCounter + 1
Next t
Next th
For Each tr In tdl.FindElementById("myTable").FindElementByTag("tbody").FindElementsByTag("tr")
colCounter = 1
For Each td In tr.FindElementsByTag("td")
Sheet2.Cells(rowCounter, colCounter).Value = td.Text
colCounter = colCounter + 1
Next td
rowCounter = rowCounter + 1
Next tr
Application.Wait Now + TimeValue("00:00:20")
End Sub
The code above shows that we created a subroutine named VBA_Web_Scraping. After that, we declared the variables for our worksheet column and row.
To open Google Chrome and the webpage we want, we have used tdl.Start and tdl.Get. We have also provided the URL of the web page here. The remaining part of the code tells VBA to fetch the table elements and add them to our declared worksheet. To get the data from the table, we have used the FindElementById and FindElementByTag methods.
After inputting the code, assign the subroutine to an Excel button.
Creating a button and Assigning a Macro.
Next, we would have to insert a button and assign a Macro to it. This is a very important stage of the website data extraction process by Excel VBA, so make sure you enter your code correctly earlier before saving.
Step 1: Begin by clicking the developer tab. Under that, click Insert. A drop-down menu would appear; under the Form Controls, click the first icon that represents a button.
Step 2: Click and drag your mouse cursor to create the button.
Step 3: The Assign Macro dialog box will pop up, as shown in the image below. If you have saved that Module correctly, you will see VBA_Web_scraping on the list of Macro names. Go ahead and select it as the Macro name. Click OK to assign the Macro.
Step 4: To name the button, right-click on it and select Edit Text. You can edit it to the desired button name. We would name ours Table Data.
Step 5: If you did that correctly, your button should look like this:
It’s time to run the code we inputted earlier.
Output.
Click on the button we just created to begin. After that, go ahead and check the worksheet. You would see that the scraped data from the web page table is the same as what we had earlier.
FAQs About Extracting Website Data by Using Excel VBA
Q. Is web scraping with VBA legal?
Web scraping is generally not illegal, although some websites do not permit it. As such, VBA web scraping is not illegal either. Since it’s a data extraction technique that legally uses Microsoft products, you are surely safe. However, it's important to pay attention to the gray areas of web scraping via Excel VBA that may lead to a violation of a website’s policy.
Q. Can I use Excel VBA to Create a Single Piece of Code that Scrapes Every Website?
The short, uncomplicated answer is no. Although the methods are similar, the code for scraping various pages varies slightly, especially when connecting VBA to an external browser. The fact that each website has a unique web structure and style is one of the causes of this. The approach used by the code for scraping an e-commerce website differs from that used for crawling a social platform like Facebook.
Q. Do I need Programming Experience in Order to Use Excel VBA to Extract Website Data?
To effectively extract data using Excel VBA, it is important to have a little knowledge of coding. This would let you know what to do even if you had to follow a tutorial. This is one of the downsides of web scraping with VBA. However the learning curve may be steep, but if you are determined, you can learn VBA programming in no time.
Conclusion
Web scraping relevant data from websites is, no doubt, as important as oil in our digitally advanced society. With different techniques out there for generating this data, Excel VBA web scraping remains one of the oldest and most efficient ways to gather data.
Although it requires some basic understanding of codes, it can be done even as a beginner if you put your mind to it. We hope that this tutorial has given you some direction on web scraping with Excel VBA.
