How Can VBA Be Used to Scrape Website Data?

Used by everyone from students to scientists, Microsoft Office is one of the most popular platforms around. Supported by one of the tech sector’s giants, its tools let you write documents, visualize data, and more — and few tools are more powerful than Excel.

Excel is Microsoft’s renowned spreadsheet program, and it’s filled with functionalities to help users process their data. One key feature is its ability to receive data gathered through the Visual Basic Application (VBA) toolkit, letting you scrape the web with Excel.

In this comprehensive guide, we’ll show you what VBA web scraping is and how you can put it to use. We’ll answer basic questions such as “What is VBA programming?” and “What is VBA programming used for?” before taking a deeper dive by giving you a sample of the VBA code. We’ll also give you the upsides and downsides of using VBA web scraping so that you can decide if it’s the best method for your application.

What Is VBA Web Scraping?

Tech companies sometimes use their own programming languages to develop their products, and Microsoft’s is called Visual Basic. It was designed to create software compatible with the .NET framework and to be as user-friendly as possible. The applications it executes (like the ones used to collect web scraping data) run off of Visual Basic Applications, or VBA. VBA web scraping simply uses Microsoft’s signature language to gather data you’ve scraped online and transfer it to Excel.

That’s simple enough, but you need to have a few pieces in place before you use VBA to jump into your web scraping project. Most importantly, because it’s developed by Microsoft, you’ll need to use VBA on a Microsoft Edge browser. You’ll also need to have Excel already downloaded to your computer, so MS Office is a must.

What Is VBA Programming Used For?

If you’re going to do Excel VBA web scraping, you’ll need to do some programming to carry it out. You’ll use VBA code for web scraping to specify which parts of a webpage you want to scrape, how it will be formatted into Excel, whether you want to scrape a single page or multiple ones, and many other parameters too.

Once you master writing VBA code for web scraping, you’ll be able to use Excel to gather and analyze data for all sorts of applications, including:

• Real estate data (property valuation, risk assessment, real estate marketing, etc.)
• Analyzing financial data (market trend analysis, balance sheets, income and wages, etc.)
• Buying or copping sneaker grails
• Academic and scientific research
• Review monitoring
• Social media engagement

When you use VBA programming to transfer data from the web to Excel, you should consider using proxies to help access the pages you’re looking to scrape. Proxies should be reliable and obtained in compliance with all related laws. Otherwise, your proxies could be banned from accessing the sites you need to access.

To protect the digital community at large, all proxy providers should not only comply with data protection requirements such as the General Data Protection Regulation (GDPR) and California Consumer Privacy Act (CCPA) but should also make sure their proxies are gathered with user consent and used only for ethical purposes.

VBA Web Scraping: Advantages and Disadvantages

Before you learn how to perform Excel VBA web scraping, you should know about its advantages and disadvantages. That way, you’ll know if VBA is the right tool for your web scraping project or if a different language like Python or Java would work better.

Fundamentally, VBA web scraping is highly reliable and well-supported given that it’s backed by Microsoft. However, it has limited compatibility with other tools and platforms and has a steeper learning curve than other languages. Here’s a closer look at VBA’s pros and cons.

Pros

• Accessibility: VBA is a toolkit within Excel, so anyone who has this classic spreadsheet software has access to VBA. That means you won’t have to install anything else, so if you have Excel, you can get started with VBA web scraping right now.
• Reliability: Because it’s part of the software, VBA can be updated any time Microsoft comes out with a newer version of Excel. There’s no need to upgrade your development environment other than periodically migrating to the newest Excel version, making it simpler to keep your ecosystem up to date.
• Support: VBA web scraping uses Microsoft Edge as its web browser, and since this industry leader keeps their browser current, scraping is more convenient.
• Automation: VBA script is easy to automate. There’s no need to take additional actions while the code runs, as the code can execute commands such as log-ins, button clicking, and scrolling on its own.

With no need to install additional software or update your environment, VBA’s out-of-the-box availability makes it highly user-friendly. The fact that it’s supported by Microsoft also makes it a reliable choice for web scraping — but it still has a few drawbacks.

Cons

• Limited compatibility: Because they are built using a Microsoft language, VBA scrapers work best on platforms that Microsoft supports. That means it’s best to perform VBA web scraping with Microsoft Edge. While it’s possible to do VBA web scraping on a Mac, it’s significantly more difficult, and fewer library supports are available.
• Office-dependent: As a Microsoft tool, VBA scrapers work best with MS Office platforms such as Word or Excel. It’s difficult to make VBA play well with third-party scraping tools, so if you want to integrate another scraping tool into the mix, it might be better to use a different language.
• Harder to learn: Despite its accessibility, VBA isn’t the easiest language for beginners to learn. Those with prior coding knowledge may have an easier time, but if you need a simpler option, other languages could work better.

The main limitation behind VBA web scraping lies in its inability to work well with other systems, but solutions exist to help you clear that hurdle as well. Excel Web Query can help you scrape on other operating systems such as Linux or Mac. That setback aside, seasoned programmers will find VBA web scraping a good option if they’re already using Windows and want to stick with Microsoft solutions.

