4 Ways to Open an XML File in Excel

Rhidi Barma Advanced Excel

You may need to import your XML file inside Microsoft Excel to do some further analysis. However, Excel offers several ways to open an XML file to enable you to use your XML file in all situations. Thus, I’m here to show you 4 different ways to open an XML file in Excel. So, without any delay, let’s just begin.

About the Setup

I’ve saved an XML file named “Sample XML File.xml” in the following directory:

D:\XML Files\Sample XML File.xml

In this article, I will show you 4 different ways to open this XML file in Excel.

Related: 2 VBA Codes to Open an XML File in Excel

Related: How to Remove XML Mapping in Excel (Quickest Way)

Easiest Way to Open an XML File

You can import your XML file from the XML group in the Developer tab.

However, if you haven’t activated your Developer tab yet, read 2 ways to enable the Developer tab in Excel.

After enabling the Developer tab,

Step_1: Select the Developer tab in the ribbon.

Step_2: Click on the Import button in the XML group.

Using Developer Tab to Open an XML File in Excel

Now the Import XML dialog box will appear.

Step_3: Navigate through the folder to locate your XML file.

Step_4: After selecting the right XML file hit the Import button.

Using Developer Tab to Open an XML File in Excel

After that, a rectangular dialog box will open with the following message: “The specified XML source does not refer to a schema. Excel will create a schema based on the XML source data.”

Step_5: Just hit the OK button to proceed.

Now the Import Data dialog box will appear.

Here, you can specify whether you want to put the XML file in the existing worksheet or in a new worksheet.

Step_6: Choose a destination option.

Here, I’m going with the “XML table in existing worksheet” option.

Step_7: Then click on a cell to locate your XML file.

I’m choosing cell A1 in sheet1.

Step_8: Finally, hit the OK button.

Using Developer Tab to Open an XML File in Excel

Now, you will notice that the XML file has been imported in cell A1 of sheet1. Have a look:

Related: How to Create an XML Schema Using Data in Excel

Related: How to Import XML Data from Web to Excel

Alternative Way #1: Load an XML File Using Power Query

The Power Query is a very powerful tool inside Microsoft Excel that enables you to import data from various sources.

However, you can use the Power Query to import an XML file too.

For that,

Step_1: Go to the Data tab ⋙ Get & Transform Data group.

Step_2: After that, select Get Data ⋙ From File ⋙ From XML.

Using Power Query to Open an XML File in Excel

Step_3: In the Navigator window, select “record” just below your XML file.

Step_4: After that, hit the Transform Data command.

Using Power Query to Open an XML File in Excel

This will take you to the Power Query Editor window. Where you will find your XML file already interpreted as an Excel table. If you need to perform any processing, you can do that here.

To import the data table from Power Query Editor to your Excel worksheet,

Step_5: Hit the Close & Load button.

Using Power Query to Open an XML File in Excel

Now, you have successfully imported your XML file to the Excel worksheet. Take a look:

Related: A Complete Guide to Convert a CSV File to an XML File in Excel

Related: 5 Steps to Convert an Excel File to XML File

Alternative Way #2: Import an XML File with Visual Basic Script

If you are a VBA freak, you can use the following Visual Basic Script to import your XML file inside an Excel worksheet.

For that,

Step_1: Press ALT + F11 to open the Visual Basic Editor.

Step_2: Go to Insert ⋙ Module to open a new module inside the Visual Basic Editor.

Step_3: Then paste the following Visual Basic Script inside the new module.

Sub Import_XML_File()

Dim gXMLFilePath As String
Dim gWBook As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False

gXMLFilePath = "D:\XML Files\Sample XML File.xml"
Set gWBook = Workbooks.OpenXML(Filename:=gXMLFilePath, LoadOption:=xlXmlLoadImportToList)

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Step_4: Insert the directory path of the XML file in the VBA code.

Look at the following screenshot to get an idea of where to insert the directory path of the XML file.

Using Visual Basic Script to Open an XML File in Excel

Get the Directory Path of Your XML File

If you don’t know how to get the directory path of your XML file,

  • Browse through the folders where you’ve kept the XML file.
  • Then click on the address bar.

The address of your XML file will be highlighted in blue color.

  • Press CTRL + C to copy it.
  • Before pasting the directory path in the VBA code, add a backward slash “\” followed by the XML file name.

For example, the copied address from the address bar is:

D:\XML Files

The complete address for the VBA code is:

D:\XML Files\Sample XML File.xml

Step_5: After pasting the VBA code, come back to your worksheet and choose a cell to put the XML file.

Here I’ve selected cell A1.

Step_6: Press ALT + F8 to open the Macro dialog box.

Step_7: Select the subroutine “Import_XML_File” and then hit the Run button.

Using Visual Basic Script to Open an XML File in Excel

Now, you have successfully imported your XML file to the Excel worksheet. Take a look:

Related: An Overview of XML in Excel

Related: How to Map an XML File in Excel (Complete Guide)

Alternative Way #3: Open an XML File From File Manu

You can also open an XML file while starting your Excel program using the Open option. You can also find the Open option in the File menu.

Anyways, here are the steps to follow:

Step_1: Select the File menu in the ribbon.

Using File Menu to Open an XML File in Excel

Step_2: Now go to Open ⋙ Browse.

Using File Menu to Open an XML File in Excel

The Open XML dialog box appears with 3 available options:

  • As an XML table.
  • As a read-only workbook
  • Use the XML Source task pane

Step_3: To open the XML file with full flexibility to just like an Excel table, select the “As an XML table” option.

Step_4: Then hit the OK button.

Using File Menu to Open an XML File in Excel

A rectangular dialog box appears.

Step_5: Just hit the OK button to proceed.

Finally, you can see that the XML file has been imported inside an Excel worksheet. Just take a look:

Refresh XML Data

When you import an XML file inside an Excel worksheet, a direct link establishes between your Excel file and the XML file. Thus, if you change your XML file, you can see the changes in your Excel worksheet. But as the connection is semi-dynamic, you have to refresh your Excel worksheet to see that changes.

There are multiple ways to refresh your Excel worksheet. I’m showing you them all one by one.

1. Using Refresh Data Command

Just go to the Developer tab in the ribbon. Then hit the Refresh Data command in the XML group.

Using Refresh Data Command to Refresh Worksheet in Excel

2. Using Refresh All Command

You will find the Refresh All command in the Queries & Connections group under the Data tab. Just click on the Refresh All command to refresh your Excel worksheet.

Using Refresh All Command to Refresh Worksheet in Excel

3. Using the Context Menu

You need to select any of the cells of your Excel Table that was created by importing the XML file. Then right-click on the cells. From the context menu, go to XML ⋙ Refresh XML Data to refresh your Excel worksheet and see the changes made.

Using Refresh XML Data Command to Refresh Worksheet in Excel

Practice XML File

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<dataset xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
	<record>
		<FirstName>Elvira</FirstName>
		<LastName>Recher</LastName>
		<Email>erecher0@goo.ne.jp</Email>
		<Gender>Female</Gender>
	</record>
	<record>
		<FirstName>Felisha</FirstName>
		<LastName>Antognozzii</LastName>
		<Email>fantognozzii1@apple.com</Email>
		<Gender>Female</Gender>
	</record>
	<record>
		<FirstName>Zilvia</FirstName>
		<LastName>Thandi</LastName>
		<Email>zthandi2@bloomberg.com</Email>
		<Gender>Female</Gender>
	</record>
	<record>
		<FirstName>Jon</FirstName>
		<LastName>Croom</LastName>
		<Email>jcroom3@clickbank.net</Email>
		<Gender>Male</Gender>
	</record>
	<record>
		<FirstName>Lars</FirstName>
		<LastName>Gresham</LastName>
		<Email>lgresham4@jiathis.com</Email>
		<Gender>Male</Gender>
	</record>
	<record>
		<FirstName>Ozzie</FirstName>
		<LastName>Corrie</LastName>
		<Email>ocorrie5@google.ca</Email>
		<Gender>Male</Gender>
	</record>
	<record>
		<FirstName>Kathy</FirstName>
		<LastName>Stirtle</LastName>
		<Email>kstirtle6@wired.com</Email>
		<Gender>Female</Gender>
	</record>
	<record>
		<FirstName>Niels</FirstName>
		<LastName>Dilloway</LastName>
		<Email>ndilloway7@ted.com</Email>
		<Gender>Male</Gender>
	</record>
	<record>
		<FirstName>Dwight</FirstName>
		<LastName>Benduhn</LastName>
		<Email>dbenduhn8@bandcamp.com</Email>
		<Gender>Male</Gender>
	</record>
</dataset>

Conclusion

Here, I’ve discussed 4 unique methods to open an XML file in Excel. I hope, you will find this article helpful. If you have any queries regarding the XML file in Excel, feel free to let me know in the comment section. I will try to respond as soon as possible. Thank you.

Data Analyst & Research Associate

Howdy! I'm Rhidi Barma. I'm a professional Excel user. I've completed my graduation from Jahangirnagar University, Bangladesh. I've been working with Microsoft Excel since 2015. I love writing blogs on MS Excel tips & tricks, data analysis, business intelligence, capital market, etc. I love to spend my leisure time reading books and watching movies. To know more about me, you can follow me on social media platforms. Thanks!

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *