Excel VBA: Pull Data Automatically from a Website (2 Methods)

Get FREE Advanced Excel Exercises with Solutions!

An important task that most developers encounter while working with Excel VBA or any other programming language is to pull data automatically from a website. Today in this article, I’ll show you how you can use Excel VBA to pull data automatically from a website quite sophistically and conveniently.


Excel VBA to Pull Data Automatically from a Website (Quick View)

Sub Pull_Data_from_Website()

Website_Address = "https://exceldemy.com"
HTML_Tag = "div"

Dim Browser As New InternetExplorer
Dim Doc As New HTMLDocument
Dim Data As Object

Browser.Visible = True
Browser.navigate Website_Address

Do
DoEvents
Loop Until Browser.readyState = READYSTATE_COMPLETE
Set Doc = Browser.document
Set Data = Doc.getElementsByTagName(HTML_Tag)

MsgBox Data(0).innerHTML

End Sub

VBA Code to Pull Data Automatically from Website into Excel VBA


An Overview to Pull Data Automatically from a Website into Excel VBA (Step-by-Step Analysis)

Without further delay, let’s go to our main discussion today. Let’s see how we can pull data from a website into Excel VBA with step-by-step analysis.

⧪ Step 1: Setting up the Environment

First of all, we have to set up the environment to pull data. Press ALT+F12 on your keyboard. The Visual Basic window will open.

Go to the toolbar at the top and click on Tools > References.

Setting up Environment to Pull Data Automatically from a Website into Excel VBA

A dialogue box called References – VBAProject will open. Check Microsoft HTML Object Library and Microsoft Internet Controls if they are unchecked.

Now the environment for pulling data is set.

⧪ Step 2: Inserting a New Module

Go to the toolbar and click on Insert > Module. A new module called Module1 (Or anything other depending on your past history) will open.

Inserting Module to Pull Data Automatically from a Website into Excel VBA

We’ll insert the VBA code here.

⧪ Step 3: Inserting the Inputs

Now it’s time for the code. First of all, we have to insert the inputs into the code. These are the website address and the name of the HTML tag from which we want to scrape data.

Let’s our website address is “https://www.exceldemy.com” and the HTML tag is div.

Website_Address = "https://exceldemy.com"
HTML_Tag = "div"

Inserting Inputs to the Code to Pull Data Automatically from a Website into Excel VBA

⧪ Step 4: Declaring the Necessary Objects

Next, we have to declare the necessary objects. They are a browser for navigating to the website, an HTML Document, and a data object.

Dim Browser As New InternetExplorer
Dim Doc As New HTMLDocument
Dim Data As Object

⧪ Step 5: Navigating to the Website

Next, we’ll navigate to the input website.

Browser.Visible = True
Browser.navigate Website_Address

Navigating to the Website to Pull Data Automatically from a Website into Excel VBA

The line <b><span style="color: #ff6600;">Browser.Visible = True</span> </b>is optional. If you put this line and run the code here, the declared browser will open on your computer taking you to the given address.

⧪ Step 6: Pulling the Data

Now the most important task. We’ll extract the necessary data from the given HTML tag using the getElementsbyTagName method of VBA.

Do
DoEvents
Loop Until Browser.readyState = READYSTATE_COMPLETE
Set Doc = Browser.document
Set Data = Doc.getElementsByTagName(HTML_Tag)

VBA Code to Pull Data Automatically from a Website into Excel VBA

⧪ Step 7 (Optional): Displaying the Data

You have successfully pulled the necessary data from the website as an array called Data. Now if you want to display any specific data, you can use that specific property of the getElementsbyTagName method.

For example, to display the inner HTML of the 1st element, you can use:

MsgBox Data(0).innerHTML

Displaying to Pull Data Automatically from a Website into Excel VBA

Therefore, the complete VBA code will be:

⧭ VBA Code:

Sub Pull_Data_from_Website()

Website_Address = "https://exceldemy.com"
HTML_Tag = "div"

Dim Browser As New InternetExplorer
Dim Doc As New HTMLDocument
Dim Data As Object

Browser.Visible = True
Browser.navigate Website_Address

