[Excel VBA] How to Export Table Data as an XML Tree Structure

Introduction

There are many situations where you need to export Excel table data as hierarchical XML files for system integration. While manual conversion is difficult, VBA can automate this process.

In this article, I will explain practical VBA code that reads a range of Excel cells and builds a dynamic XML tree using the header row as tag names.

Example Data

First, assume the following table is prepared on Sheet1 as the source data. Row 1 contains headers (which will become XML element names), and data starts from Row 2.

OrderID	OrderDate	CustomerName	Amount
T-001	2025/8/1	Sample Corp	50000
T-002	2025/8/5	Test Ltd	75000
T-003	2025/8/9	ABC Trading	120000

Complete VBA Code

The following VBA code converts the table data above into structured XML.

Sub ExportTableToXmlTree()

    ' Declare variables
    Dim xmlDoc As Object
    Dim rootNode As Object
    Dim recordNode As Object
    Dim fieldNode As Object
    Dim sourceRange As Range
    Dim i As Long, j As Long

    ' Create XML document object
    Set xmlDoc = CreateObject("MSXML2.DOMDocument.6.0")
    
    ' --- 1. Create basic XML structure ---
    With xmlDoc
        ' Add XML declaration
        .AppendChild .CreateProcessingInstruction("xml", "version=""1.0"" encoding=""UTF-8""")
        
        ' Create root element wrapping the whole structure (e.g., <OrderList>)
        Set rootNode = .CreateElement("OrderList")
        .AppendChild rootNode
    End With
    
    ' --- 2. Read Excel data and build XML tree ---
    ' Set target cell range (including header row)
    Set sourceRange = ThisWorkbook.Worksheets("Sheet1").Range("A1:D4")
    
    ' Loop through data rows (from row 2 to last row)
    For i = 2 To sourceRange.Rows.Count
        ' Create parent element for 1 record (e.g., <Order>)
        Set recordNode = xmlDoc.CreateElement("Order")
        ' Set value of 1st column as "id" attribute
        recordNode.setAttribute "id", sourceRange.Cells(i, 1).Value
        
        ' Loop through field columns (from col 2 to last col)
        For j = 2 To sourceRange.Columns.Count
            ' Create element name dynamically using header row (row 1) value (e.g., <OrderDate>)
            Set fieldNode = xmlDoc.CreateElement(sourceRange.Cells(1, j).Value)
            ' Add data row value as text to the element
            fieldNode.Text = sourceRange.Cells(i, j).Value
            ' Append created field element as child of record node
            recordNode.AppendChild fieldNode
        Next j
        
        ' Append completed record element to root node
        rootNode.AppendChild recordNode
    Next i
    
    ' --- 3. Save to file ---
    xmlDoc.Save ThisWorkbook.Path & "\OrderData.xml"
    
    ' Release objects
    Set fieldNode = Nothing
    Set recordNode = Nothing
    Set rootNode = Nothing
    Set xmlDoc = Nothing
    
    MsgBox "XML file export complete."

End Sub

Key Points of the Code

1. Preparation of the Root Element

First, we create the “Root Element” (in this example, <OrderList>) which will contain all records. We also add the XML declaration to prepare the basic document structure.

2. Loop for Data Rows (For i = ...)

For i = 2 To sourceRange.Rows.Count

In the first loop, we process the data line by line, from the second row to the last. One iteration of this loop creates one XML record (in this example, the <Order> element).

3. Creating Record Elements and Setting Attributes

Set recordNode = xmlDoc.CreateElement("Order")
recordNode.setAttribute "id", sourceRange.Cells(i, 1).Value

Inside the loop, we create the parent element for each record (<Order>). We then use .setAttribute to set the ID for identification. Here, we use the value from the first column of Excel as the ID.

4. Loop for Field Elements (For j = ...)

For j = 2 To sourceRange.Columns.Count

The inner loop processes each item within the record (Date, Customer Name, Amount, etc.).

5. Dynamic Element Name Creation

Set fieldNode = xmlDoc.CreateElement(sourceRange.Cells(1, j).Value)

This is the most important technique in this code. Instead of hardcoding tag names like “OrderDate”, we dynamically retrieve the header from the first row of the Excel sheet (Cells(1, j)). This means you don’t need to modify the code even if the column names change.

6. Assembling the Tree (.AppendChild)

recordNode.AppendChild fieldNode  ' Add field to record
rootNode.AppendChild recordNode   ' Add record to root

We use AppendChild to add the field elements (like <OrderDate>) to the record element (<Order>). Once the inner loop is finished, we add that record element to the root element (<OrderList>). This nested assembly forms the XML tree.

Example of Generated XML

Running the code above generates a file named OrderData.xml with the following content:

<?xml version="1.0" encoding="UTF-8"?>
<OrderList>
    <Order id="T-001">
        <OrderDate>45879</OrderDate>
        <CustomerName>Sample Corp</CustomerName>
        <Amount>50000</Amount>
    </Order>
    <Order id="T-002">
        <OrderDate>45883</OrderDate>
        <CustomerName>Test Ltd</CustomerName>
        <Amount>75000</Amount>
    </Order>
    <Order id="T-003">
        <OrderDate>45879</OrderDate>
        <CustomerName>ABC Trading</CustomerName>
        <Amount>120000</Amount>
    </Order>
</OrderList>

(Note: Dates may appear as serial numbers depending on formatting. Use the Format function in VBA if you need specific date formats.)

Summary

To convert Excel table data into structured XML, the keys are “Nested Loops” and “Dynamic Element Name Creation.”

1. Create a parent element for one record in the outer loop.
2. Dynamically generate elements for each field in the inner loop and add them to the parent.

By applying this pattern, you can flexibly convert various types of table data into XML.