EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home VBA VBA Resources VBA Tips VBA Web Scraping
Secondary Sidebar
VBA Tips
  • VBA Tips
    • VBA RGB
    • VBA Web Scraping
    • VBA AutoFill
    • VBA GoTo
    • VBA Color Index
    • VBA Wait
    • VBA Paste
    • VBA Copy Paste
    • VBA Remove Duplicates
    • VBA Sleep
    • VBA Font Color
    • VBA PowerPoint
    • VBA Borders
    • VBA Pivot Table
    • VBA Save As
    • VBA Tutorial For Beginners
    • VBA Charts
    • VBA Dictionary
    • VBA Conditional Formatting
    • VBA Paste Values
    • VBA Pause
    • VBA Refresh Pivot Table
    • VBA Macros
    • VBA Examples
    • Programming in Excel
    • VBA SendKeys
    • VBA Save Workbook
    • VBA PasteSpecial
    • VBA Function in Excel
    • VBA Visual Basic Applications
    • VBA Return

VBA Web Scraping

By Ashwani JaiswalAshwani Jaiswal

VBA Web Scraping

Excel VBA Web Scraping

We all have heard of the scraping process but we have never come across it. Web Scraping in Excel or in any tool is used to fetch the data from any website using the Internet Explorer browser mainly. We can copy the data from any website and paste it in the cell where we want to see the data. Or we can create a process by which we get the data of any website in any form. There are two ways to get Web Scraping done. First is Early binding and the other is late binding. For this, we may need to login to the website if it requires. We either login to the website we want or we can just directly choose the source website link from where we want to copy the data. In a normal way, if we want to copy any data from any website, we first open the website, copy the data, and paste it in Excel file. But now we will use the Excel VBA Web Scraping code to fetch the data from the website we want without even opening it. If requires, then we can earlier login to the website.

All in One Financial Analyst Bundle(250+ Courses, 40+ Projects)
Financial ModelingInvestment BankingUS GAAPCFA-Level 1 & 2
Equity ResearchM & A ModelingPrivate Equity ModelingForex Trading
Price
View Courses
250+ Online Courses | 40+ Projects | 1000+ Hours | Verifiable Certificates | Lifetime Access
4.9 (86,035 ratings)

How to Use Web Scraping in VBA Excel?

We will learn how to use web scraping code in Excel by using the VBA Code.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

You can download this VBA Web Scraping Excel Template here – VBA Web Scraping Excel Template

To use VBA Web Scraping, we need to activate the tools which we would be using for Web Scraping. But before we do that, let’s see if we are able to do it when it is not activated. for this, follow the below steps:

Step 1: Insert a new module inside Visual Basic Editor (VBE). Click on Insert tab > select Module.

Insert Module

Step 2: And write the sub procedure for VBA Web Scraping as shown below.

Code:

Sub VBA_WebScraping()

End Sub

VBA Web Scraping Example 2

Step 3: Now if we declare a variable using DIM for the browser Internet Explorer, then we will not able to select any from the activated tools as shown below.

Code:

Sub VBA_WebScraping()

Dim Browser As Internet

End Sub

VBA Web Scraping Example 3

Step 4: To activate the browser tool, go to Tool menu option, and select References as shown below.

VBA Web Scraping Example 4

Step 5: Now select the Microsoft Internet Controls tool from the references list as shown below and then click on OK. This would activate the internet browser controls to the Excel.

VBA Web Scraping Example 5

This is how we can activate the required tools in the VBA Excel.

Step 6: We have seen the above, how to activate the Internet controls. Now, will write the code for web scraping. For this again, in the same module select the variable using DIM as Internet Explorer.

Code:

Sub VBA_WebScraping()

Dim Browser As InternetExplorer

End Sub

Internet Explorer Example 6

Step 7: Now Set the defined variable as New Internet Explorer. Which means, each time when we will run the code, it will open the browser again and again.

