Building a VBA-Powered XML Import and Export Engine: Parse, Transform, and Sync Structured Data Between Excel and External Systems

Introduction

You're a data analyst at a logistics company. Every morning, your ERP system dumps a 2,000-line XML file into a shared network folder — purchase orders, shipment statuses, supplier codes — and someone has to manually open it, copy data into Excel, clean it up, and distribute a formatted report before the 9 AM standup. That someone is you. The process takes 40 minutes and it's error-prone, tedious, and frankly beneath your skill level.

Or maybe you're on the other end: your team has built a clean Excel-based data collection tool, and now the IT department needs that data sent back to a REST API or loaded into a system that only accepts XML. You've got the data; you just don't have a bridge between Excel and that structured format.

XML is the connective tissue of enterprise software. ERP systems, CRMs, healthcare platforms, financial data feeds, and supply chain tools all speak XML. Building a VBA-powered engine that reads, parses, transforms, and writes XML means you can make Excel talk to virtually any external system — without waiting for IT, without a middleware tool, and without converting everything to CSV and hoping for the best.

By the end of this lesson, you'll have a production-quality XML engine built entirely in VBA.

What you'll learn:

• How to use the MSXML2.DOMDocument object to load, parse, and navigate XML in VBA
• How to extract data from complex, nested XML structures into a worksheet
• How to build an XML export engine that writes structured XML from Excel data
• How to handle namespaces, attributes, and edge cases that break naive parsers
• How to wire up a sync workflow that imports, compares, and flags changes between XML snapshots

---

Prerequisites

You should be comfortable writing VBA procedures, working with loops and conditionals, and manipulating worksheet ranges. If you need a refresher, VBA Variables, Data Types, and Control Structures: Building Robust Excel Automation and Working with Ranges, Cells, and Worksheets in VBA for Data Professionals will get you up to speed. You should also understand basic error handling — we'll use it extensively here.

---

Understanding the MSXML2 Library

Before writing a single line of parser code, you need to understand the tool you're building with: the Microsoft XML Core Services library, known as MSXML2. This is a COM library already installed on every Windows machine that has Office. It exposes a full XML DOM (Document Object Model) that lets you load, navigate, query, and manipulate XML as an object hierarchy rather than raw text.

The key objects you'll use are:

Object	What It Does
MSXML2.DOMDocument60	The top-level XML document
IXMLDOMElement	A single XML element (tag)
IXMLDOMNodeList	A collection of nodes returned by a query
IXMLDOMAttribute	An attribute on an element
IXMLDOMNamedNodeMap	A collection of attributes

You have two ways to reference this library: early binding (add a reference via Tools > References > "Microsoft XML, v6.0") or late binding (declare As Object and use CreateObject). For production code, early binding is strongly preferred — you get IntelliSense, compile-time error checking, and better performance.

To add the reference: open the VBA editor (Alt+F11), go to Tools > References, scroll down to "Microsoft XML, v6.0," and check the box.

Here's a minimal example that loads an XML string and reads the root element's name:

Sub TestMSXML()
    Dim xml As MSXML2.DOMDocument60
    Set xml = New MSXML2.DOMDocument60
    
    xml.async = False
    xml.validateOnParse = False
    
    Dim loaded As Boolean
    loaded = xml.LoadXML("<orders><order id='1001'><status>Pending</status></order></orders>")
    
    If loaded Then
        Debug.Print xml.documentElement.nodeName  ' outputs: orders
        Debug.Print xml.documentElement.childNodes(0).Attributes.getNamedItem("id").Text  ' outputs: 1001
    Else
        Debug.Print xml.parseError.reason
    End If
End Sub

Notice xml.async = False — this is critical. Without it, the DOM loads asynchronously and your code races ahead before the document is ready. Always set this before loading.

---

Building the XML Import Engine

The Source XML

Let's establish the realistic dataset we'll work with throughout this lesson. This is a simplified purchase order XML file, the kind you'd receive from an ERP like SAP or Oracle:

