VBA code to pull data from website

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

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.

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.

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"

⧪ 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

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)

⧪ 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

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

⧭ 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.

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

⧭ 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

• How to Import Data from Secure Website to Excel (With Quick Steps)
• How to Convert Excel to Text File with Pipe Delimiter (2 Ways)
• Convert Excel to Text File with Delimiter (2 Easy Approaches)
• How to Convert Notepad to Excel with Columns (5 Methods)
• How to Extract Data from Excel Sheet (6 Effective Methods)

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

⧭ Output:

The code creates a function called PullData.

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

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.

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

• How to Extract Data from Excel Based on Criteria (5 Ways)
• Extract Text After a Character in Excel (6 Ways)
• How to Extract Month from Date in Excel (5 Quick Ways)
• How to Extract Year from Date in Excel (3 Ways)
• Return Multiple Values in Excel Based on Single Criteria (3 Options)
• How to Extract Data from Excel to Word (4 Ways)