VBA Web Scraping

VBA Web Scraping is a technique of accessing web pages and downloading the data from that website to our computer files. Web Scraping is possible by accessing external applications like Internet Explorer. We can do it in two ways: Early Binding and Late Binding.

Web Scraping with VBA means when we use VBA to fetch the data from the other sources on the web. It may require logins for the data sources. But first, to do so, we need to enable the references from the "Tools" section in the VBA Editor for the Microsoft HTML library to access the web from VBA.

Not many of us know that from Excel, we can access web pages and get the data from those web pages. Yes, you heard it right. We can scrape through web pages, access browsing applications, and many more. This article will detail how to write an Excel VBA code for Web Scraping.

Usually, we open the web pages, copy the data, and paste it into our files like Excel, word, or some other files. But in this article, we will show you how to access websites from Excel and do many other kinds of stuff.

When we want to access any other applications from excel, we can do this in ways: Early Binding and Late Binding. Using the "Early Binding" technique at the beginner stage is always safe.

To access the website, we need browsing applications like "Internet Explorer." Since it is an external object, we must 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."

Code:

Sub Web_Scraping()

  Dim Internet_Explorer As internet

End Sub

As you can see above, when we try to set the reference to Internet Explorer, we don't see "Internet Explorer" because "Internet Explorer" is an external object, so we need to set the reference.

Step 2: To set the reference go to “Tools” and choose “References.”

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

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

Code:

Sub Web_Scraping()

  Dim Internet_Explorer As inter

End Sub

Step 4: Choose “InternetExplorer.”

Code:

Sub Web_Scraping()

   Dim Internet_Explorer As InternetExplorer

End Sub

Step 5: Next, we need to set the reference to enable Internet Explorer. Since this is an object variable, we need to use the "Set" keyword to set the references.

Code:

Sub Web_Scraping()

  Dim Internet_Explorer As InternetExplorer
  Set Internet_Explorer = New InternetExplorer

End Sub

Step 6: Now, using the variable "Internet_Explorer," we can use the properties and methods of Internet Explorer.

Enter the variable name and put a dot to see the IntelliSense list.

Code:

Sub Web_Scraping()

  Dim Internet_Explorer As InternetExplorer
  Set Internet_Explorer = New InternetExplorer
  Internet_Explorer.

End Sub

Step 7: To view the Internet Explorer application, we must choose the "Visible" property and set the status as "True."

Code:

Sub Web_Scraping()

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

End Sub

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

Step 8: Since no web address is mentioned, we can see only a blank page. To give the web address to Internet Explorer, we need the "Navigation" method.

Code:

Sub Web_Scraping()

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

End Sub

Step 9: As you can see above "Navigation" method asks which URL to navigate in Internet Explorer. Now, we need to open the website "Wallstreetmojo," and we can give the URL address as follows. "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

Now, run the code. We should see the mentioned web address page in Internet Explorer.

Here, we have a problem: once the web page opens, our code needs to wait until the page web page fully opens.

Step 10: 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 enters the "Ready State Complete" mode.

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

End Sub

Step 11: Now, let us try to get information about the website in a single line. We need to use the "Location Name" property to get the mentioned 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

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

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

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

Now, this will tell about the website description and also shows the website address.

• Web Scraping is possible by accessing external applications like Internet Explorer.
• We can do it in two ways: Early Binding and Late Binding. With Early Binding, we can see the IntelliSense list. But with Late Binding, we cannot get to see the IntelliSense list.

This article has been a guide to VBA Web Scraping. Here, we discuss how to access websites from excel through VBA code with examples and download an Excel template. You may also have a look at other articles related to Excel VBA: -

• VBA SendKeys
• How to Sort Range in VBA?
• Login Form with VBA
• Select Range using VBA