<?xml version="1.0" encoding="UTF-8"?>
<PurchaseOrders xmlns="http://schemas.acme.com/po/2024">
    <PurchaseOrder id="PO-20240101" status="Approved" supplier="SUP-441">
        <OrderDate>2024-01-15</OrderDate>
        <DeliveryDate>2024-01-30</DeliveryDate>
        <LineItems>
            <LineItem seq="1">
                <SKU>WH-4421-BLK</SKU>
                <Description>Widget Housing Black 4421</Description>
                <Quantity>500</Quantity>
                <UnitPrice>12.50</UnitPrice>
            </LineItem>
            <LineItem seq="2">
                <SKU>WH-4421-GRY</SKU>
                <Description>Widget Housing Grey 4421</Description>
                <Quantity>250</Quantity>
                <UnitPrice>12.50</UnitPrice>
            </LineItem>
        </LineItems>
    </PurchaseOrder>
    <PurchaseOrder id="PO-20240102" status="Pending" supplier="SUP-882">
        <OrderDate>2024-01-16</OrderDate>
        <DeliveryDate>2024-02-05</DeliveryDate>
        <LineItems>
            <LineItem seq="1">
                <SKU>MB-1100-X</SKU>
                <Description>Mounting Bracket 1100 XL</Description>
                <Quantity>1000</Quantity>
                <UnitPrice>4.75</UnitPrice>
            </LineItem>
        </LineItems>
    </PurchaseOrder>
</PurchaseOrders>

Save this as PurchaseOrders.xml somewhere on your machine. Notice the xmlns namespace declaration — this will require special handling, which we'll address shortly.

Setting Up the Import Framework

The import engine has three responsibilities: load the file, navigate the node tree, and write data to the worksheet. We'll build each piece separately and then assemble them.

Option Explicit

' ============================================================
' MODULE: XMLImportEngine
' Purpose: Import structured XML data into Excel worksheets
' ============================================================

Private Const NS_PREFIX As String = "po"
Private Const NS_URI As String = "http://schemas.acme.com/po/2024"

' ---- Public Entry Point ----

Public Sub ImportPurchaseOrders()
    Dim strFilePath As String
    strFilePath = GetXMLFilePath()
    If strFilePath = "" Then Exit Sub
    
    Dim xml As MSXML2.DOMDocument60
    Set xml = LoadXMLDocument(strFilePath)
    If xml Is Nothing Then Exit Sub
    
    Dim wsData As Worksheet
    Set wsData = GetOrCreateSheet("PO_Import")
    
    Call WriteHeaders(wsData)
    Call ParseAndWriteOrders(xml, wsData)
    Call FormatImportSheet(wsData)
    
    MsgBox "Import complete. " & (wsData.UsedRange.Rows.Count - 1) & " line items imported.", vbInformation
End Sub

' ---- File Selection ----

Private Function GetXMLFilePath() As String
    Dim dlg As FileDialog
    Set dlg = Application.FileDialog(msoFileDialogFilePicker)
    dlg.Title = "Select Purchase Order XML File"
    dlg.Filters.Clear
    dlg.Filters.Add "XML Files", "*.xml"
    dlg.AllowMultiSelect = False
    
    If dlg.Show = -1 Then
        GetXMLFilePath = dlg.SelectedItems(1)
    Else
        GetXMLFilePath = ""
    End If
End Function

Loading and Namespace Registration

This is where most people get tripped up. When your XML has a default namespace (xmlns="..."), XPath queries with no prefix match nothing — even though the elements look like they should match. You have to register a prefix and use it in every XPath expression.

Private Function LoadXMLDocument(strPath As String) As MSXML2.DOMDocument60
    Dim xml As MSXML2.DOMDocument60
    Set xml = New MSXML2.DOMDocument60
    
    xml.async = False
    xml.validateOnParse = False
    xml.preserveWhiteSpace = False
    
    If Not xml.Load(strPath) Then
        Dim errMsg As String
        errMsg = "XML parse error on line " & xml.parseError.line & ":" & vbCrLf & _
                 xml.parseError.reason
        MsgBox errMsg, vbCritical, "XML Load Failed"
        Set LoadXMLDocument = Nothing
        Exit Function
    End If
    
    ' Register namespace prefix for XPath queries
    xml.setProperty "SelectionLanguage", "XPath"
    xml.setProperty "SelectionNamespaces", _
        "xmlns:" & NS_PREFIX & "='" & NS_URI & "'"
    
    Set LoadXMLDocument = xml
End Function

The setProperty "SelectionNamespaces" call registers your prefix-to-URI mapping. After this, you can write XPath like "//po:PurchaseOrder" and it resolves correctly against the document's default namespace.

