[Excel VBA] How to Read XML Files: Extracting Nodes to Sheets Using DOM Objects

2025年12月30日

Introduction

XML files are frequently used for data integration between systems and for configuration files. There are often cases where you need to read this XML data in Excel and list it on a sheet.

In this article, I will introduce how to read XML format data node by node and output it to Excel using the VBA DOM parser (MSXML2.DOMDocument).

Expected XML Structure

Example: user_data.xml

<users>
  <user id="001">
    <name>Tanaka Taro</name>
    <email>tanaka@example.com</email>
  </user>
  <user id="002">
    <name>Yamada Hanako</name>
    <email>hanako@example.com</email>
  </user>
</users>

From this structure, we will extract the ID, Name, and Email address and paste them into Excel.

VBA Code: Read XML and Output to Excel

Sub ReadXMLToExcel()
    Dim xmlDoc As Object
    Dim itemNode As Object, userList As Object
    Dim rowIndex As Long
    
    ' Create the DOMDocument object
    Set xmlDoc = CreateObject("MSXML2.DOMDocument.6.0")
    xmlDoc.async = False
    
    ' Load the local XML file
    xmlDoc.Load ThisWorkbook.Path & "\user_data.xml"
    
    ' Get the list of child nodes from the root element
    Set userList = xmlDoc.DocumentElement.ChildNodes
    
    ' Loop through each user node
    For rowIndex = 0 To userList.Length - 1
        Set itemNode = userList.Item(rowIndex)
        
        With ThisWorkbook.Worksheets(1)
            ' Extract attribute (id)
            .Cells(rowIndex + 2, 2).Value = itemNode.getAttribute("id")
            ' Extract child node text (name)
            .Cells(rowIndex + 2, 3).Value = itemNode.ChildNodes(0).Text
            ' Extract child node text (email)
            .Cells(rowIndex + 2, 4).Value = itemNode.ChildNodes(1).Text
        End With
    Next
    
    ' Display raw XML for verification
    MsgBox xmlDoc.DocumentElement.XML, vbInformation, "XML Raw Data"
End Sub

Key Points of the Process

Code / Item	Explanation
CreateObject(“MSXML2.DOMDocument.6.0”)	Performs XML parsing based on DOM (Document Object Model).
Load	Loads a local XML file.
getAttribute(“id”)	Retrieves the attribute (ID) of the specific node.
ChildNodes(0).Text	Extracts the content of `<name>`.
ChildNodes(1).Text	Extracts the content of `<email>`.
DocumentElement.XML	Displays the entire XML structure as a string (useful for debugging).

Execution Result (Excel Sheet)

Column B (ID)	Column C (Name)	Column D (Email)
001	Tanaka Taro	tanaka@example.com
002	Yamada Hanako	hanako@example.com

Important Notes

Structure Changes: If the hierarchy of the XML file changes, you will need to adjust the index of ChildNodes() or how you specify the hierarchy.
Version: MSXML2.DOMDocument.6.0 is the standard Windows library, but MSXML2.DOMDocument.3.0 may be used depending on the environment.
Encoding: Since XML containing Japanese characters is often saved in UTF-8, it is recommended to use a format with BOM to prevent character corruption.

Application Tips

XPath: If you want to extract only specific tags regardless of depth, you can use SelectNodes("//TagName") (XPath support).
Direct Writing: It is possible to write the extracted data directly to another workbook.
JSON: XML converted from JSON format can also be handled with the same processing logic.

Summary

In Excel VBA, by utilizing the MSXML library, it is possible to accurately read the structure of XML data, extract only the necessary information, and format it for display on an Excel sheet.

This is a very powerful method when dealing with structured data.

よかったらシェアしてね！