How to Open an XML File in Excel [2 VBA Codes]

If you want to import your XML files inside Microsoft Excel, there are multiple ways to do that. But if you are a VBA freak, you might be looking for VBA codes to import your XML files in Excel. Well, here I’m going to discuss 2 effective VBA codes that you can use to open your XML file in Microsoft Excel. So, without any delay, let’s just begin.

About the Setup

I have saved an XML file (Employee Details.xml) in the following path:

D:\XML Files\Employee Details.xml

Now I will give you 2 different VBA codes to import the XML file in Microsoft Excel.

• How to Remove XML Mapping in Excel (Quickest Way)
• How to Create an XML Schema Using Data in Excel

First Code: Open XML File in a New Workbook

If you want to open your XML file in a new fresh Excel workbook, you can use this VBA code. This code will create a brand new Excel workbook and import the XML file in “Sheet1” at cell A1.

Sub Import_XML_in_New_Workbook()

 Dim gTargetFile As String
 
 Application.DisplayAlerts = False
 
 gTargetFile = "D:\XML Files\Employee Details.xml"
 Workbooks.OpenXML Filename:=gTargetFile, LoadOption:=xlXmlLoadImportToList
 
 Application.DisplayAlerts = True

End Sub

Use a VBA code to open XML file in Excel. To do that, follow these steps:

1. Press ALT + F11 to open Visual Basic Editor.
2. Then, select Insert > Module to open a new module.
   
   
3. Paste the VBA code in the new module.
4. Press the F5 button to run the code.
   Editable LineIn the VBA code, you just need to update the following line:

    gTargetFile = "D:\XML Files\Employee Details.xml"

   Here, inside the double-inverted comma, you will insert your XML file path.

   

As you run the VBA code, it will create a brand new Excel file for you. Then it will import your XML file in “Sheet1” starting from cell A1. See my result in the picture below:

• How to Import XML Data from Web to Excel
• 4 Ways to Open an XML File in Excel
• A Complete Guide to Convert a CSV File to an XML File in Excel

Second Code: Open XML File in a Specific Sheet at a Specific Location

The previous piece of code creates a new Excel file and imports an XML file in “Sheet1”. So, you will not get your flexibility in choosing a destination while importing your XML file to an Excel sheet.

However, this piece of code will import an XML file into the active Excel workbook. In this code, you can specify which worksheet you want to choose to import your XML file. Also, you can choose a cell reference in the specified worksheet to locate the imported XML file.

Sub Import_XML_at_Specific_Location()

Dim gTargetFile As String
Dim gWb As Workbook

     Application.ScreenUpdating = False
     Application.DisplayAlerts = False
     
     gTargetFile = "D:\XML Files\Employee Details.xml"
     Set gWb = Workbooks.OpenXML(Filename:=gTargetFile, LoadOption:=xlXmlLoadImportToList)
     Application.DisplayAlerts = True

     gWb.Sheets(1).UsedRange.Copy ThisWorkbook.Sheets("Sheet5").Range("B2")
     gWb.Close False
     Application.ScreenUpdating = True

End Sub

To open a XML file in Excel applying a VBA code in Excel, here are the steps below:

1. Press ALT + F11 to open Visual Basic Editor.
2. Then, select Insert > Module to open a new module.
   
   
3. Paste the VBA code in the new module.
4. Press the F5 button to run the code.
   Editable LineIn the following line, update the XML file path inside the double inverted commas.

    gTargetFile = "D:\XML Files\Employee Details.xml"

   You can specify the destination sheet name as well as a cell address in the following line:

   gWb.Sheets(1).UsedRange.Copy ThisWorkbook.Sheets("Sheet5").Range("B2")

   Inside the Sheets object, you will specify your desired sheet name. And inside the Range object, you will specify a cell address where you want to locate your XML file.

   

According to the code above, my XML file has been imported in Sheet5 of the active workbook starting from cell B2. Take a look.

Practice XML File

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<dataset xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
	<record>
		<Name>Fronek</Name>
		<Country>Portugal</Country>
		<Age>52</Age>
	</record>
	<record>
		<Name>Janman</Name>
		<Country>China</Country>
		<Age>69</Age>
	</record>
	<record>
		<Name>Dacca</Name>
		<Country>Croatia</Country>
		<Age>41</Age>
	</record>
	<record>
		<Name>Orrick</Name>
		<Country>Serbia</Country>
		<Age>51</Age>
	</record>
	<record>
		<Name>Levermore</Name>
		<Country>Indonesia</Country>
		<Age>70</Age>
	</record>
	<record>
		<Name>Chellingworth</Name>
		<Country>Russia</Country>
		<Age>37</Age>
	</record>
	<record>
		<Name>Russi</Name>
		<Country>Serbia</Country>
		<Age>33</Age>
	</record>
	<record>
		<Name>Andrei</Name>
		<Country>Russia</Country>
		<Age>35</Age>
	</record>
	<record>
		<Name>Waszkiewicz</Name>
		<Country>Russia</Country>
		<Age>45</Age>
	</record>
	<record>
		<Name>Van Giffen</Name>
		<Country>China</Country>
		<Age>31</Age>
	</record>
</dataset>

Conclusion

In this article, I’ve discussed two VBA codes to open an XML file in Microsoft Excel. I hope, you’ve found this article quite helpful. If you have any queries regarding importing XML files in Excel, please let me know in the comment section below. Also, you can check out more articles about XML in Excel on our website. Have a great day!

Frequently Asked Questions

How to open XML file?

To open an XML file, follow these steps:

1. Launch your preferred text editor or XML-compatible application.
2. Navigate to File and choose Open.
3. Browse to the location of the XML file.
4. Select the XML file and click Open.

Alternatively, use a specialized XML editor or open the file in Excel by selecting Data, then Get Data, and choosing From File > From XML.

How do I edit an XML file in Excel?

To edit an XML file in Excel, follow these steps:

1. Open Excel and go to the Data tab.
2. Click Get Data, then choose From File, and select From XML.
3. Locate and open your XML file.
4. Excel will display the XML data in a structured table.
5. Make edits directly in the cells or use the Power Query Editor for more advanced transformations.

Once edited, click Close & Load to save changes back to the XML file.

How do I open an XML Power Query in Excel?

To open an XML Power Query in Excel, go to the Data tab, select Get Data, choose From File, and pick From XML. Navigate to the XML file, select it, and click Import. Excel will launch the Power Query Editor for XML, allowing you to transform and load the data.

Related Articles

• An Overview of XML in Excel
• How to Map an XML File in Excel (Complete Guide)
• 5 Steps to Convert an Excel File to XML File