Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Speed up your file

FastExcel
The best tool to optimise your Excel model!
Home > English site > Articles > XML and Excel > XML in Excel
Deze pagina in het Nederlands

XML and Excel

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:

By default, XML files show when you try to open a file with Excel
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).

The Open XML selection dialog of Excel
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.

The structure of the xml file shows up in the taskpane
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.

Dragging an element to an Excel cell
Figure 5: Dragging an element to an Excel cell

Result after dropping the element
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).

List produced by dragging the "employee" element to the sheet
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.

Refresh Data using the ribbon
Figure 8: Refresh Data using the ribbon

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

Result of importing the data
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):

Changing connection properties to Import another XML file into Excel

Figure 11: Changing connection properties to Import another XML file into Excel

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.


 


Comments

Showing last 8 comments of 172 in total (Show All Comments):

 


Comment by: Chip Wood (6/6/2014 5:53:56 PM)

In our system there are tables that have layers - a Master and a detail. so there can be catagories and detailes within catagories. Is there a way to build those import files from excel? We have been sucessful in importing them xml to xlm - but I have not been able to build and EXCEL file that would convert and import in a master/detail scenario.

Any Input?

 


Comment by: Jan Karel Pieterse (6/7/2014 11:56:38 AM)

Hi Chip,

Not out of the box, it would require writing VBA code to build the XML files.

 


Comment by: S.VADIVELU (7/19/2014 4:35:54 AM)

kindly create the xml file

 


Comment by: Jan Karel Pieterse (7/21/2014 9:00:11 PM)

Hi,

I'm not sure what you need?

 


Comment by: Jan (9/16/2014 6:06:56 PM)

I am trying to Export the excel file to generate the XML file using vba. First time it happens correctly but for next attempt it throws an error as same name file is already present. How to code usig vba to Override the file available with same name ?
plz help.

 


Comment by: Jan Karel Pieterse (9/17/2014 9:21:56 AM)

Hi Jan,

You could delete the file first:

Dim sFileName As String
'Some code in which sFileName gets its value
On Error Resume Next 'Prevent error in case file isn't there
Kill sFileName
On Error Goto 0 'Reset error ignore
'Now save xml file.

 


Comment by: Jan (9/30/2014 9:22:53 AM)

Thanks Jan for your comment.

I have already mapped the elements in the excel file and there are some cells where I need to use single element from XSD to map. The element will be mapped on multiple cells. But I am not able to map 1 element for multiple cells, it shows an error saying that you can not map multiple cells with 1 element.
Plz Help.

 


Comment by: Jan Karel Pieterse (9/30/2014 9:46:49 AM)

Hi Jan,

An element in an XML file cannot be mapped to more than one cell (or column if it is a repeating element), there is no way around that. But you could use a formula in the other cells that point to the mapped cell perhaps?

 


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):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].