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.
Step 2 – Creating New Module
The Visual Basic window will open.
- From the Insert option, choose the new Module to write the VBA code.
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.
- 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
- 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.
Download Practice Workbook
Related Articles
When getting elements by class name, how do you know what the class name you want is? I use peoplsearchnow.com to look up the names of people who live at a certain address. I dont know what class name to use.
Hello Jack Holuta,
To identify the Class Name, you can inspect the HTML source code of the webpage you’re trying to scrape. Right-click on the element you want to extract and select “Inspect” or “Inspect Element” in your browser. This will open the Developer Tools, where you can locate the HTML code for the element and find its class name (often within a class=”…” attribute). From there, you can extract the required information using VBA.
Regards
ExcelDemy
How do you know what the Class Name you want to get is?
I use Peoplesearchnow.com to look up the names of people who live at an address. I dont know what Class Name to extract.
Hello Jack Holuta,
To identify the Class Name, you can inspect the HTML source code of the webpage you’re trying to scrape. Right-click on the element you want to extract and select “Inspect” or “Inspect Element” in your browser. This will open the Developer Tools, where you can locate the HTML code for the element and find its class name (often within a class=”…” attribute). From there, you can extract the required information using VBA.
Regards
ExcelDemy