Excel 2003 and XML (Extensible Markup Language)

Excel 2003 supports importation, processing, and, with certain restrictions, the exportation of arbitrary XML data. Here the list functions that we have just described come into play. Upon importation, Excel automatically transforms the XML data into a table format and treats this table like a list.

In spite of the greatly improved XML functions, there is still a fundamental problem: XML data are organized hierarchically, while Excel is designed for the processing of tabular data. To be sure, Excel tries hard to transform XML data into table format to the best of its ability, but the result is satisfactory only when the XML data do not have multiple hierarchies. (And only in this case is a later exportation of data possible.)

If this condition is not satisfied, you can achieve better import results by influencing the importation process manually: You should specify which individual components of the XML data should be assigned to particular cells or ranges of cells . (This assignment is called mapping and is reflected in the new Excel object XmlMap .) Excel supports this task with a treelike representation of the XML structure in the XML Source Task Pane. You can move individual parts of this tree into the worksheet using drag and drop. Only after this assignment has been completed does the actual importation of data begin with DataXMLRefresh XML Data.

Pointer  

On the following pages we discuss the control of XML functions in VBA code. We assume that you are familiar with the basic XML functions in Excel 2003. Unfortunately, there is not sufficient space here to describe these functions and their application. That would require an entire chapter (and if we were to go into the fundamentals of XML, an entire book).

If you need to acquire some fundamental XML background, I recommend the Excel XML Toolkit for your first experiments. It was last seen, available for free download, at http://msdn.microsoft.com/library/en-us/dno2k3ta/html/ODC_Office2003Page.asp