VBA Code for Web Scraping: Tutorial

Now that you can determine if VBA is the right choice for your scraping project, you can learn the ins and outs of the code. The exact script may vary for your application depending on which sites you scrape and what parameters you prefer, but this step-by-step tutorial will give you an overview of the process.

Step 1: The prerequisites

Before you can do VBA web scraping, you’ll need to have a few elements in place. The first is Microsoft Excel, and the second is a Microsoft Edge browser. You probably already have them, but set those up if you haven’t already.

Step 2: Enable the developer menu

Once you’ve opened Excel, open the “Developer” tab to write the code for your project. Later versions automatically have the “Developer” tab, but earlier ones may not. If you don’t see it across the top, go to “File,” select “Options,” then click on “Customize” and check the box on “Developer” to populate the tab.

Step 3: Open Visual Basic

You’ll write your code within a new module, but to access that, you’ll first need to open Visual Basic. It’s on the far left of the “Developer” tab, so select it to write VBA web scraping code.

Step 4: Create a new module

You might be asking, What is a VBA module?  It’s the window in which you write all your code, so you’ll need to create a new one when you begin.

To create a new module, select “Insert” within Visual Basic. Next, select “Module,” and a window will populate for your work.

In some cases, it’s better to tailor your scraping method to a certain type of object, called a class. If you need to customize your scraping according to its object type, you can create a class module under “Insert” instead. What is ‘class module’ in VBA? It’s a module designed for a subset of objects. If you’re still learning how to web scrape using VBA, it’s probably best to start off with a standard module first.

Step 5: Add your references

References make another application’s objects available to your VBA, so you’ll need them to scrape the web.

To choose which references you want to connect with, select the “Tools” tab, then click “References.” A drop-down box will populate from there, so scroll through it and select both “Microsoft HTML Client Library” and “Microsoft Internet Control.” They may already be checked depending on the version you use, but it’s important to make sure beforehand.

Step 6: Automate Microsoft Edge for scraping

Now that you’ve set up your environment, it’s time to use the module (or class module) editor to write your VBA web scraping code. Here’s what the code snippet would look like:

Sub scrape_quotes()

Dim browser As InternetExplorer

Dim page As HTMLDocument

Set browser = New InternetExplorer

browser.Visible = True

browser.navigate (“https://quotes.toscrape.com”)

End Sub

Here, scrape_quotes denotes the URL that you’re scraping, with “Sub” denoting the subroutine, “Dim” denoting the variables, “and browser.Visible = True” enabling you to view the browser.

Also note that while Internet Explorer has been replaced by Microsoft Edge, thanks to some Microsoft updates, the script can still run in IEMode without any bugs.

Step 7: Scrape the data and save it to Excel

Once you automate your browser with the code, you can then scrape your data.

First, define the objects you want to scrape.  A sample from the above site might be quotes and authors, which would look like:

Dim quotes As Object

Dim authors As Object

Next, specify the data you’ll be extracting.

Set page = browser.document

Set quotes = page.getElementsByClassName(“quote”)

Set authors = page.getElementsByClassName(“author”)

If you want to help the website load, add a command to pause beforehand:

Do While browser.Busy: Loop

To transfer the extracted data to your Excel sheet, use a “For” loop and specify the rows and columns that the data should span:

For num = 1 To 5

Cells(num, 1).Value = quotes.Item(num).innerText

Cells(num, 2).Value = authors.Item(num).innerText

Next num

Finally, close the browser.

browser Quit

Put it all together, and the total source code for this example is:

Sub scrape_quotes()

Dim browser As InternetExplorer

Dim page As HTMLDocument

Dim quotes As Object

Dim authors As Object

Set browser = New InternetExplorer

browser.Visible = True

browser.navigate (“https://quotes.toscrape.com”)

Do While browser.Busy: Loop

Set page = browser.document

Set quotes = page.getElementsByClassName(“quote”)

Set authors = page.getElementsByClassName(“author”)

For num = 1 To 5

Cells(num, 1).Value = quotes.Item(num).innerText

Cells(num, 2).Value = authors.Item(num).innerText

Next num

browser.Quit

End Sub

Final Thoughts

Excel is already one of the most powerful and popular data analytics tools you’ll find, so it makes sense to use it for web scraping when you can. VBA code for web scraping makes that possible, and while its Windows-only compatibility and somewhat steep learning curve may make the process more complex at first, the high degree of reliability, availability, and support may make it the best option for your scraping project.

Whether you choose VBA or some other tool to scour the web for your data, you’ll still need a collection of high-quality, ethically sourced proxies to help you pull it off. Reliable proxies are essential for crawling web pages so that you can access the data you need without getting blocked.

At Rayobyte, we provide residential and data center proxies worthy of Fortune 500 companies for startups and enterprises alike. They’re ethically sourced to prevent you from getting blocked, and our team of experts is here to help make your project a success. Contact us today, and put our proxies to use with your VBA web scraping project!

The information contained within this article, including information posted by official staff, guest-submitted material, message board postings, or other third-party material is presented solely for the purposes of education and furtherance of the knowledge of the reader. All trademarks used in this publication are hereby acknowledged as the property of their respective owners.