Parsing the Node Tree

Now the interesting part. We'll walk the order nodes, and for each order, walk its line items — a classic nested XML structure that flattens into one row per line item in the spreadsheet.

Private Sub ParseAndWriteOrders(xml As MSXML2.DOMDocument60, ws As Worksheet)
    Dim orderNodes As MSXML2.IXMLDOMNodeList
    Set orderNodes = xml.selectNodes("//po:PurchaseOrder")
    
    If orderNodes Is Nothing Or orderNodes.Length = 0 Then
        MsgBox "No PurchaseOrder elements found. Check namespace configuration.", vbWarning
        Exit Sub
    End If
    
    Dim outputRow As Long
    outputRow = 2  ' Row 1 is headers
    
    Dim orderNode As MSXML2.IXMLDOMElement
    Dim lineNode As MSXML2.IXMLDOMElement
    Dim lineNodes As MSXML2.IXMLDOMNodeList
    
    For Each orderNode In orderNodes
        ' Extract header-level attributes
        Dim poID As String
        Dim poStatus As String
        Dim supplierCode As String
        Dim orderDate As String
        Dim deliveryDate As String
        
        poID = GetAttributeValue(orderNode, "id")
        poStatus = GetAttributeValue(orderNode, "status")
        supplierCode = GetAttributeValue(orderNode, "supplier")
        orderDate = GetChildText(orderNode, "po:OrderDate")
        deliveryDate = GetChildText(orderNode, "po:DeliveryDate")
        
        ' Iterate line items
        Set lineNodes = orderNode.selectNodes("po:LineItems/po:LineItem")
        
        For Each lineNode In lineNodes
            Dim seqNum As String
            Dim sku As String
            Dim description As String
            Dim qty As Long
            Dim unitPrice As Double
            
            seqNum = GetAttributeValue(lineNode, "seq")
            sku = GetChildText(lineNode, "po:SKU")
            description = GetChildText(lineNode, "po:Description")
            qty = CLng(GetChildText(lineNode, "po:Quantity"))
            unitPrice = CDbl(GetChildText(lineNode, "po:UnitPrice"))
            
            ' Write to worksheet
            With ws
                .Cells(outputRow, 1).Value = poID
                .Cells(outputRow, 2).Value = poStatus
                .Cells(outputRow, 3).Value = supplierCode
                .Cells(outputRow, 4).Value = CDate(orderDate)
                .Cells(outputRow, 5).Value = CDate(deliveryDate)
                .Cells(outputRow, 6).Value = CLng(seqNum)
                .Cells(outputRow, 7).Value = sku
                .Cells(outputRow, 8).Value = description
                .Cells(outputRow, 9).Value = qty
                .Cells(outputRow, 10).Value = unitPrice
                .Cells(outputRow, 11).Value = qty * unitPrice  ' Line total
            End With
            
            outputRow = outputRow + 1
        Next lineNode
    Next orderNode
End Sub

Helper Functions That Earn Their Keep

These two helpers — GetAttributeValue and GetChildText — prevent your main loop from becoming a wall of error-prone inline XML navigation. They also handle the case where a node doesn't exist (which will happen with real data).

Private Function GetAttributeValue(node As MSXML2.IXMLDOMElement, _
                                    attrName As String) As String
    Dim attr As MSXML2.IXMLDOMAttribute
    Set attr = node.Attributes.getNamedItem(attrName)
    If Not attr Is Nothing Then
        GetAttributeValue = attr.Text
    Else
        GetAttributeValue = ""
    End If
End Function

Private Function GetChildText(parentNode As MSXML2.IXMLDOMElement, _
                               xpathExpr As String) As String
    Dim childNode As MSXML2.IXMLDOMNode
    Set childNode = parentNode.selectSingleNode(xpathExpr)
    If Not childNode Is Nothing Then
        GetChildText = childNode.Text
    Else
        GetChildText = ""
    End If
End Function

Writing Headers and Formatting

