2 VBA Codes to Open an XML File in Excel
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.
Related: How to Remove XML Mapping in Excel (Quickest Way)
Related: 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
Usage Guide
Step_1: Press ALT + F11 to open Visual Basic Editor.
Step_2: Then select Insert ⋙ Module to open a new module.
Step_3: Paste the VBA code in the new module.
Step_4: Press the F5 button to run the code.
Editable Line
In 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.
Final Result
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:
Related: How to Import XML Data from Web to Excel
Related: 4 Ways to Open an XML File in Excel
Related: 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
Usage Guide
Step_1: Press ALT + F11 to open Visual Basic Editor.
Step_2: Then select Insert ⋙ Module to open a new module.
Step_3: Paste the VBA code in the new module.
Step_4: Press the F5 button to run the code.
Editable Line
In 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.
Final Result
According to the code above, my XML file has been imported in Sheet5 of the active workbook starting from cell B2. Take a look.
Related: 5 Steps to Convert an Excel File to XML File
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!
