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