Web Scraping - Complete Guide

In this lesson you will learn all the essentials of the VBA Web scraping. And at the end of the lesson, you will be able to write your first web scraping program to navigate to a webpage you like and collect the data. Also there are lots of other articles in this website which explain about various other techniques you can use in vba web scraping. So please check the link at the end of this lesson. You will find lots of helpful resources to develop efficient and robust VBA web scraping programs.

Today, websites play major role in our day today lives. Some websites are built to show particular information to the visitors. These websites usually contain only static web pages. But some websites can interact with their visitors. Amount of data included in the websites vary from one website to another. Some websites contain a little amount of data and some contain a large amount. Sometimes business organizations and individuals want to extract that information for their needs. This is where the technology called Web scraping comes into action. VBA Web scraping is a popular technique used to collect data automatically from websites to Excel.

Visual Basic for Applications (VBA) makes web scraping a powerful tool for extracting data from websites. VBA, integrated with Microsoft Excel, enables users to automate the retrieval of information, transforming tedious tasks into efficient processes. With a few lines of code, VBA can navigate web pages, interact with elements, and collect data systematically. This automation is particularly valuable for analysts, researchers, or businesses requiring up-to-date information for decision-making.

Here are some uses of web scraping

• To compare prices
• To monitor weather data
• To detect changes made to websites
• For research purposes
• To collect various other information

Complexity of a VBA web scraping program depends on the website's coding structure and the type of data we are going to collect.

So now let’s look at how we can create a simple VBA web scraping program which can open an internet explorer window and navigate to a particular webpage.

Before writing the VBA Web scraping program, we need to enable the developer tab and set up the Visual basic editor for coding.

Follow these simple steps to enable the developer tab if you don’t have it in your Excel ribbon.

1. Launch Microsoft Excel on your computer.
2. Click on the "File" tab in the ribbon and select "Options" at the bottom.
3. In the Excel Options window, choose "Customize Ribbon" from the left sidebar.
4. On the right side, you will see the main tabs. Check the box next to "Developer" to enable it, and then click "OK."
5. Go to the Developer tab on the ribbon, and click on "Visual Basic" to open the Visual Basic for Applications (VBA) Editor.

You can find a more detailed explanation from this post.
How to show the Developer tab in Excel

Now you have enabled the developer tab in Excel. The Developer tab provides access to various tools, including the Visual Basic Editor, allowing you to create powerful automation scripts and macros. So now we need to insert a module to write our VBA web scraping program.

To insert a module to our project in Excel, follow these simple steps:

1. Go to the Developer tab on the ribbon.
2. Click on "Visual Basic" to open the VBA Editor.
3. In the Project Explorer window on the left, click on the workbook(to select it if you have multiple workbooks open) for which you want to insert a module.
4. From the menu, select "Insert" and then choose "Module."
5. A new module will appear under the workbook in the Project Explorer.
6. Now double-click on the module to open it in the central editing area.

VBA web scraping programs use internet explorers to collect the data. But you don’t need to configure anything manually for the Internet explorer to do this. Internet explorer will behave according to the code written in the VBA web scraping program. When developing VBA web scraping software, our first step is to create a subroutine to write the code. Let’s name the subroutine as MyFirstWebScrapingProgram.

Sub MyFirstWebScrapingProgram()


End Sub

Then, first of all, we need to define a variable of type object and then assign an internet explorer object to it. You can use the below line of code to create that object.

Dim objIE As Object

Set objIE = CreateObject("InternetExplorer.Application")

Then set the following properties to the created object. You can change them according to your requirements. First two properties(Top and Left) decide the position of the internet explorer window. Next two properties(Width and Height) will decide the size of the window.

objIE.Top = 0
objIE.Left = 0
objIE.Width = 800
objIE.Height = 600

Following property enables the visibility of the internet explorer window. You can set it to false if you want to run the program in the background. If you set it to false, the user won’t see how the data is collected using the internet explorer. He/she will only see how the data is filled to the Excel file.

objIE.Visible = True

So now we have created the internet explorer object and set the relevant properties. Next let's see how to navigate to a webpage. You can use the below VBA statement to navigate to the webpage you want.

