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

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


Download Practice Workbook

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


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://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.


2 Handy Approaches to Pull Data Automatically into Excel VBA

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.

Read More: How to Pull Data From Another Sheet Based on Criteria in Excel


Similar Readings


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://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: Transfer Data from One Excel Worksheet to Another Automatically


Conclusion

So, this is the way to pull data automatically into VBA in Excel. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.


Related Articles

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo