VBA Loads a XML file into a Excel Sheet

An XML file is like a simple text file but structured in a predefined format using tags. Sometimes you need to view an XML file and do calculation on the data using Excel. The code below is used to open an XML file using Excel VBA.

There are two points to take notice of when using the code.

The first point is, you need to take the fullpath of the XML file and type this in sheet 1 cell A2. An example of a fullpath is C:\Users\Jane Doe\Desktop\testfile.xml. The fullpath will be saved in the variable “xml_File_Path” as defined at line 6.

The second point is the creation of Sheet2, which you need to manually create. The content of the XML file will be specifically opened in Sheet2 cell A1 as defined at line 9. If this sheet doesn’t exist at runtime, the code will give an error stating that something is out of range.

Public Sub Convert_XML_To_Excel_From_Local_Path()

Dim xml_File_Path As String
Dim wb As Workbook

Load XML Data into a New Workbook - Code from Officetricks.com
Application.DisplayAlerts = False

xml_File_Path = ThisWorkbook.Sheets(1).Cells(2, 1)
Set wb = Workbooks.OpenXML(Filename:=xml_File_Path)

Copy Content from New workbook to current active Worksheet
wb.Sheets(1).UsedRange.Copy ThisWorkbook.Sheets("Sheet2").Range("A1")

Close New Workbook & Enable Alerts
wb.Close False
Application.DisplayAlerts = True

End Sub

If you would like to know more about how to do this refer to the below link as well.

External Link: Learn to convert any XML File to Excel