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.

Table of Contents

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

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:

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.

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!

Related Articles