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.

How to Use Web Scraping in VBA Excel?

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

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.

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

Code:

Sub VBA_WebScraping()

End Sub

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

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

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.

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

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

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

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

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

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

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.

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

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?