Private Sub WriteHeaders(ws As Worksheet)
    ws.Cells.Clear
    
    Dim headers As Variant
    headers = Array("PO Number", "Status", "Supplier", "Order Date", _
                    "Delivery Date", "Line Seq", "SKU", "Description", _
                    "Quantity", "Unit Price", "Line Total")
    
    Dim i As Integer
    For i = 0 To UBound(headers)
        ws.Cells(1, i + 1).Value = headers(i)
    Next i
    
    With ws.Range("A1:K1")
        .Font.Bold = True
        .Interior.Color = RGB(31, 73, 125)
        .Font.Color = RGB(255, 255, 255)
    End With
End Sub

Private Function GetOrCreateSheet(sheetName As String) As Worksheet
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets(sheetName)
    On Error GoTo 0
    
    If ws Is Nothing Then
        Set ws = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        ws.Name = sheetName
    End If
    
    Set GetOrCreateSheet = ws
End Function

Private Sub FormatImportSheet(ws As Worksheet)
    ' Format date columns
    ws.Columns("D:E").NumberFormat = "yyyy-mm-dd"
    ' Format currency columns
    ws.Columns("J:K").NumberFormat = "$#,##0.00"
    ' Autofit all columns
    ws.UsedRange.Columns.AutoFit
    ' Convert to Table for easier analysis
    Dim tbl As ListObject
    On Error Resume Next
    Set tbl = ws.ListObjects.Add(xlSrcRange, ws.UsedRange, , xlYes)
    If Not tbl Is Nothing Then tbl.Name = "tblPurchaseOrders"
    On Error GoTo 0
End Sub

---

Building the XML Export Engine

Now we go in the other direction: take data from a worksheet and write it out as valid, schema-compliant XML. This is where many teams get stuck — they try to build XML by string concatenation, which is brittle, slow, and breaks the moment someone puts a < or & in a description field.

The right approach is to build the XML document programmatically using the DOM, then serialize it. The DOM handles all escaping automatically.

' ============================================================
' MODULE: XMLExportEngine
' Purpose: Export worksheet data to structured XML
' ============================================================

Public Sub ExportPurchaseOrdersToXML()
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets("PO_Import")
    On Error GoTo 0
    
    If ws Is Nothing Then
        MsgBox "Sheet 'PO_Import' not found. Run the import first.", vbExclamation
        Exit Sub
    End If
    
    Dim tbl As ListObject
    On Error Resume Next
    Set tbl = ws.ListObjects("tblPurchaseOrders")
    On Error GoTo 0
    
    If tbl Is Nothing Then
        MsgBox "Table 'tblPurchaseOrders' not found.", vbExclamation
        Exit Sub
    End If
    
    ' Build the XML document
    Dim xml As MSXML2.DOMDocument60
    Set xml = BuildXMLFromTable(tbl)
    
    ' Save to file
    Dim savePath As String
    savePath = GetSaveFilePath()
    If savePath = "" Then Exit Sub
    
    xml.Save savePath
    MsgBox "Exported successfully to:" & vbCrLf & savePath, vbInformation
End Sub