objIE.Navigate ("http://www.example.com")

When creating a web scraping software, it's crucial to bear following in mind. Once you develop a complete VBA web scraping program, it will have to run through several VBA statements after navigating to a specific webpage. However, webpages can take some time to be fully loaded due to various reasons. So when you run the program there is a chance of executing subsequent VBA statements before the webpage is fully loaded. This can adversely affect the results you get from the software. But luckily we can take preventive action to overcome this. You can use below VBA statements to tell the program to wait until the webpage is fully loaded.

Do
DoEvents
Loop Until objIE.readystate = 4

Below is the full code to create an Internet Explorer object and navigate to a webpage.

Sub MyFirstWebScrapingProgram()

     Dim objIE As Object

     Set objIE = CreateObject("InternetExplorer.Application")

     objIE.Top = 0
     objIE.Left = 0
     objIE.Width = 800
     objIE.Height = 600

     objIE.Visible = True

     objIE.Navigate ("http://www.example.com")

     Do
     DoEvents
     Loop Until objIE.readystate = 4

End Sub

Also if the website is temporarily unavailable, VBA web scraping programs can be suspended before collecting all the relevant data. So If our program tries to collect data from a website while it is down, it will halt our program. So we need to use error handling techniques to overcome temporary interruptions like this. Then our program will wait until the website is up again. If you don’t use this technique, then the user will have to check from time to time whether the program is working or halted.

Here is an example of using error handling in VBA web scraping.

Dim objIE As Object

the_start:

Set objIE = CreateObject("InternetExplorer.Application")
objIE.Top = 0
objIE.Left = 0
objIE.Width = 800
objIE.Height = 600
objIE.Visible = True

On Error Resume Next
objIE.navigate ("http://www.example.com/")

Do
   DoEvents
   If Err.Number <> 0 Then
        objIE.Quit
        Set objIE = Nothing
        GoTo the_start:
   End If
Loop Until objIE.readystate = 4

MsgBox "Web page has loaded"

In the above example we have used the error handling technique to check whether it has navigated to a webpage. But in practical situations a program will need to navigate through hundreds or thousands of pages. Then you will need to use a similar error handling technique inside loop iterations.

In the first steps of the web scraping programs, often we need to assign the HTML code to a variable of type string. So the below code shows how to assign HTML code of a webpage to a variable of type string. Here HTML_Code is the variable name.

Dim HTML_Code as String

HTML_Code = objIE.document.body.innerhtml

This will extract the inner HTML content of the <body> element of the web page loaded in the Internet Explorer (objIE) object and assign it to the variable called HTML_Code. You can even print the inner HTML content to the immediate window as follows.

Dim HTML_Code As String

HTML_Code = objIE.document.body.innerHTML

Debug.Print HTML_Code

Sometimes it is very easy to get required data from the inner-text without dealing with HTML codes. Below example shows how to assign inner text to a variable of type string and print it in the immediate window.

Dim InnerText as String

InnerText = objIE.document.body.innertext

Debug.Print InnerText

However if you do this, in lots of cases, you will realize that extracted content is not in a usable format. In lots of times you will get a stockpile of useless data. So in VBA web scraping we should always try to focus on getting only required data using id attributes and class attributes. Because these attributes help us to get only the targeted data. Read these posts to learn how to collect data using id and class attributes.
Web Scraping - Collecting Data From a Webpage
When to use the getElementsByClassName method
getElementsByClassName Vs getElementById

But sometimes required data might not be associated with any id or class name. In such cases we can use the javascript method “getElementsByTagName'' to collect the required data. Following example shows how to get elements by < a > tags. (anchor tags)

Set Alllinks = objIE.document.getelementsbytagname("A")
For Each Hyperlink In Alllinks
    MsgBox Hyperlink.innertext & "-" & Hyperlink.href
Next

Sometimes we need our VBA web scraping programs to follow the links and collect the data. To do that the program needs to click on < a > tags. Now let’s see how we can accomplish this. To achieve this the program needs to loop through all the < a > tags of the webpage and find the relevant link. Then the VBA program can click the link to navigate to that page. Let’s try to understand this using an example. In this example, the VBA web scraping program will click the link if it finds a link with the text "New Arrivals".

