Extract or Get data from HTML Element in Excel using VBA

Last updated: 14th June 2025

Easily extract data from websites into Excel using web scraping techniques. Excel’s built-in web query feature allows you to import structured or tabular data from any webpage directly into your spreadsheet. To further automate data extraction, you can use VBA (Visual Basic for Applications) to create custom macros. This tutorial demonstrates how to use Excel VBA to read and extract content from specific HTML elements, streamlining your web data collection process.

📑 Table of Contents

1) What this macro can do?
2) How to Identify and Read HTML Elements in a Webpage with Excel VBA?
3) Inspecting Web Elements
4) Setting Up Excel for VBA
5) Adding Microsoft HTML Object Library
6) Writing the VBA Macro
7) Code Explained Step-by-Step

What this macro can do?

Using the script (below), you can extract specific contents from any websites or a webpage. For example, you can extract news headlines from a news portal, or get stock quotes from a web page etc. This process is popularly known by the term Screen Scraping.

How to Identify and Read HTML Elements in a Webpage with Excel VBA?

Contents in webpage, are embeded inside HTML elements. For example, to add a "paragraph" in a webpage, you can use the <p></p> element. Every HTML element has a tag and you can identify each element by its tag. To read the content of an element (from Excel using VBA), you will first have to locate the element by its tag on the webpage. In-addition, some elements will also have a unique id.

Now let us assume, I want to read all the headings under "Latest Articles" from this web page. The header texts are inside a <p> tag (or paragraph). All the "headers" are embeded inside a DIV element, which is the parent. The parent has an id (a unique id). The script will use this id to locate the elements that we need.

See the below image.

Extract HTML Element Contents in WebPage using VBA in Excel

How to Use the Browser Inspect Tool to Identify HTML Elements for Web Scraping?

How do you know which HTML element it is? Its simple. Go to the home page, scroll down and find "Latest Articles" section. Set the cursor on the header and right click the mouse, choose Inspect option and click it. (This is for Chrome and Edge browsers). It will open the browsers console window, highlighting the element.

Let us now see the example.

Open an Excel file and save the file in .xlsm format (macro format). Then, press Ctrl+F11 to open the VBA editor.

Add Microsoft HTML Object Library Reference

To make the macro work with an external source such as a webpage, we’ll need Internet Explorer reference. In-addition, to read and extract contents of HTML elements, we’ll have to create few objects using a library.

Note: Microsoft has ended support for IE. However, the example that I am sharing here still works in Windows 11.

Therefore, first add the Microsoft HTML Object Library reference to the application. From the top menu of your VBA editor, click Tools -> References…. In the References window, find and select Microsoft HTML Object Library and click OK.

The Macro

Option Explicit

Const sSiteName = "https://www.encodedna.com/"

Private Sub getHTMLContents()
    ' create internet explorer object.
    Dim IE As Object
    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = False          ' keep this hidden.
    
    IE.navigate sSiteName
    
    ' wait till ie is fully loaded.
    While IE.readyState <> 4
        DoEvents
    Wend
    
    Dim oHDoc As HTMLDocument     ' create document object.
    Set oHDoc = IE.document
    
    Dim oHEle As HTMLDivElement     ' create html element (<div>) object.
    Set oHEle = oHDoc.getElementById("latest")   ' get the element reference using its id. this is the parent element.
    
    Dim iCnt As Integer
    
    ' loop through elements inside the parent <div> element and find <p>,
        ' which has the content we want.
    With oHEle
        For iCnt = 0 To .getElementsByTagName("p").Length - 1
            Debug.Print .getElementsByTagName("p").Item(iCnt).innerHTML   ' show the result in immediate window.
        Next iCnt
    End With
    
    ' clean up.
    IE.Quit
    Set IE = Nothing
    Set oHEle = Nothing
    Set oHDoc = Nothing
End Sub

You can see the output in your VBA editors immediate window.

Code Explained Step-by-Step

At the start of the code, I’ve specified the target website URL (const sSiteName). You can easily modify this to point to any other webpage you’d like to extract data from.

Also, there’s no need to add a reference to "Internet Explorer" in your VBA project. Instead, the code dynamically creates an IE object using the CreateObject() method, which keeps the setup simple and avoids hard dependencies.

Dim IE As Object
Set IE = CreateObject("InternetExplorer.Application")

You can run Internet Explorer in the background without displaying the browser window. This keeps the scraping process discreet and efficient. The macro will then pause until the webpage has fully loaded before continuing.

While IE.ReadyState <> 4
    DoEvents
Wend

Next, I’ve create two HTML objects of properties namely, HTMLDocument and HTMLUListElement.

Dim oHDoc As HTMLDocument
Dim oHEle As HTMLUListElement

The HTMLUListElement interface provides convenient methods to interact with and manipulate list elements (<ul>) in the DOM. Likewise, you can use other interfaces such as HTMLDivElement to access and work with the contents of a <div> element.

In this example, the macro loops through each child element inside the parent <div> and looks specifically for <p> tags. It then uses the innerHTML property to retrieve and extract the content contained within each paragraph.

.getElementsByTagName("p").Item(iCnt).innerHTML

Don’t forget to clean the memory and close the Internet Explorer (which is still running while the macro fetches the content).

The script above prints the data in the immediate window using Debug.print method. However, you can write the data in your Excel’s worksheet like this …

Cells(iCnt + 1, 1) = .getElementsByTagName("p").Item(iCnt).innerHTML

Hope you find the macro useful. Let me know if you have any query.

← PreviousNext →