Private Function BuildXMLFromTable(tbl As ListObject) As MSXML2.DOMDocument60
    Dim xml As MSXML2.DOMDocument60
    Set xml = New MSXML2.DOMDocument60
    
    ' Add XML declaration
    Dim xmlDecl As MSXML2.IXMLDOMProcessingInstruction
    Set xmlDecl = xml.createProcessingInstruction("xml", "version=""1.0"" encoding=""UTF-8""")
    xml.appendChild xmlDecl
    
    ' Create root element with namespace
    Dim root As MSXML2.IXMLDOMElement
    Set root = xml.createNode(NODE_ELEMENT, "PurchaseOrders", NS_URI)
    xml.appendChild root
    
    ' Group rows by PO Number — we need a Dictionary for this
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    
    Dim dataBody As Range
    Set dataBody = tbl.DataBodyRange
    
    If dataBody Is Nothing Then
        Set BuildXMLFromTable = xml
        Exit Function
    End If
    
    ' Map column positions by header name for resilience
    Dim colMap As Object
    Set colMap = BuildColumnMap(tbl)
    
    Dim r As Long
    For r = 1 To dataBody.Rows.Count
        Dim poNum As String
        poNum = CStr(dataBody.Cells(r, colMap("PO Number")).Value)
        If Not dict.Exists(poNum) Then
            dict.Add poNum, r  ' Track first row of each PO
        End If
    Next r
    
    ' Now build one PurchaseOrder element per unique PO
    Dim poKey As Variant
    For Each poKey In dict.Keys
        Dim firstRow As Long
        firstRow = dict(poKey)
        
        ' Create PurchaseOrder element
        Dim poElem As MSXML2.IXMLDOMElement
        Set poElem = xml.createNode(NODE_ELEMENT, "PurchaseOrder", NS_URI)
        
        ' Set attributes from first row of this PO
        poElem.setAttribute "id", CStr(dataBody.Cells(firstRow, colMap("PO Number")).Value)
        poElem.setAttribute "status", CStr(dataBody.Cells(firstRow, colMap("Status")).Value)
        poElem.setAttribute "supplier", CStr(dataBody.Cells(firstRow, colMap("Supplier")).Value)
        
        ' Add child elements
        AppendTextElement xml, poElem, "OrderDate", _
            Format(dataBody.Cells(firstRow, colMap("Order Date")).Value, "yyyy-mm-dd"), NS_URI
        AppendTextElement xml, poElem, "DeliveryDate", _
            Format(dataBody.Cells(firstRow, colMap("Delivery Date")).Value, "yyyy-mm-dd"), NS_URI
        
        ' Create LineItems container
        Dim lineItemsElem As MSXML2.IXMLDOMElement
        Set lineItemsElem = xml.createNode(NODE_ELEMENT, "LineItems", NS_URI)
        
        ' Add all line items belonging to this PO
        For r = 1 To dataBody.Rows.Count
            If CStr(dataBody.Cells(r, colMap("PO Number")).Value) = CStr(poKey) Then
                Dim liElem As MSXML2.IXMLDOMElement
                Set liElem = xml.createNode(NODE_ELEMENT, "LineItem", NS_URI)
                liElem.setAttribute "seq", CStr(dataBody.Cells(r, colMap("Line Seq")).Value)
                
                AppendTextElement xml, liElem, "SKU", _
                    CStr(dataBody.Cells(r, colMap("SKU")).Value), NS_URI
                AppendTextElement xml, liElem, "Description", _
                    CStr(dataBody.Cells(r, colMap("Description")).Value), NS_URI
                AppendTextElement xml, liElem, "Quantity", _
                    CStr(CLng(dataBody.Cells(r, colMap("Quantity")).Value)), NS_URI
                AppendTextElement xml, liElem, "UnitPrice", _
                    Format(dataBody.Cells(r, colMap("Unit Price")).Value, "0.00"), NS_URI
                
                lineItemsElem.appendChild liElem
            End If
        Next r
        
        poElem.appendChild lineItemsElem
        root.appendChild poElem
    Next poKey
    
    Set BuildXMLFromTable = xml
End Function

' Helper: Append a named text element to a parent
Private Sub AppendTextElement(xml As MSXML2.DOMDocument60, _
                               parent As MSXML2.IXMLDOMElement, _
                               elemName As String, _
                               elemValue As String, _
                               nsURI As String)
    Dim elem As MSXML2.IXMLDOMElement
    Set elem = xml.createNode(NODE_ELEMENT, elemName, nsURI)
    elem.Text = elemValue
    parent.appendChild elem
End Sub

' Helper: Build a column name -> column index map from a ListObject
Private Function BuildColumnMap(tbl As ListObject) As Object
    Dim map As Object
    Set map = CreateObject("Scripting.Dictionary")
    
    Dim col As ListColumn
    For Each col In tbl.ListColumns
        map(col.Name) = col.Index
    Next col
    
    Set BuildColumnMap = map
End Function

Private Function GetSaveFilePath() As String
    Dim dlg As FileDialog
    Set dlg = Application.FileDialog(msoFileDialogSaveAs)
    dlg.Title = "Save XML Export"
    dlg.InitialFileName = "PurchaseOrders_Export_" & Format(Now, "yyyymmdd_hhmmss") & ".xml"
    
    If dlg.Show = -1 Then
        GetSaveFilePath = dlg.SelectedItems(1)
    Else
        GetSaveFilePath = ""
    End If
End Function

---

Building the Sync Engine: Detect and Flag Changes

The most powerful piece of this engine isn't pure import or pure export — it's the comparison layer. When you get a new XML file from your supplier every morning, you don't just want to overwrite everything. You want to know: what changed? Which orders moved from Pending to Approved? Which line item quantities were revised?

This sync engine uses a Scripting.Dictionary to hold the previous state, then compares each incoming record against it.

' ============================================================
' MODULE: XMLSyncEngine
' Purpose: Compare incoming XML against existing data and flag changes
' ============================================================

Public Sub SyncPurchaseOrders()
    ' Step 1: Capture current state as a dictionary keyed by PO+Seq
    Dim currentState As Object
    Set currentState = SnapshotCurrentData()
    
    ' Step 2: Load the new XML file
    Dim strFilePath As String
    strFilePath = GetXMLFilePath()
    If strFilePath = "" Then Exit Sub
    
    Dim xml As MSXML2.DOMDocument60
    Set xml = LoadXMLDocument(strFilePath)
    If xml Is Nothing Then Exit Sub
    
    ' Step 3: Set up a "Changes" log sheet
    Dim wsLog As Worksheet
    Set wsLog = GetOrCreateSheet("Sync_Log")
    Call WriteChangeLogHeaders(wsLog)
    
    ' Step 4: Walk the new XML, compare and log changes
    Dim orderNodes As MSXML2.IXMLDOMNodeList
    Set orderNodes = xml.selectNodes("//po:PurchaseOrder")
    
    Dim logRow As Long
    logRow = 2
    
    Dim orderNode As MSXML2.IXMLDOMElement
    For Each orderNode In orderNodes
        Dim poID As String
        Dim newStatus As String
        poID = GetAttributeValue(orderNode, "id")
        newStatus = GetAttributeValue(orderNode, "status")
        
        Dim lineNodes As MSXML2.IXMLDOMNodeList
        Set lineNodes = orderNode.selectNodes("po:LineItems/po:LineItem")
        
        Dim lineNode As MSXML2.IXMLDOMElement
        For Each lineNode In lineNodes
            Dim seqNum As String
            Dim newQty As String
            Dim newPrice As String
            seqNum = GetAttributeValue(lineNode, "seq")
            newQty = GetChildText(lineNode, "po:Quantity")
            newPrice = GetChildText(lineNode, "po:UnitPrice")
            
            Dim recordKey As String
            recordKey = poID & "|" & seqNum
            
            If currentState.Exists(recordKey) Then
                ' Record exists — check for changes
                Dim oldValues As Variant
                oldValues = currentState(recordKey)
                ' oldValues: (0)=Status, (1)=Qty, (2)=Price
                
                If oldValues(0) <> newStatus Then
                    LogChange wsLog, logRow, recordKey, "Status", oldValues(0), newStatus, "CHANGED"
                    logRow = logRow + 1
                End If
                If CStr(oldValues(1)) <> newQty Then
                    LogChange wsLog, logRow, recordKey, "Quantity", CStr(oldValues(1)), newQty, "CHANGED"
                    logRow = logRow + 1
                End If
                If Format(CDbl(oldValues(2)), "0.00") <> Format(CDbl(newPrice), "0.00") Then
                    LogChange wsLog, logRow, recordKey, "UnitPrice", _
                        Format(CDbl(oldValues(2)), "0.00"), Format(CDbl(newPrice), "0.00"), "CHANGED"
                    logRow = logRow + 1
                End If
            Else
                ' New record
                LogChange wsLog, logRow, recordKey, "ALL", "", _
                    newStatus & " | Qty:" & newQty & " | $" & newPrice, "NEW"
                logRow = logRow + 1
            End If
        Next lineNode
    Next orderNode
    
    ' Step 5: Re-import the full dataset to refresh the main sheet
    Call ImportPurchaseOrders
    
    Dim changeCount As Long
    changeCount = logRow - 2
    MsgBox "Sync complete. " & changeCount & " change(s) logged in 'Sync_Log' sheet.", vbInformation
End Sub

Private Function SnapshotCurrentData() As Object
    Dim snap As Object
    Set snap = CreateObject("Scripting.Dictionary")
    
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets("PO_Import")
    On Error GoTo 0
    If ws Is Nothing Then
        Set SnapshotCurrentData = snap
        Exit Function
    End If
    
    Dim tbl As ListObject
    On Error Resume Next
    Set tbl = ws.ListObjects("tblPurchaseOrders")
    On Error GoTo 0
    If tbl Is Nothing Then
        Set SnapshotCurrentData = snap
        Exit Function
    End If
    
    Dim colMap As Object
    Set colMap = BuildColumnMap(tbl)
    
    Dim db As Range
    Set db = tbl.DataBodyRange
    If db Is Nothing Then
        Set SnapshotCurrentData = snap
        Exit Function
    End If
    
    Dim r As Long
    For r = 1 To db.Rows.Count
        Dim key As String
        key = CStr(db.Cells(r, colMap("PO Number")).Value) & "|" & _
              CStr(db.Cells(r, colMap("Line Seq")).Value)
        
        Dim vals(2) As Variant
        vals(0) = CStr(db.Cells(r, colMap("Status")).Value)
        vals(1) = db.Cells(r, colMap("Quantity")).Value
        vals(2) = db.Cells(r, colMap("Unit Price")).Value
        
        If Not snap.Exists(key) Then
            snap.Add key, vals
        End If
    Next r
    
    Set SnapshotCurrentData = snap
End Function

Private Sub WriteChangeLogHeaders(ws As Worksheet)
    ws.Cells.Clear
    Dim headers As Variant
    headers = Array("Record Key", "Field", "Old Value", "New Value", "Change Type", "Logged At")
    Dim i As Integer
    For i = 0 To UBound(headers)
        ws.Cells(1, i + 1).Value = headers(i)
    Next i
    ws.Range("A1:F1").Font.Bold = True
    ws.Range("A1:F1").Interior.Color = RGB(197, 90, 17)
    ws.Range("A1:F1").Font.Color = RGB(255, 255, 255)
End Sub

Private Sub LogChange(ws As Worksheet, logRow As Long, recordKey As String, _
                       fieldName As String, oldVal As String, newVal As String, _
                       changeType As String)
    ws.Cells(logRow, 1).Value = recordKey
    ws.Cells(logRow, 2).Value = fieldName
    ws.Cells(logRow, 3).Value = oldVal
    ws.Cells(logRow, 4).Value = newVal
    ws.Cells(logRow, 5).Value = changeType
    ws.Cells(logRow, 6).Value = Now
    
    ' Color-code change type
    If changeType = "NEW" Then
        ws.Cells(logRow, 5).Interior.Color = RGB(198, 239, 206)  ' Green
    ElseIf changeType = "CHANGED" Then
        ws.Cells(logRow, 5).Interior.Color = RGB(255, 235, 156)  ' Yellow
    ElseIf changeType = "REMOVED" Then
        ws.Cells(logRow, 5).Interior.Color = RGB(255, 199, 206)  ' Red
    End If
End Sub

This sync engine pairs naturally with VBA Arrays and Collections for Efficient Data Processing — the Dictionary is doing the heavy lifting of keyed lookups that would otherwise require nested loops. For large datasets, this pattern is dramatically faster than scanning ranges row by row.

---

Performance Considerations for Large XML Files

When your XML files get large — 50,000+ nodes — naïve implementations start choking. A few strategies keep things fast:

Batch worksheet writes. Writing cell by cell in a loop is slow. For very large datasets, load all values into a 2D Variant array first, then write the whole array to the sheet in a single Range.Value assignment. This is covered extensively in Excel Performance Optimization: Fix Slow Workbooks and Scale Your Analysis.

Suppress screen updates and calculations during import:

Private Sub OptimizeForImport()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
End Sub

Private Sub RestoreAfterImport()
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
End Sub

Always call these as a matched pair, ideally wrapped in error handling so they're restored even if the import fails.

Consider SAX for read-only large file parsing. The DOM loads the entire document into memory. For files over ~10MB, consider using MSXML2.SAXXMLReader60 (SAX parser), which processes the file as a stream without holding it all in RAM. SAX is more complex to implement but handles gigabyte-scale XML that would crash a DOM approach.

---

Hands-On Exercise

Build a complete XML round-trip for an inventory management scenario:

1. Create a worksheet called Inventory with these columns: Item ID, Category, Description, Stock Level, Reorder Point, Unit Cost.

2. Populate it with at least 10 realistic rows (use product names, part numbers, categories like "Hardware," "Electronics," etc.)

3. Write an ExportInventoryToXML procedure that:

   • Groups items by Category
   • Creates a <Category name="..."> parent element for each group
   • Adds <Item id="..."> children with child elements for each field
   • Flags items where Stock Level < Reorder Point with a status="low_stock" attribute
   • Saves the result as Inventory_[timestamp].xml

4. Write an ImportInventoryXML procedure that reads the exported file back in, using selectNodes with a registered namespace (create a fake one: http://schemas.yourcompany.com/inventory/1.0), and writes the data to a new sheet called Inventory_Import.

5. Verify round-trip fidelity: After importing, check that the low-stock flags came through correctly and that numeric values (stock levels, costs) were preserved — not just stored as strings.

---

Common Mistakes & Troubleshooting

"XPath query returns nothing" Nine times out of ten, this is the namespace issue. If your XML has a default xmlns="..." and you're querying //PurchaseOrder, you'll get zero results. Register a prefix with setProperty "SelectionNamespaces" and prefix every element name in your XPath.

"Type mismatch when converting node text" XML has no native types — everything is a string. If a <Quantity> element contains "500 units" instead of "500", CLng() will crash. Wrap numeric conversions in error handling or IsNumeric() checks.

"XML export produces invalid characters" This happens when you concatenate strings instead of using the DOM. Switch to DOM-based construction. If you're stuck with string approach for legacy reasons, at minimum implement this escaping helper:

Private Function EscapeXML(s As String) As String
    s = Replace(s, "&", "&")
    s = Replace(s, "<", "&lt;")
    s = Replace(s, ">", "&gt;")
    s = Replace(s, """", "&quot;")
    s = Replace(s, "'", "&apos;")
    EscapeXML = s
End Function

"Document loads but some nodes are missing" Check xml.preserveWhiteSpace. If it's True, whitespace text nodes appear between elements and throw off your index-based navigation. Set it to False before loading.

"Export XML doesn't include namespace declarations" When using createNode(NODE_ELEMENT, name, nsURI), the namespace URI is attached to the element, but the xmlns attribute won't appear until the document is serialized. This is correct behavior. If you need explicit xmlns declarations on child elements (some consuming systems require this), set the attribute manually: elem.setAttribute "xmlns", NS_URI.

"Sync reports false positives on numeric fields" Floating-point comparison is unreliable. Instead of comparing doubles directly, compare formatted strings: Format(val1, "0.00") <> Format(val2, "0.00"). This matches to two decimal places and avoids floating-point noise.

---

Extending the Engine

Once this foundation is solid, you can take it in several directions:

• Combine with REST API calls to POST your generated XML directly to an endpoint. The techniques in Integrating Excel VBA with REST APIs: Fetch, Parse, and Automate Live Data Workflows cover the HTTP request layer — your XML engine handles the payload construction.

• Schedule automated syncs without user intervention. Once the import logic is encapsulated in a procedure, you can trigger it on a schedule using the approaches described in Scheduling and Running VBA Macros Automatically with Windows Task Scheduler and Application.OnTime.

• Add a UserForm front-end so non-technical colleagues can trigger imports, select schemas, and review sync logs without touching VBA. Building UserForms for Custom Data Entry Interfaces has the foundation you'd need.

• Wrap the engine in an Add-In for deployment across your team, so anyone can access the XML import/export commands from the Ribbon without the source code being visible or editable.

---

Summary & Next Steps

You've built a complete XML engine from the ground up. The import side uses MSXML2.DOMDocument60 with proper namespace registration to reliably navigate complex, nested XML into a structured flat table. The export side uses DOM construction — not string concatenation — to produce valid, properly-escaped XML from worksheet data. And the sync engine uses a Dictionary-based snapshot comparison to detect exactly what changed between XML deliveries.

The patterns here — load, register namespace, selectNodes with prefixed XPath, null-check every node, write via DOM — will serve you on virtually any XML format you encounter, not just purchase orders. SOAP responses, healthcare HL7 payloads, financial FIXML messages — they're all variations on the same structure.

Where to go next:

• Explore XSLT transformation using MSXML2.XSLTemplate if you need to restructure XML before importing
• Investigate schema validation with XSD files to reject malformed XML before it touches your worksheet
• Study how this engine could feed into a multi-workbook consolidation pipeline where each site exports XML that gets centrally merged
• Consider connecting this XML data directly to Power Pivot for analysis once it's in your worksheet

The bottleneck in most enterprise Excel workflows isn't analysis — it's getting structured data in and out reliably. You've just eliminated that bottleneck.