Set Alllinks = objIE.document.getelementsbytagname("A")
For Each Hyperlink In Alllinks
        If InStr(Hyperlink.innertext, "New Arrivals") > 0 Then
               Hyperlink.Click
               Exit For
        End If
Next

When creating VBA web scraping programs to collect data from websites, several built-in functions become imperative. Here are some key VBA functions essential for web scraping programs.

Commonly used VBA functions in web scraping

Mid - This VBA function can be used to extract a string segment from it's parent string.
InStr - This function can be used to check whether certain string is included in another string and get the position of that sub string if it exists.
Replace - This VBA function replaces a character or string segments with another character or string.

Above are the basics you need to know when developing VBA web scraping programs. Follow this complete guide if you want to become an expert in VBA web scraping.

Open and read text file

                         We need to handle text files in various situations. Sometimes we need to create, open, write  or delete text files while the program is running. So today I will explain you how to Open and read text file through VBA.
First of all we need to define the variables.

Dim myFile, text, textline as String

Variable "myFile" is used to store the file path of the text file. Each and every text lines will be stored temporarily to the variable call "textline" while program run throgh the Do loop until it reaches to the end of the file. All the text lines of the text file will be appended to variable "text".

There are two ways to assign the text file to "myFile" variable depending on your requirement.

Either you can use below line of code to assign the file path directly. This method can be used if you always use same folder path and file.

myFile = "D:\Fiverr\amjedamjed\Project 2\all - Copy (11).txt"

Or you can use below line of code to assign the file name to variable. Here user get opportunity to select the file through browsing window.

myFile = Application.GetOpenFilename()

So you should use one of the above two lines in your program according to end user requirement. However rest of the program will not affected by what you chooses from above two ways.  
Below line will open the file for reading

Open myFile For Input As #1

Below do loop will run until it reaches to the end of the text file. Inside the do loop, program read the text file line by line and assign that single line to variable "textline". Whole text of text file is appended to variable call "text" by the next line.
Do Until EOF(1)
    Line Input #1, textline
    text = text & textline
Loop

Below line of code will close the text file which was opened for reading.
Close #1

Now you have whole content of text file in variable "text". Now you can do any string manipulation to that text from here.
However I will just show the content in a message box.

MsgBox text

Here below is the full code for you to easily copy paste for your project

Dim myFile, text, textline as String myFile = "D:\Fiverr\amjedamjed\Project 2\all - Copy (11).txt" or myFile = Application.GetOpenFilename() Open myFile For Input As #1 Do Until EOF(1)     Line Input #1, textline     text = text & textline Loop Close #1 MsgBox text

VBA - Error 400

Have you ever got a error similar to shown below. If you are a VBA developer you might have got this error when you run or debug your program.
This error message contains only 400 and OK button.

I saw lot of people have asked about this error in lot forums. This error made developers very frustrated because it doesn't show any error message or anything. It only pop up a message box with "400" and OK button.

Soon after you press the OK button, it will kill the process.

However in microsoft website I found information about similar error. I saw following line in that website.

400 Form already displayed; can't show modally

So here what has happened is program tried to display the form again while it is already displayed. May be it is hidden by a sheet or another form.

But in our case error message is different. Only similarity is both the error messages have "400" in common. However after doing some experiments I was able to find one reason for "error 400". Think your program access files in your hard drives while it runs. So this error can occur if you have given non existent path to the program. So program try to access a file. But it is not existing. This will cause error 400. Then even missing of a one letter in the file path can throw this error. Lot of people says it takes much more time to identify a small misspelling than finding a complicated one. 

Open files in a specific directory (folder)

Some times VBA programs needs to access files from a specific folder. Assume you have a folder call "Test" within your drive "D".

Following code will open all the files inside that folder. You can easily modify this code for your requirement.

