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 importing XML data is very simple. Unfortunately, the XML facilities are only available in Excel 2007 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), 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.

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.

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

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 92 in total (Show All Comments):

Comment by: Jan Karel Pieterse (12/29/2011 7:08:42 AM)

Hi Neil,

I would record a macro in Excel whilst opening the file, it should give you the right syntax to open the file directly without the prompt.

Comment by: Jan Karel Pieterse (12/29/2011 7:13:16 AM)

Hi Umesh,

Excel's XML export capabilities are limited, you will probably have to revert to writing VBA code to get this working.

Comment by: neil (12/29/2011 1:59:39 PM)

Thanks, Does not work. The name of the file I open is different each time. I will try another workaround instead.

Comment by: Jan Karel Pieterse (12/30/2011 12:27:17 PM)

Hi Umesh,

It is fairly simple to write a little bit of VBA code that enables you to select a file to import and then use the resulting information in your recorded macro. For example:

Sub GetOpenFileNameExample()
    Dim vFilename As Variant
    Dim sPath As String
    sPath = ActiveWorkbook.Path & "\"
    ChDrive sPath
    ChDir sPath
    vFilename = Application.GetOpenFilename("xml files (*.xml),*.xml", , "Please select the file(s) to import", , False)
    If TypeName(vFilename) = "Boolean" Then Exit Sub
    If CStr(vFilename) = "" Then Exit Sub
    If Len(Dir(CStr(vFilename))) > 0 Then
' Now do something with vfilename, like:
' Workbook.OpenText Filename:=vFilename
'So your recorde macro could start here, where you replace
'the name the XML import does with
'vFilename
End If
End Sub

Comment by: Shilpee (1/3/2012 11:13:29 PM)

Hi

My XML file contain data for 5 tables. If I want to import only a susbset of data, how can i do that?

Comment by: J (1/3/2012 11:57:48 PM)

Hi Shilpee,

The only way I know would be to import everything and use filtering to get the right table (assuming there is a way to discern the different tables).

Comment by: Bob Tom (2/2/2012 4:02:10 AM)

Hi Jan,

Firstly, thanks for maintaining such an extremely useful blog on xml maps.

I'm clutching onto the ends of my sanity, working on what one would assume Excel would handle elegantly!

After googling for VBA code to parse the xml to retrieve node information, I have now started using xml maps.

The only issue I have is in dynamically setting the path for xml to be imported. If I hardcode it, it works like a charm. But I'm using formulae to concatenate the TargetPath based on the Date and Currency that user chooses. When I run the macro, Excel crashes with a System Error. [I'm using Excel 2003 SP3).

I'm adding the code as well below.

Sub GetInterestRates()
Dim TargetPath As String
TargetPath = ActiveSheet.Range("S12").Select
ActiveWorkbook.XmlMaps("interestRateCurve_Map").Import URL:=TargetPath
End Sub

If i do Msgbox(TargetPath), it just returns True. Is that the problem?

Any assistance in this regard would be highly appreciated!!!!

Kind Regards,
Bob

Comment by: Jan Karel Pieterse (2/2/2012 4:33:14 AM)

Hi Bob,

You need to replace the .Select with .Value. The Select method just selects the cell and returns True if the selecting succeeded. You need the Value of that cell.

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.