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.
How to Do Web Scraping Without Browser with Excel VBA (With Easy Steps)
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.
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.
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.
- Therefore, select Microsoft Internet Controls and Microsoft HTML Object Library from the available list of references.
- Then, click OK.
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
- 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: Excel VBA: Web Scraping with Chrome
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.
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice it by yourself.
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. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.