Dim file As Variant Dim pathAndFileName  as String file = Dir("D:\Test\") While (file <> "")     pathAndFileName = "D:\Test\" & file     Application.Workbooks.Open (pathAndFileName)     file = Dir Wend

Here is the explanation of how this code works.

First we need to define our variables. Variable "file" is used to get individual file names from the folder. Variable "pathAndFileName" is used to hold the file path and file name.

Dim file As Variant
Dim pathAndFileName  as String

Next we assign a our first value to the variable we have defined.

file = Dir("D:\Test\")

So now the name of firsts file inside the folder "Test" is assigned to the variable "file". In this example we use while wend loop to open all the files inside that folder. We use variable "pathAndFileName" to hold the file path and file name. 

 pathAndFileName = "D:\Test\" & file

Next we use  Application.Workbooks.Open method to open the file.



Application.Workbooks.Open (pathAndFileName)

So now we have opened the first file in that directory. We need to repeat the process for all the other files. But before going to next cycle we need to clear the attributes of our variable "file". We use following line of code to clear them

file = Dir

That is the full explanation of the code.

Finding a specific file from a folder

Some times we need to check whether specific file is contained in a folder. Below example checks whether file "book1.xlsx" is contained in a folder call "Test" which is located in "D" drive. Then open the file if it is exists.

Dim file As Variant Dim FName, pathAndFileName As String FName = "book1.xlsx" file = Dir("D:\Test\") While (file <> "")     If StrComp(file, FName) = 0 Then         pathAndFileName = "D:\Test\" & file         Application.Workbooks.Open (pathAndFileName)         MsgBox "found " & file     End If file = Dir Wend

Here is the explanation of how this code works.

First we need to define our variables. Variable "file" is used to get individual file names from the folder. Variable "pathAndFileName" is used to hold the file path and file name. Variable FName holds the file name we want to check.

Dim file As Variant
Dim FName, pathAndFileName  As String
FName = "book1.xlsx"

Next we assign the first file of the folder to the variable we have defined.

file = Dir("D:\Test\")

So now the name of firsts file inside the folder "Test" is assigned to the variable "file". In this example we use while wend loop to compare all the files inside that folder with file name we want. For the comparison we use below line of code

 If StrComp(file, FName) = 0 Then

If the file names matched then the codes inside the if statement will be executed.
We use variable "pathAndFileName" to hold the file path and file name. 

 pathAndFileName = "D:\Test\" & file

Next we use  Application.Workbooks.Open method to open the file.

Application.Workbooks.Open (pathAndFileName)

Then program will give the message that it has found the file we want.



 MsgBox "found " & file

There is important thing we need to do when loop through the program. It is that we need to clear the attributes of our variable "file"  before going to next cycle. We use following line of code to clear them

file = Dir

Sort Function

                   Today we will discuss about using Sort function through VBA. So assume you have below kind of excel sheet. You can notice that values in column A under the title "NO" are not in and order. So what we going to do is sort the data according to the values in column  A.

Just go to the Data tab of excel application. You will see below icon in that tab.

                   If you click that icon, it will pop-up a table similar to below. From that table you can choose based on what column you going to sort the data. Based on what, you going to sort the data. And also you can select the order as well.

                      After selecting the parameters accordingly, you can click the OK. Button. Then worksheet will end up with result similar to shown in below image.

So now we have come to the important part. What we are going to do is complete the above process through VBA. You can use below code for that. This sample code will sort data from cells A2 to E13 based on values in the 1st column.

Dim WS As Worksheet Set WS = ActiveSheet WS.Range("A3:E13").Select WS.Sort.SortFields.Clear WS.Sort.SortFields.Add Key:=WS.Range _     ("A3:A13"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _     xlSortNormal With WS.Sort     .SetRange WS.Range("A3:E13")     .Header = xlGuess     .MatchCase = False     .Orientation = xlTopToBottom     .SortMethod = xlPinYin     .Apply End With

Also you can use this subroutine to sort data in ascending order. This subroutine take data range, sort fields as parameters. So you can readily use this inside your projects as separate sub routine.

Sub SortAscending(WS As Worksheet, FirstColumn As String, LastColumn As String, KeyColumn As String, _ FirstRow As Long, LastRow As Long) WS.Range(FirstColumn & FirstRow & ":" & LastColumn & LastRow).Select WS.Sort.SortFields.Clear WS.Sort.SortFields.Add Key:=WS.Range _     (KeyColumn & FirstRow & ":" & KeyColumn & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _     xlSortNormal With WS.Sort     .SetRange WS.Range(FirstColumn & FirstRow & ":" & LastColumn & LastRow)     .Header = xlGuess     .MatchCase = False     .Orientation = xlTopToBottom     .SortMethod = xlPinYin     .Apply End With End Sub

And this is the subroutine for sort descending

Sub SortDescending(WS As Worksheet, FirstColumn As String, LastColumn As String, KeyColumn As String, _ FirstRow As Long, LastRow As Long) WS.Range(FirstColumn & FirstRow & ":" & LastColumn & LastRow).Select WS.Sort.SortFields.Clear WS.Sort.SortFields.Add Key:=WS.Range _     (KeyColumn & FirstRow & ":" & KeyColumn & LastRow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _     xlSortNormal With WS.Sort     .SetRange WS.Range(FirstColumn & FirstRow & ":" & LastColumn & LastRow)     .Header = xlGuess     .MatchCase = False     .Orientation = xlTopToBottom     .SortMethod = xlPinYin     .Apply End With End Sub

Sometimes we need to add two or more levels when we sorting the data. This is how we do it manually.

Following is a subroutine which can use for two levels. This will sort both levels in ascending order.

Sub SortAscendingTwoLevels(WS As Worksheet, FirstColumn As String, LastColumn As String, _ KeyColumn As String, SecondKeyColumn As String, FirstRow As Long, LastRow As Long) WS.Range(FirstColumn & FirstRow & ":" & LastColumn & LastRow).Select WS.Sort.SortFields.Clear WS.Sort.SortFields.Add Key:=WS.Range _     (KeyColumn & FirstRow & ":" & KeyColumn & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _     xlSortNormal WS.Sort.SortFields.Add Key:=WS.Range _     (SecondKeyColumn & FirstRow & ":" & SecondKeyColumn & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _     xlSortNormal With WS.Sort     .SetRange WS.Range(FirstColumn & FirstRow & ":" & LastColumn & LastRow)     .Header = xlGuess     .MatchCase = False     .Orientation = xlTopToBottom     .SortMethod = xlPinYin     .Apply End With End Sub

If you want to sort both levels in descending order, then you can use below subroutine.

Sub SortDescendingTwoLevels(WS As Worksheet, FirstColumn As String, LastColumn As String, _ KeyColumn As String, SecondKeyColumn As String, FirstRow As Long, LastRow As Long) WS.Range(FirstColumn & FirstRow & ":" & LastColumn & LastRow).Select WS.Sort.SortFields.Clear WS.Sort.SortFields.Add Key:=WS.Range _     (KeyColumn & FirstRow & ":" & KeyColumn & LastRow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _     xlSortNormal WS.Sort.SortFields.Add Key:=WS.Range _     (SecondKeyColumn & FirstRow & ":" & SecondKeyColumn & LastRow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _     xlSortNormal With WS.Sort     .SetRange WS.Range(FirstColumn & FirstRow & ":" & LastColumn & LastRow)     .Header = xlGuess     .MatchCase = False     .Orientation = xlTopToBottom     .SortMethod = xlPinYin     .Apply End With End Sub

Date format issue (How to solve)

     
                  In this lesson I'm going to teach you how to overcome formatting errors when you input data to excel sheet from a text box, combo box etc.

                  Here is a my own experience. Once I had a date value 01/08/2014 in one of text box of a user form.I got that value to the text box from a date picker. So it was in the format dd/mm/yyyy.
So the above date refers to 1st of August 2014.

                  However when data entered to the excel sheet it took the form of 08/01/2014
So now it refers to 8th of January 2014
    
                  If you face this kind of situation here below is the solution.Just format the value using Format function before input data to the sheet.

Here txtDueDate is the name of the text box. Below example enters date value to cell E3 of worksheet("Data")

Dim WS as worksheet     set WS=worksheets("Data")     Dim DDate As Date     DDate = Format(txtDueDate.Text, "dd/mm/yyyy")     WS.Range("E3").Value = DDate