Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction 
2.
How to Scrap Website Data using VBA?
3.
Frequently Asked Questions
4.
Conclusion
Last Updated: Mar 27, 2024
Easy

Web Scraping with VBA

Master Python: Predicting weather forecasts
Speaker
Ashwin Goyal
Product Manager @

Introduction 

Web Scraping is a technique of accessing or extracting data from the web pages and downloading it from that particular website to our computer files/system. It is possible by accessing external applications like Internet Explorer. Two ways to do it, i.e., Late Binding & Early Binding.

Web Scraping with VBA means that when we use Virtual Basic for Applications(VBA) to fetch the data from the sources on the web, we may need logins for the data sources. Still, first, to do so, we need to enable the references from the tools section in the VBA editor(As the object is external) for the Microsoft HTML library to access the web from VBA.

Not many of us know that we can access the web pages and get the data from those web pages from excel. We can scrape through web pages, access browsing applications, etc. This article will show you how to write an excel VBA code for web scraping in detail.

This article will give you insights into how to access websites from excel with the code and do many other kinds of stuff.

How to Scrap Website Data using VBA?

We can access any other applications from excel using two ways, i.e., "Early Binding" & "Late Binding." Using the "Early Binding" technique at the beginner's stage is always safe.

To access the website, we need external applications, for example, "Internet Explorer." Since we know it is an external object, we need to set the reference first.

Follow the below steps to web scrap.

Step 1: Define the VBA variable and assign the data type as "Internet Explorer."

The code is as follows:

Sub Web_Scraping()

  Dim Internet_Explorer As internet

End Sub

VBA Web Scraping Example 1.0

As you can see above, we don't get to see "Internet Explorer" because "Internet Explorer" is an external object/application, so we need to set the reference for this.

Step 2: To set the reference for the object, go to "Tools" & choose "References."

VBA Web Scraping Example 1.1.0

In the below window, scroll down and choose “Microsoft Internet Controls.”

Example 1.2

Step 3:  Check the "Microsoft Internet Controls" box and click on the Ok button. Now we should see this object name in the IntelliSense list.

The code for this step is:

Sub Web_Scraping()

  Dim Internet_Explorer As inter

End Sub

VBA Web Scraping Example 1.3.0

Step 4: Choose the "InternetExplorer."

The code for the above step is:

Sub Web_Scraping()

   Dim Internet_Explorer As InternetExplorer

End Sub

 Example 1.4.0

Step 5: The next step is that we need to set the reference point to enable the Internet Explorer. Since this is an object variable, we need to use a keyword called "Set" to set the references over the object.

The code is as follows:

Sub Web_Scraping()

  Dim Internet_Explorer As InternetExplorer
  Set Internet_Explorer = New InternetExplorer

End Sub

 Example 1.5.0

Step 6: The next move is to use the variable "Internet_Explorer," we can also use the properties and methods of the internet explorer.

Now, enter the variable name and put a dot to see the IntelliSense list.

The code for this step is:

Sub Web_Scraping()

  Dim Internet_Explorer As InternetExplorer
  Set Internet_Explorer = New InternetExplorer
  Internet_Explorer.

End Sub

VBA Web Scraping Example 1.6.0

Step 7: Now, to view the internet explorer application, we need to choose the "Visible" property and set the status as "True."

The code is as follows:

Sub Web_Scraping()

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

End Sub

 Example 1.7.0

Now run the code, and you should see an Internet Explorer opens up on your computer.

 Example 1.8

Step 8: As no web address has been mentioned here, we can see only a blank page. So, to give the web address to the internet explorer, we need to go to the "Navigation" method.

The code is as follows:

Sub Web_Scraping()

  Dim Internet_Explorer As InternetExplorer
  Set Internet_Explorer = New InternetExplorer
  Internet_Explorer.Visible = True
  Internet_Explorer.Navigate(

End Sub

Example 1.9.0

Step 9: You can see above that the "Navigation" method asks which URL to be navigated in internet explorer. Now I need to open this website named "Wallstreetnmojo," and I can give the URL address. "https://www.wallstreetmojo.com/"

Code:

Sub Web_Scraping()

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

End Sub

After you run the code, you should see the specified web address page in the internet explorer.

But there is a problem that once the web page is opened, our code needs to wait until the page web page is fully opened.

Step 10: To overcome this, we need to use the "Do While" loop in VBA to wait for our code to go further until the mentioned page is fully loaded.

So, add the "Do While" loop below to force the macro to wait until the mentioned web page comes to the "Ready State Complete" mode.

The code is as follows:

Sub Web_Scraping()

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

End Sub

Example 1.12.0

Step 11: Let's try to get information about the website in a single line. We need to use the "Location Name" property to get the web address information.

Code:

Sub Web_Scraping()

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

End Sub

VBA Web Scraping Example 1.13.0

Run the code, and in the message box, we would get the information about the website.

 Example 1.14

Step 12: Now, we can print website addresses at the bottom.

Code:

Sub Web_Scraping()

  Dim Internet_Explorer As InternetExplorer
  Set Internet_Explorer = New InternetExplorer
  Internet_Explorer.Visible = True
  Internet_Explorer.Navigate ("https://www.wallstreetmojo.com")
  Do While Internet_Explorer.ReadyState <> READYSTATE_COMPLETE: Loop
  MsgBox Internet_Explorer.LocationName & vbNewLine & vbNewLine
  & Internet_Explorer.LocationURL

End Sub

VBA Web Scraping Example 1.15.0

This will tell about the website description and show the website address.

 Example 1.16

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

Frequently Asked Questions

1. What are the pros of VBA web scrapping?

Data extraction at scale

Speed

Flexibility

Low maintenance costs

2. Why should scraping be performed with caution?

The scraping should be performed with caution and absolute carefulness as it can harm and crash the system being utilized for scraping.

Conclusion

This article has been a guide to VBA web scraping. Here we have discussed how to access websites from excel through VBA.

  • Web scraping is possible by accessing external browsing applications like Internet Explorer.
  • There are two ways to do it, i.e., Early Binding & Late Binding. With Early Binding, we can see the IntelliSense list, but we cannot see the IntelliSense list with late binding.

Click here to read about Introduction to ExcelWorksheets in Excel.


If you wonder how to prepare data structures and algorithms to do well in your programming interviews, here is your ultimate guide for practicing and testing your problem-solving skills on Coding Ninjas Studio

 

Happy Learning!!!

Previous article
Userforms
Next article
Excel vs Google Sheets
Live masterclass