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

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

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

Step 1 – Setting up the Environment

• Press Alt + F12 on your keyboard. The Visual Basic window will open.
• Go to the toolbar at the top and click on Tools and select References.

• A dialogue box called References – VBAProject will open. Check Microsoft HTML Object Library and Microsoft Internet Controls.

Step 2 – Inserting a New Module

• Go to the toolbar and click on Insert and select Module. A new module (named depending on your past history) will open.

• We’ll insert the VBA code here.

Step 3 – Inserting the Inputs

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. Our website address is “https://www.exceldemy.com” and the HTML tag is div.

• Copy the following code snippet into the module :

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.

• Copy the following snippet:

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

Step 5 – Navigating to the Website

• Copy the following snippet to navigate to the target 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

We’ll extract the necessary data from the given HTML tag using the getElementsbyTagName method.

• Copy the following snippet:

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

Step 7 (Optional) – Displaying the Data

The previous code snippets pulled the necessary data from the website as an array called Data. If you want to display any specific data, you can use that specific property of the getElementsbyTagName method.

• To display the inner HTML of the 1st element, 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 first div element in a Message Box.

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

Method 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. We’ll 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 (after changing the inputs). It’ll pull the inner HTML of each tag in the corresponding column like this.

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

The VBA code for this 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:

• Paste the code in a new VBA Module and save it. The code creates a function called PullData.
• Select any range of cells in your worksheet and enter the formula:

• Press Ctrl + Shift + Enter (Use Enter only in Office 365).
• This pulls the innerHTML of all the head tags of the website “https://www.exceldemy.com” in a range.

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

Related Articles

• Excel VBA: Web Scraping with Chrome
• Excel VBA to Scrape Table from Website
• How to Do Web Scraping Without Browser with Excel VBA