Code:

Sub VBA_WebScraping()

Dim Browser As InternetExplorer
Set Browser = New InternetExplorer

End Sub

VBA Web Scraping Example 7

Step 8: Keeping Visibility of the browser will help us to see the browser getting opened.

Code:

Sub VBA_WebScraping()

Dim Browser As InternetExplorer
Set Browser = New InternetExplorer
Browser.Visible = True

End Sub

VBA Web Scraping Example 8

Step 9: Here comes the part where we will enter the website name which we want to open. For demonstration, we are using the FACEBOOK web link as shown below. Facebook is the most commonly used website so using its link would help us to understand the data being fetched. We have not logged-in in it.

Code:

Sub VBA_WebScraping()

Dim Browser As InternetExplorer
Set Browser = New InternetExplorer
Browser.Visible = True
Browser.Navigate ("https://www.facebook.com/")

End Sub

FACEBOOK web link Example 9

Step 10: Now open a Do While loop where we will select the Browser variable to assign it as Ready state activate to Ready state complete as shown below.

Code:

Sub VBA_WebScraping()

Dim Browser As InternetExplorer
Set Browser = New InternetExplorer
Browser.Visible = True
Browser.Navigate ("https://www.facebook.com/")
Do While Browser.ReadyState <> READYSTATE_COMPLETE: Loop

End Sub

Do While loop Example 10

Step 11: At last, use message box operation to see the data being pulled from the selected web link in a single line using LOCATION NAME property.

Code:

Sub VBA_WebScraping()

Dim Browser As InternetExplorer
Set Browser = New InternetExplorer
Browser.Visible = True
Browser.Navigate ("https://www.facebook.com/")
Do While Browser.ReadyState <> READYSTATE_COMPLETE: Loop
MsgBox Browser.LocationName

End Sub

VBA Web Scraping Example 11

Step 12: At last, compile the code by pressing the F8 functional key as shortcut key and then run it if there is no error found by clicking on the Play button located below the menu bar.

Once we do that, code will open the Internet Explorer browser with the web link which we used as shown below.

VBA Web Scraping Example 12

Step 13: And just after that, we will get the message box where it will just fetch the mainline web data shown below.

VBA Web Scraping Example 13

The reason being we got question marks after the work Facebook is because of blank fields of a User ID and Password section. We can try and test different websites as well from where we want to scrap the data.

Now in the same message box, if try to get the website name from there we are fetching the data, then for that, we need to use Location URL operation in the same line of code as shown below.

Now if we again run this code, then we would see in the same message we now have the website data in one line and URL link in other line.

Pros of VBA Web Scraping

  • The above shown method of Web, Scrapping is the easiest way to do.
  • VBA Web scrapping is quite helpful in fetching the data in segregated Excel cells which is quite easy to copy and then process further.

Things to Remember

  • Setting up New Internet Explorer as shown in the second line of code, will allow us to see the used website link getting opened multiple times whenever we run the code. And the data will be fetched from the same link but with a new page which will get opened again.
  • VBA Scraping uses only Internet Explorer as a browser because it is owned by Microsoft.
  • There are the different processes of Web Scraping through which we can directly install the tool and add-ins into the browser which would help in fetching up the data from any URL.
  • Once the code work is done, remember to save the file in Macro enable format to avoid code getting lose. By this we can use the written VBA Code multiple times in the future.

Recommended Articles

This is a guide to the VBA Web Scraping. Here we discuss how to use web Scraping code to fetch the data from the website in excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. How to Use VBA Login?
  2. VBA Month | Examples With Excel Template
  3. How to Use Create Object Function in VBA Excel?
  4. How to Use VBA IsError Function?
Popular Course in this category
All in One Excel VBA Bundle (120+ Courses)
  120+ Online Courses |  500+ Hours |  Verifiable Certificates |  Lifetime Access
4.9
Price

View Course
1 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more