XML in Excel

Let's get a bit more practical and use Excel to do something with XML files. With Excel 2003/2007/2010/2013 importing XML data is very simple. Unfortunately, the XML facilities are only available in Excel 2007
and up and in the Microsoft Office Professional or ultimate versions of Excel 2003, or when Excel 2003 is bought as a standalone package. When you select File (Office button in Excel 2007
and up), Open from Excel, then you will see xml file types are shown along with the available Excel files:

Figure 2: The File, Open window of Excel 2007 and up.

Selecting the file test-en.xml causes a small window to appear (see figure 3).

Figure 3: Excel asking what to do with the XML file

By selecting the third option Excel will analyse the structure of the XML file and present the structure in the taskpane (figure 4). Excel does not open the XML file at this moment.

Figure 4, The structure of the xml file shows up in the taskpane

The elements of the XML file can now be tied to cells on the sheet, simply by dragging them from the task pane to a cell in Excel. See Figure 5 and 6.

Figure 5: Dragging an element to an Excel cell

Figure 6: Result after dropping the element

By dragging an element which contains multiple other elements (or by selecting multiple elements in the taskpane using control+click or shift+click) all elements are placed next to each other as a list:(see figure 7).

Figure 7: List produced by dragging the "employee" element to the sheet

Now a template has been defined which can import data from XML files which have the same structure as the one we "opened" in Excel. We can import the data from test_en.xml, simply by pressing the refresh button on the "External Table Data" group of the custom "Table tools, design" tab of the ribbon in Excel 2007
and up, or by clicking the Refresh XML data button on the List toolbar of Excel 2003 (see figure 8 and 9). The data from test_en.xml will be read into our newly created template. See figure 10.

Figure 8: Refresh Data using the ribbon

Figure 9: Button "Refresh XML data"on the "List" toolbar

Figure 10: Result of importing the data

To import a different file into the same structure, use the connection properties dialog, which you can access by clicking the dropdown arrow below the refresh button (see figure 11):

After clicking connection properties, click the Details tab on the dialog that shows up and you can hit the browse button to access a different XML file. Or -in Excel 2003- Simply press the "Import XML" button on the List toolbar.

Can you please try to be a little more specific? Right now I am not sure what you are trying to achieve.

Comment by: Madhu (12/10/2014 10:54:34 AM)

Hi Jan,
I wish to extract only two tags of information from a group of xml files that located in a folder. And after extracting this informatin i wish to store it in a excel file.
Could you please help me.
Thanks
Madhu

you should be able to find some code that demo's refreshing the connection and copying the result to another worksheet.

Comment by: Varun Sharma (3/5/2015 5:45:44 AM)

Thanks for the helpful article. I have a requirement to convert an office xml into a csv file in an automated fashion using a script.
Any pointers / examples on how this can be approached are much appreciated!

Comment by: Jan Karel Pieterse (3/5/2015 12:05:19 PM)

Hi Varun,

Have you got the XML import working?

Comment by: Jan (3/30/2015 1:32:36 PM)

Hi Jan,

I am trying to open another excel file from one using code and trying to select one the sheet from new excel but it is giving 'Subscript out f range' error.

Please suggest.

Thanks.

Comment by: Jan Karel Pieterse (3/30/2015 3:19:45 PM)

Hi Jan,

Probably the sheetname in your code is not exactly the same as the name on the tab?

Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):