How to Do Web Scraping Without Browser with Excel VBA

VBA web scraping without using the browser is a unique scraping method that enables automatic data collection from websites and export to Excel.

In this article, we will demonstrate how to do web scraping without a browser in Excel by running a VBA macro. When we run the code, it will launch Microsoft Edge, open quotes.toscrape.com, find and scrape the top 5 quotations from the list, and save them to the first sheet in the opened Excel file.

In the image below are two quotations from the website that we’ll be scraping..


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

The built-in Developer tab is the place to find the tools required to use Visual Basic for Applications (VBA) to write and execute a Macro. As a convention, the tab is disabled. To make it visible on the toolbar in the main ribbon, it has to be activated in the Options section of the File tab.

  • Open the Developer tab.
  • 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

The Visual Basic window will open.

  • From the Insert option, choose the new Module to write the VBA code.

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


Step 3 – Adding New References from Tools Option

A new module will open as in the below image.

Now we select the references that we need in order to write our code.

  • Click Tools.
  • Click on References.

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

  • Select Microsoft Internet Controls and Microsoft HTML Object Library from the list of Available References.
  • Click OK.


Step 4 – Writing VBA Code for Scraping Web Data

We are ready to enter the code for our Macro.

  • Press Alt + F11 to launch the VBA editor.
  • Paste the following VBA code into the Module:
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
  • We define a subroutine name as Sub Web_Scraping().
  • 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
  • We then open the web browser with navigate (“https://quotes.toscrape.com“).
  • We add some pauses to allow loading the website properly with a loop named Do While Web_browser.Busy: Loop.
  • When the website is loaded, we extract the quotes and authors from the HTML document by using Set Web_page = Web_browser.document.
Set qts = Web_page.getElementsByClassName("quote")
Set Author_Name = Web_page.getElementsByClassName("author")
  • Excel rows are now filled with the extracted data by means of a For loop that executes the Cells function and provides the row and column positions:
For Xnum = 1 To 5
Cells(Xnum, 1).Value = quotes.Item(Xnum).innerText
Cells(Xnum, 2).Value = Author_Name.Item(Xnum).innerText
Next Xnum
  • At the end of the For loop, we close the function by calling the Quit function.
  • Finally, we end the VBA macro by calling End Sub.
  • Save the code.
  • Press F5 to run the macro.

Read More: Excel VBA: Web Scraping with Chrome


Step 5 – Showing Results After Scraping Data from Web

After pressing Run, the macro will launch Microsoft Edge browser, open quotes.toscrape.com, and explore there while it loads before taking the top 5 quotations from the list and saving them to the first page of the open Excel file.

The final output in the spreadsheet will be scraped data of some quotes from different authors.

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


Download Practice Workbook


Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Bishawajit Chakraborty
Bishawajit Chakraborty

Bishawajit Chakraborty, a Rajshahi University of Engineering & Technology graduate with a B.Sc. in Mechanical Engineering, has been associated with ExcelDemy since 2022. Presently, he is a content developer, specializing in Excel Power Query, Data Analysis and VBA. It is worth mentioning that he has authored more than 90 articles on VBA content development. His profound interest lies in the fields of data analytics and data science. He possesses expertise in VBA, Power BI, machine learning, and Python... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo