Excel Made Easy

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

excel vba import xml data

2.Select "From XML Data Import".

TheXML file selection dialog box will appear as shown below:

excel vba import xml data

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

  1. Sub ImportXML()
  2. Dim xmFile As String
  3. Application.DisplayAlerts = False
  4. xmFile = "D:\test.xml"
  5. Workbooks.OpenXML Filename:=xmFile, LoadOption:=xlXmlLoadImportToList
  6. Application.DisplayAlerts = True
  7. End Sub

 

You can find similar Excel Questions and Answer hereunder

1) How to use data table function in Excel. It helps you go through various scenario when seeking a goal

2) How can I plot 2 series on the same chart with different scales / measurement unit for Values (Ex: Pareto chart)?

3) Here some explanations about how to load an XML document in Excel VBA

4) How can I use SUMPRODUCT to summarize my raw data?

5) What is activeX data object in Excel. How to use it? Here the basic explanations

6) What is the xml group in the developer tab in Excel

7) How can I fill a series of data automatically?

8) How can I identify all cells with Data Validation in my WorkSheet?

9) How can I set the Source Data of charts using VBA?

10) How can I get row count of filtered data?

 

Here the previous and next chapter

Excel Made Easy was founded in 2002. Excel Made Easy is a service to people looking to learn Microsoft Excel or any other spreadsheet SW on the market.

We endeavor to provide the best service and support and to respond quickly to your questions.

Excel Made Easy is a proud sponsor of Dismantle-It.com the site that shows you the belly of your daily objects, Poupounette.com the site of cars loving cat and PowerPointMadeEasy.com the world top reference for PowerPoint Presentations questions and Pet Cemetery Online a site dedicated to your loving Pets.