Vba import xml data in Excel
Microsoft Excel makes it easy to import data from Extensible Markup Language (XML).
XML is a technology that is designed for managing and sharing structured data in a human-readable text file.
XML greatly eases the definition, transmission, validation, and interpretation of data between databases
XML is also platform independent and data are self describing and hence is the most preferred format of data exchange across the web.
This topic details on how to import data from XML to Excel.
Excel works with two types of XML files
1..XML files
2.Schema (.XSD) files
To import XML file, the following steps are to be followed:
1.Goto Data tab
2.Select "From XML Data Import".
TheXML file selection dialog box will appear as shown below:
After selecting the XML file, the data are imported into the sheet with every parent tag as column header.
The following code snippet illustrates the same with VBA code
- Sub ImportXML()
- Dim xmFile As String
- Application.DisplayAlerts = False
- xmFile = "D:\test.xml"
- Workbooks.OpenXML Filename:=xmFile, LoadOption:=xlXmlLoadImportToList
- Application.DisplayAlerts = True
- End Sub