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
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."
In the below window, scroll down and choose “Microsoft Internet Controls.”
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
Step 4: Choose the "InternetExplorer."
The code for the above step is:
Sub Web_Scraping()
Dim Internet_Explorer As InternetExplorer
End Sub
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
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
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
Now run the code, and you should see an Internet Explorer opens up on your computer.
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
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
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
Run the code, and in the message box, we would get the information about the website.
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
This will tell about the website description and show the website address.
24+ registered 
