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.OpenXMLto 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.reasonand 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
- Excel: Find Where a Cell Is Used Across Sheets
- Excel Custom Number Format for Millions
- Excel Custom Number Format: Show Numbers in Thousands
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Sort Order in Excel: Rules and ExamplesIn Excel VBA, sort order is set with xlAscending or xlDescending; the sort key determines which column or…
- Excel UserForm: How to Create, Design, and Use FormsTo create an Excel UserForm in Microsoft 365 for Windows, press Alt+F11 to open the Visual Basic Editor,…
- VBA Dictionary Multiple Items per Key: Practical GuideYes. A VBA Scripting.Dictionary can hold multiple items for one key if you store a container—most often a…
- Excel VBA Clear Dictionary: Methods, Examples, and TipsTo empty a VBA Scripting.Dictionary, call its RemoveAll method: dict.RemoveAll. This removes every key and item while leaving…
- Access VBA Date Conversion: Parse, Format, and ValidateIn Access VBA, use CDate to convert a value VBA already recognizes as a date, DateSerial to build…
- Excel Not Responding After a Macro? What to DoIf Excel says “Not Responding” after you run a macro, first give it a moment and check whether…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.