Programming Excel with VBA and .NET

Open XML Files

Tip

Important XML features are part of Excel 2003 Professional and standalone versions for Windows. Earlier and Macintosh versions of Excel support only limited access to XML files.

Text files may be the universal data format of today, but the future belongs to XML. XML is actually a type of text file, since XML files are stored as text. But unlike delimited text files, they are self-describing. That means Excel doesn’t have to guess where a field or record starts; the information is right there in the file:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Order>
    <ID>2002</ID>
    <BillTo>
        <Address>
             <Name>Biege Bond</Name>
             <Street1>55 Lost Lane</Street1>
             <City>Anywhere</City>
             <State>AR</State>
             <Zip>67832</Zip>
         </Address>
    </BillTo>
    <Line>
         <Number>10</Number>
         <Description>Qt Microballoons</Description>
         <Quantity>1</Quantity>
         <UnitPrice>95</UnitPrice>
         <Taxible>No</Taxible>
         <Total>95</Total>
    </Line>
</Order>

In the preceding XML, items surrounded by brackets identify the data, < tag > and </ tag > notation shows where a data item starts and ends, and the fact that some tags contain others establishes the relationships between items. You’ll notice that this is not a strict row/column relationship—not all data is grid oriented! This means that Excel often has to be told where to put XML items on a worksheet.

The easiest way to see how this works is to follow these steps:

1. Open an XML file in Excel. Choose File → Open, select an XML file, and click OK. Excel displays a dialog ...