How to Open XML File in Excel (2 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

The Extensible Mark-up Language is known as XML. This dialect is comparable to HTML.  A mark-up language really employs tags to define text and is a type of computer language. While showing the text is not pre-defined, tags are utilized to format it. This implies that the writer of the file defines the tags that create the XML file. In other words, a text-based document with an XML extension is what an XML file actually is. You can tell that a file is an XML file by looking for the .xml file extension. In this article, we will show you how to open an XML file in Excel.


How to Open an XML File in Excel: 2 Easy Ways

You might occasionally need to work with the data in an XML file because it is a widely used file format on the internet. Since the XML file isn’t intended for human reading, you can’t use it directly on machines. You will learn how to open an XML file in Excel using the Data tab and VBA Code in the next two approaches so you can quickly manipulate and analyze the data. Let’s suppose, we have a sample data set of the XML file.

Sample Data


1. Utilizing Data Tab to Open XML File in Excel

This first step will demonstrate how to access an XML file in Excel using the Data tab.

Step 1:

  • Firstly, go to the Data tab.
  • Secondly, click on the Get Data command.
  • Thirdly, select the From File option.
  • Finally, choose the From XML option to import the data from the XML file.

Sample Data

Step 2:

  • Here, we put our XML file as OrderDetails.xml in the Local Disc (C:).
  • Now, click on the Local Disc (C:).
  • Then, select the XML file named OrderDetails.xml.
  • Finally, click on Import.

Sample Data

Step 3:

  • In this part, the Navigator panel will open.
  • And, you can see the preview of the data set on the left by clicking the book option on the left side which is marked by 1.
  • Then, click on the Load option, which is marked by 2, to load your XML data into Excel.

 Easy Ways to Open XML an File in Excel

Step 4:

  • Finally, you will show the outcomes of opening the XML file in Excel by utilizing the Data tab.

 Easy Ways to Open XML an File in Excel

Read More: How to Extract Data from XML File to Excel


2. Applying VBA Code to Open XML File in Excel

In this last section, we will generate a VBA code using the Developer tab to open an XML file in Excel.

Step 1:

  • At first, we will open the Developer tab.
  • Then, we will select the Visual Basic command.

 Easy Ways to Open XML an File in Excel

Step 2:

  • Here, the Visual Basic window will open.
  • After that, from the Insert option, we will choose the new Module to write a VBA code.

 Easy Ways to Open XML an File in Excel

Step 3:

  • Now, paste the following VBA code into the Module
  • Besides, to run the program, click the “Run” button or press F5.
Sub Open_XML_File()
'Declaring Variables
Dim XMLFilePath As String
Dim WBook As Workbook
' Disabling the screen updating
Application.ScreenUpdating = False
'Does not display any alerts or messages
Application.DisplayAlerts = False
'Calling the location of the XML file
XMLFilePath = "C:\OrderDetails.xml"
'Creating a new workbook and loads an XML file into it
Set WBook = Workbooks.OpenXML(Filename:=XMLFilePath, LoadOption:=xlXmlLoadImportToList)
'Display alerts or messages
Application.DisplayAlerts = True
 'Enabling the screen updating
Application.ScreenUpdating = True
End Sub

 Easy Ways to Open XML an File in Excel

VBA Code Breakdown

  • Firstly, we will call our Sub Procedure Sub Open_XML_File().
  • Then, we declare variables using Dim XMLFilePath As String and Dim WBook As Workbook
  • Now we will call the location of the XML file using XMLFilePath = “C:\OrderDetails.xml”.
  • Finally, we will specify the new workbook to open the XML file in Excel Set WBook = Workbooks.OpenXML(Filename:=XMLFilePath, LoadOption:=xlXmlLoadImportToList).

Step 4:

  • Finally, by using the VBA code, you will demonstrate the results of opening the XML file in Excel.

 Easy Ways to Open XML an File in Excel

Read More: VBA Code to Convert XML to Excel


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice it by yourself.


Conclusion

In this article, I’ve covered 2 handy methods to open an XML file in Excel. I sincerely hope you enjoyed and learned a lot from this article. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.


Related Articles

<< Go Back to Import XML to Excel | Importing Data in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Bishawajit Chakraborty
Bishawajit Chakraborty

Bishawajit Chakraborty, a Rajshahi University of Engineering & Technology graduate with a B.Sc. in Mechanical Engineering, has been associated with ExcelDemy since 2022. Presently, he is a content developer, specializing in Excel Power Query, Data Analysis and VBA. It is worth mentioning that he has authored more than 90 articles on VBA content development. His profound interest lies in the fields of data analytics and data science. He possesses expertise in VBA, Power BI, machine learning, and Python... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo