Guide to Extracting Website Data by Using Excel VBA

Pros and cons of using VBA for scraping

Before we move on to the tutorial part it is essential to highlight some advantages and disadvantages of web scraping to Excel with VBA.

Pros

• Ready to use – VBA is bundled with Microsoft Office which basically means that if you already have MS Office installed, you don’t have to worry about installing anything else. You can use VBA right away in all the Microsoft Office tools.

• Reliable – Both Microsoft Excel & VBA are developed and maintained by Microsoft. Unlike other development environments, these tools can be upgraded together to the latest version without much hassle.

• Out-of-the-box support for browser – VBA web scrapers can take advantage of Microsoft’s latest browser Microsoft Edge which makes scraping dynamic websites pretty convenient.  

• Complete automation – When running the VBA script, you don’t have to perform any additional tasks or interact with the browser. Everything will be taken care of by the VBA script including log-in, scrolling, button clicks, etc.

Cons

• Only works in Windows – VBA scrapers are not cross-platform. They only work in a Windows environment. While MS Office does have support for Mac, it is way harder to write a working VBA scraper on it. The library supports are also limited, for example, you will be unable to use Microsoft Edge.

• Tightly coupled with MS Office – VBA scrapers are highly dependent on MS Office tools. Third-party useful scraping tools are hard to integrate with it.

• Steep learning curve - VBA programming language is less beginner-friendly and a bit harder than other modern programming languages, such as Python or Javascript.

Overall, if you are looking to develop a web scraper for the Windows operating system that automatically pulls data from a website, then VBA-based web scraping will be a good choice.

Tutorial

Before we begin, let us make sure we’ve installed all the prerequisites and set up our environment properly so that it will be easier to follow along.

Prerequisites

In this tutorial, we’ll be using Windows 10 and Microsoft Office 10. However, the steps will be the same or similar for other versions of Windows. You’ll only need a computer with Windows Operating System. In addition, it’s necessary to install Microsoft Office if you don’t have it already. Detailed installation instructions can be found in the Microsoft’s Official documentation.

Preparing the environment

Now, that you’ve installed MS Office, complete the steps below to set up the development environment:

Step 1 - Open Microsoft Excel

From the start menu or Cortana search, find Microsoft Excel and open the application. You will see a similar interface as below: 

Click on File 

Step 2 - Go to Options to enable developer menu

By default, Excel doesn’t show the developer button in the top ribbon. To enable this, we’ll have to go to “Options” from the File menu.

Step 3 -  Select Customize Ribbon

Once you click the “Options,” a dialog will pop up where, from the side menu, you’ll need to select “Customize Ribbon”. Click on the check box next to “developer.” Make sure it is ticked and then click on “OK.”

Step 4 - Open Visual Basic application dialog

Now, you’ll see a new developer button on the top ribbon, clicking on it will expand the developer menu. From the menu, select “Visual Basic.”

Step 5 - Insert a new Module

Once you click on “Visual Basic", it will open a new window as demonstrated below:

Step 6 - Add new references

From the top menu select Tools >  References… which will open a new window like the one below. Make sure to scroll through the available list of references and find Microsoft HTML Client Library and Microsoft Internet Control. Click on the check box next to both of them to enable these references. Once done, click OK.

Source Code

Below is an example of a full source code:

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

Conclusion

Excel web scraping with VBA is a great choice for Windows automation and web extraction. It’s reliable and ready to use which means you won’t have to worry about any unexpected issues or additional steps. For your convenience, you can also access this tutorial in our GitHub repository. 

The biggest disadvantage of VBA web scraping that was highlighted in the article is the lack of cross-platform support. However, if you want to develop web scrapers that can be used on multiple operating systems such as Linux or Mac, Excel Web Query can also be an option. We also recommend exploring web scraping with Python – one of the most popular programming languages that is capable of developing complex network applications while maintaining its simplified syntax. Of course, if you're searching for more advanced web scraping solutions, check our Web Scraper API, designed to collect real-time public data at scale from most websites. And if you already tried web scraping but faced IP blocks, our Residential Proxies can help you overcome this issue.