Do
DoEvents
Loop Until Browser.readyState = READYSTATE_COMPLETE
Set Doc = Browser.document
Set Data = Doc.getElementsByTagName(HTML_Tag)

MsgBox Data(0).innerHTML

End Sub

VBA Code to Pull Data Automatically from a Website into Excel VBA

⧭ Output:

Run the code. It’ll display the inner HTML of the 1st div element in a Message Box.


Pull Data Automatically from a Website into Excel VBA: 2 Handy Approaches

We’ve learned how to pull data automatically into Excel VBA. Now, we’ll develop a Macro and a User-Defined function to accomplish this.


1. Developing a Macro to Pull Data Automatically into Excel VBA

Here we’ve got a website address in cell B2 along with the names of some HTML tags in range B4:D4.

Data Set to Pull Data Automatically from a Website into Excel VBA

Our object is to develop a Macro to pull the inner HTML of each tag from the mentioned website in the corresponding column.

The VBA code will be:

⧭ VBA Code:

Sub Pull_Data_from_Website()

Website_Address = Range("B2").Value

Dim Browser As New InternetExplorer
Dim Doc As New HTMLDocument
Dim Data As Object

Set HTML_Tags = Range("B4:D4")

For i = 1 To HTML_Tags.Columns.Count

    HTML_Tag = HTML_Tags.Cells(1, i)

    Browser.navigate Website_Address

    Do
    DoEvents
    Loop Until Browser.readyState = READYSTATE_COMPLETE
    Set Doc = Browser.document
    Set Data = Doc.getElementsByTagName(HTML_Tag)

    For j = 1 To Data.Length
        HTML_Tags.Cells(j + 1, i) = Data(j - 1).innerHTML
    Next j

Next i

End Sub

Developing Macro to Pull Data Automatically from a Website into Excel VBA

⧭ Output:

Run the code (Obviously after changing the inputs). It’ll pull the inner HTML of each tag in the corresponding column like this.


2. Creating a User-Defined Function to Pull Data Automatically into Excel VBA

Now, we’ll develop a User-Defined function to pull data of a given HTML tag from a given website.

The VBA code will be:

⧭ VBA Code:

Function PullData(Website_Address, HTML_Tag)

Dim Browser As New InternetExplorer
Dim Doc As New HTMLDocument
Dim Data As Object

Browser.navigate Website_Address

Do
DoEvents
Loop Until Browser.readyState = READYSTATE_COMPLETE
Set Doc = Browser.document
Set Data = Doc.getElementsByTagName(HTML_Tag)

Dim Output As Variant
ReDim Output(Data.Length - 1)

For i = LBound(Output) To UBound(Output)
    Output(i) = Data(i).innerHTML
Next i

PullData = Output

End Function

Creating Function to Pull Data Automatically from a Website into Excel VBA

⧭ Output:

The code creates a function called PullData.

Select any range of cells in your worksheet and enter the formula:

<b>=PullData(B2,B4)</b>

Then press CTRL + SHIFT + ENTER (Not necessary if you are in Office 365).

It’ll pull the innerHTML of all the head tags of the website “https://www.exceldemy.com” in a range.

Entering Function to Pull Data Automatically from a Website into Excel VBA

Then you can drag the Fill Handle rightward to repeat the same procedure for the rest of the tags.

Read More: How to Import Data from Website to Excel with VBA Code


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

So, this is the way to pull data automatically into VBA in Excel. Do you have any questions? Feel free to ask us.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

4 Comments
  1. The code you wrote here is absolutely fantastic and extremely helpful; and your explanation as to how it works is one of the most cogent I have seen on the web.

    Unfortunately, likely due to my environment (Windows 2007 Professional) and Excel 2007, I cannot get this code to work on the google maps website which is mission critical to an application that I am writing. Apparently that site needs Edge, and for the life of me, I cannot seem to find what I have to do to access a couple of functions that are in the LIB “user32″….almost as if that library does not exist in Excel release 12.0

    Maybe you can point me in the right direction?

  2. Hi, thanks for the help!

    Any way we can open Google Chrome instead of opening Internet Explorer?

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo