Excel VBA: Open XML Files, Read Data, and Handle Errors

VBA & macros · Updated November 19, 2026

Yes. In Excel VBA on Windows, use Workbooks.OpenXML to open an XML file in Excel’s workbook format, or use the MSXML parser to read elements from a general-purpose XML document and place their values in a worksheet. The right method depends on whether you want Excel to interpret the file as a workbook or your code to extract particular pieces of data.

What “open an XML file” means

XML is a text format for structured data, not one single Excel workbook format. A document might contain a list of products, an invoice, or data exported by another application. Its tags describe the structure; for example, an <order> element might contain a customer name and a total. Excel can import some XML documents into tables, but it does not automatically know what every tag means or how every document should be arranged in cells.

There is also an important distinction between an ordinary file ending in .xml and an Excel workbook ending in .xlsx. An .xlsx file is a packaged workbook containing multiple related XML files and other components. It is not simply one XML document, so parsing a workbook package as if it were a plain data file is the wrong approach. Open an .xlsx with Excel’s workbook methods, not an XML DOM parser.

Choose the right VBA method

Use Workbooks.OpenXML when you want Excel to interpret an XML file and create or populate a workbook. This is convenient for files that Excel recognizes as tabular or spreadsheet-style XML. Excel decides how to represent the document, so it is not a guarantee that every arbitrary XML structure will become a useful worksheet.

Use MSXML when your macro needs specific fields from a general XML document—for example, each order’s ID, customer, and total. The parser gives VBA access to nodes and attributes, after which your code can write selected values into cells. This approach is more controllable, but you need to know the document’s structure and handle missing or unexpected elements.

  • Choose Workbooks.OpenXML to let Excel import a supported XML file as a workbook.
  • Choose MSXML to extract defined elements from XML and arrange them yourself.
  • Choose a different import route if the source is actually an .xlsx, a very large feed, or an XML format with complex transformations.

Open XML as an Excel workbook

In Excel for Microsoft 365 on Windows, VBA’s Workbooks.OpenXML method opens an XML file and lets you choose how Excel loads it. The example below asks you to select a file and requests an import to a list. The exact result still depends on the file’s structure and whether Excel can interpret it as tabular XML.

Sub OpenXmlAsWorkbook()
    Dim filePath As Variant
    Dim xmlBook As Workbook

    ' Ask the user to choose an XML file.
    filePath = Application.GetOpenFilename( _
        "XML Files (*.xml),*.xml", , "Select an XML file")

    If filePath = False Then Exit Sub

    ' Ask Excel to import the XML data as a list.
    Set xmlBook = Workbooks.OpenXML( _
        Filename:=CStr(filePath), _
        LoadOption:=xlXmlLoadImportToList)
End Sub

Save a copy of the workbook before running any macro that changes data: macro actions cannot be undone with Ctrl+Z. The example opens a workbook rather than writing into the active sheet. If you want the imported data in a particular existing workbook, add explicit copy or transfer steps and check the destination before saving.

You can also open an XML file through Excel’s interface with File > Open, then inspect the import choices Excel offers. The available choices and result depend on the file. That manual test is useful before automating: if Excel cannot make a sensible table from the file by hand, calling OpenXML usually will not turn an arbitrary nested document into the table you intended.

Read XML nodes with MSXML

For selective extraction on Windows, MSXML provides a document object model that VBA can query with XPath. This example expects a document shaped like <orders><order id="A101"><customer>Northwind</customer><total>125.50</total></order></orders>. It writes one row per order to a worksheet named Sheet1. Change the path, sheet, and XPath expressions to match the actual source file.

Save a copy of the workbook before running this macro. It clears existing cell contents on the destination sheet, and that change cannot be reversed with Ctrl+Z.

Sub ImportOrderXml()
    Dim doc As Object
    Dim records As Object
    Dim record As Object
    Dim ws As Worksheet
    Dim filePath As String
    Dim i As Long

    filePath = "C:\Data\orders.xml"
    Set doc = CreateObject("MSXML2.DOMDocument.6.0")
    doc.async = False
    doc.validateOnParse = False
    doc.resolveExternals = False

    If Not doc.Load(filePath) Then
        MsgBox "XML parse error: " & doc.parseError.reason, vbExclamation
        Exit Sub
    End If

    doc.setProperty "SelectionLanguage", "XPath"
    Set records = doc.selectNodes("/orders/order")
    Set ws = ThisWorkbook.Worksheets("Sheet1")

    ws.Cells.ClearContents
    ws.Range("A1:C1").Value = Array("Order ID", "Customer", "Total")

    For i = 0 To records.Length - 1
        Set record = records.Item(i)
        ws.Cells(i + 2, 1).Value = record.getAttribute("id")
        ws.Cells(i + 2, 2).Value = record.selectSingleNode("customer").Text
        ws.Cells(i + 2, 3).Value = record.selectSingleNode("total").Text
    Next i
End Sub

This uses late binding, so you do not need to add an MSXML reference under Tools > References in the VBA editor. The versioned ProgID MSXML2.DOMDocument.6.0 requests MSXML 6.0 on Windows. The code assumes every order has the named child elements; if the input may omit them, check whether each result of selectSingleNode is Nothing before reading its Text property. Otherwise, a missing field can stop the macro with a runtime error.

How the worksheet import works

The XPath /orders/order selects each direct order child of the document’s root orders element. Within each selected order, customer and total select child elements, while getAttribute("id") reads the order’s ID attribute. The loop writes those three values to columns A, B, and C, beginning on row 2 after the headings.

The sample writes the total as text taken from the XML node. If you need arithmetic, convert and validate the value deliberately rather than assuming every source string is a valid number. XML may use decimal points, localized formats, empty values, or unexpected text. Similarly, production code should usually write to a designated range or a new sheet instead of clearing a sheet that may contain unrelated work.

For a large file, writing one cell at a time can be slower than building a two-dimensional VBA array and assigning it to a range in one operation. For modest files, the direct loop is easier to understand. Either way, decide how to handle duplicate records, blank fields, and source values that look like dates or numbers but should remain text.

Namespaces and differences in XML structure

A frequent reason an XPath query returns zero nodes is an XML namespace. A document can look like it has an orders root but declare a default namespace, such as xmlns="urn:example:orders". In that case, the unprefixed XPath /orders/order does not match those elements in MSXML. Set the parser’s SelectionNamespaces property to bind a prefix to the namespace URI, then use that prefix in the XPath, such as /x:orders/x:order. The prefix you choose in the query can differ from the prefix, or lack of one, in the source; the URI must match.

XML can also be nested in ways that do not map neatly to a flat table. A customer might contain several addresses, or an order might contain multiple line items. Decide whether to create one row per order, one row per line item, or separate related tables. Flattening nested data without a plan can duplicate parent details or lose relationships between records.

Common problems and safe handling

  • Parse errors: The file may be malformed, incomplete, or not actually XML. Check doc.parseError.reason and verify the file opens as plain text in a suitable editor.
  • No matching nodes: Check the root path, capitalization, nesting, and namespaces. XML element names are case-sensitive.
  • File not found: Confirm the full path, permissions, and that the file is available locally. A mapped drive may not be available to an automated process running under another account.
  • Missing values: Check for absent elements before reading their text, and decide whether a blank cell, a default value, or an error is appropriate.
  • Unsafe or unexpected input: The example disables external resolution. Keep that safeguard for untrusted files, validate the source, and avoid enabling external resources unless there is a specific, understood need.

An XML document does not run Excel macros merely because it is opened, but imported content can still be misleading or harmful in other ways—for example, by containing deceptive text or links. Treat files from unknown sources cautiously. Avoid overwriting the original XML, and make the macro’s output destination explicit.

Windows, Mac, and alternatives

The examples target Excel for Microsoft 365 on Windows. MSXML is a Windows COM component, so code that creates MSXML2.DOMDocument.6.0 is not portable VBA for Mac. Excel’s XML import and mapping capabilities also differ by platform and build; test the exact workflow in the version you plan to use rather than assuming a Windows macro will work unchanged on Mac. Excel for the web does not run VBA macros.

If the same import must work across platforms, consider an Excel-supported data import feature available in your environment, a controlled service that converts the XML to a simpler format such as CSV, or a parser designed for cross-platform VBA. Power Query may be suitable for some XML sources, but connector availability and refresh behavior depend on the Excel platform and source. For a one-time import, Excel’s own open or import interface may be simpler than writing a macro.

Frequently Asked Questions

Can VBA open an XML file with Workbooks.Open?
Workbooks.Open is primarily for opening workbooks. For XML input, use Workbooks.OpenXML when Excel should interpret the file as a workbook, or parse the document with MSXML when you need selected values.

How do I import XML into an existing worksheet?
Parse the document with MSXML, select the nodes you need, and write their values to a specified worksheet range, as in the example. Choose a safe destination and avoid clearing existing content unless that is intentional.

Why does my XML XPath return no results?
Check the element names, capitalization, nesting, and namespace declarations. A default namespace is a particularly common cause; bind its URI to a query prefix and include that prefix in the XPath.

Can Excel VBA read an XLSX file as XML?
An .xlsx is a ZIP-based package containing many XML parts, not a single XML data document. Open it as a workbook with Excel rather than sending the whole file to the MSXML document parser.

Will this macro work in Excel for Mac?
The MSXML COM object shown here is Windows-specific. Excel for Mac and Excel for the web have different VBA and XML support, so use a platform-appropriate import method or rewrite and verify the parsing approach for the target environment.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.