Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Do Web Scraping Without Browser with Excel VBA

Definitely, one of the most popular software programs utilized globally and in many fields is Microsoft Excel. Users can access web pages and extract data from them. It also stores, organizes and manipulates data using a variety of functions and formulas. This article will show you how to do web scraping without a browser using VBA.


What Is VBA Web Scraping?

VBA web scraping without using the browser is a unique scraping method that enables automatic data collection from websites and export to Excel. With the help of such external tools as the Microsoft Edge browser, scraping itself is made possible.


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice it by yourself.


Step-by-Step Procedures to Do Web Scraping Without Browser with Excel VBA

In this article, we will demonstrate how to do web scraping in Excel by employing VBA code without a browser with some easy steps. So, to know how to do web scraping without browsing, you can follow the below steps accordingly. When we launch the program, it will launch Microsoft Edge, open quotes.toscrape.com, and explore there before taking the top 5 quotations from the list and saving them to the first page in the opened Excel file. In the image below, we have shown the two quotations from the website. After that, we will launch all the quotations from the following page of the website into our Excel file.


Step 1: Using Developer Tab to Open Visual Basic Applications Window

The Excel built-in Developer tab offers the tools required to use Visual Basic for Applications (VBA) to execute a Macro. As a convention, the tab is disabled. To make it visible on the toolbar at the top of the Excel window, it has first to be activated in the Options section of the Menu bar.

  • Firstly, we will open the Developer tab.
  • Then, we will select the Visual Basic command.

Using Developer Tab to Open Visual Basic Applications Window to Do Web Scraping Without Browser with Excel VBA


Step 2: Creating New Module

  • Here, the Visual Basic window will open.
  • After that, from the Insert option, we will choose the new Module to write the VBA code.

Creating New Module to Do Web Scraping Without Browser with Excel VBA

Read More: How to Use Web Scraping with Excel VBA (3 Suitable Examples)


Step 3: Adding New References from Tools Option

Here, we will choose the desired references from the available list of references in the Tools option because we want to do web scraping without a browser with Excel VBA.

  • Here, a new module will open in the below image.
  • Firstly, go to the Tools option.
  • Secondly, click on References.

Adding New References from Tools Option to Do Web Scraping Without Browser with Excel VBA

  • Therefore, select Microsoft Internet Controls and Microsoft HTML Object Library from the available list of references.
  • Then, click OK.

Read More: Excel VBA: Web Scraping with Chrome (With Easy Steps)


Step 4: Writing VBA Code for Scraping Web Data

VBA is a programming language that may be used for a variety of tasks, and different types of users can use it for those tasks. Using the Alt + F11 keyboard shortcut, you can launch the VBA editor. In the last section, we will generate VBA code that makes it very easy to do web scraping without a browser in Excel.

  • So, paste the following VBA code into the Module.
  • Then, save the following code.
  • After that, press F5 to run the VBA macro.
Sub Web_Scraping()
'Declaring variables
Dim Web_browser As InternetExplorer
Dim Web_page As HTMLDocument
Dim qts As Object
Dim Author_Name As Object
'Set the browser
Set Web_browser = New InternetExplorer
'Showing the browser
Web_browser.Visible = True
Web_browser.navigate ("https://quotes.toscrape.com")
'Using Do While Loop
Do While Web_browser.Busy: Loop
'Set the web browser document
Set Web_page = Web_browser.document
Set qts = Web_page.getElementsByClassName("quote")
Set Author_Name = Web_page.getElementsByClassName("author")
'Using For loop
For Xnum = 1 To 5
Cells(Xnum, 1).Value = qts.Item(Xnum).innerText
Cells(Xnum, 2).Value = Author_Name.Item(Xnum).innerText
Next Xnum
Web_browser.Quit
End Sub

 

Writing VBA Code for Scraping Web Data without Browser

 

VBA Breakdown
  • Firstly, we will define a subroutine name as Sub Web_Scraping().
  • Secondly, we declare our variables by adding references as
Dim Web_browser As InternetExplorer  
Dim Web_page As HTMLDocument 
Dim qts As Object  
Dim Author_Name As Object
  • Thirdly, we will open the web browser as navigate (“https://quotes.toscrape.com“).
  • After navigating to the website, we will add some pauses to load the website properly by using a loop named Do While Web_browser.Busy: Loop.
  • Then, we will extract the quotes and authors from the HTML document by using this as Set Web_page = Web_browser.document
Set qts = Web_page.getElementsByClassName("quote")
Set Author_Name = Web_page.getElementsByClassName("author")
  • Next, Excel rows can be filled with the extracted data by using a for loop by executing the Cells function and providing the row and column positions as
For Xnum = 1 To 5
Cells(Xnum, 1).Value = quotes.Item(Xnum).innerText
Cells(Xnum, 2).Value = Author_Name.Item(Xnum).innerText
Next Xnum
  • Therefore, we will close the function by calling the function named Quit.
  • Finally, end the VBA macro by using End Sub.

Read More: Automated Data Scraping from Websites into Excel


Step 5: Showing Results After Scraping Data from Web

Here we will launch Microsoft Edge, open quotes.toscrape.com, and explore there before taking the top 5 quotations from the list and saving them to the first page of the open Excel file.

  • Finally, you will get the following output where you can see some scrapped data of some quotes from different authors.

Showing Results After Scraping Data from Web without browser with Excel VBA

Read More: How to Extract Data from Multiple Web Pages into Excel


Conclusion

In this article, we’ve covered step-by-step procedures to do web scraping without a browser in Excel. We sincerely hope you enjoyed and learned a lot from this article. Additionally, if you want to read more articles on Excel, you may visit our website, Exceldemy. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.


Bishawajit Chakraborty

Bishawajit Chakraborty

I'm Bishawajit Chakraborty. Hello. I graduated from Rajshahi University of Engineering & Technology (RUET) with a degree in Mechanical Engineering. I'm working with ExcelDemy as a Content Developer for Excel & VBA. You can visit our website, Exceldemy if you'd like to read my published articles on MS Excel and VBA.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo