Even without the XML Spreadsheet Add-in, Access enables you to save your tables, queries, forms, and reports as XML data and schema files. However, when an Access XML data file is opened in Excel, Excel looks for a reference to an Extensible Style Sheet (XSL) file with the data file. An XSL file describes how the data is to be displayed. If the reference is present, Excel asks if you want to apply the style sheet. If you choose to apply an XSL, the XML file is displayed in the worksheet according to the rules defined in the style sheet.

If there is no XSL reference in the XML file, or if you choose not to apply the XSL, the first element in the XML file is used like a title in the worksheet and is placed in cell A1. The remaining tags are sorted alphabetically and displayed across the second row of the worksheet. The data for each of these columns is then displayed on the third and following lines of the spreadsheet. For example, consider a portion of the Employees table from the Northwind sample database:

When exported as an XML data file from Access, this table appears as follows:

<?xml version="1.0" encoding="UTF-8"?>
<dataroot xmlns:od="urn:schemas-microsoft-com:officedata">
<Employees>
<LastName>Davolio</LastName>
<FirstName>Nancy</FirstName>
<Title>Sales Representative</Title>
<BirthDate>1968-12-08T00:00:00</BirthDate>
<HireDate>1992-05-01T00:00:00</HireDate>
</Employees>
<Employees>
<LastName>Fuller</LastName>
<FirstName>Andrew</FirstName>
<Title>Vice President, Sales</Title>
<BirthDate>1952-02-19T00:00:00</BirthDate>
<HireDate>1992-08-14T00:00:00</HireDate>
...
</Employees>
</dataroot>

The table would appear in Excel as follows:

When you export the same data by using the XML Spreadsheet Add-in, the following XML data file is created:

<?xml version="1.0" encoding="UTF-16"?>
<Workbook xmlns:xsd="http://www.w3.org/2000/10/XMLSchema" xmlns:od="urn:schemas-microsoft-com:officedata" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns="urn:schemas-microsoft-com:office:spreadsheet">
<Styles>
<Style ss:ID="Header">
<Font ss:Bold="1" />
</Style>
<Style ss:ID="Default" />
<Style ss:ID="DateTime">
<NumberFormat ss:Format="General Date" />
</Style>
<Style ss:ID="Time">
<NumberFormat ss:Format="Long Time" />
</Style>
<Style ss:ID="Currency">
<NumberFormat ss:Format="Currency" />
</Style>
<Style ss:ID="Hyperlink">
<Font ss:Color="#0000FF" ss:Underline="Single" />
</Style>
</Styles><Worksheet ss:Name="Employees1">
<Table>
<Column ss:StyleID="Default" />
<Column ss:StyleID="Default" />
<Column ss:StyleID="Default" />
<Column ss:StyleID="DateTime" />
<Column ss:StyleID="DateTime" />
<Row ss:StyleID="Header">
<Cell>
<Data ss:Type="String">LastName</Data>
</Cell>
<Cell>
<Data ss:Type="String">FirstName</Data>
</Cell>
<Cell>
<Data ss:Type="String">Title</Data>
</Cell>
<Cell>
<Data ss:Type="String">BirthDate</Data>
</Cell>
<Cell>
<Data ss:Type="String">HireDate</Data>
</Cell>
</Row><Row>
<Cell>
<Data ss:Type="String">Davolio</Data>
</Cell>
<Cell>
<Data ss:Type="String">Nancy</Data>
</Cell>
<Cell>
<Data ss:Type="String">Sales Representative</Data>
</Cell>
<Cell>
<Data ss:Type="DateTime">1968-12-08T00:00:00</Data>
</Cell>
<Cell>
<Data ss:Type="DateTime">1992-05-01T00:00:00</Data>
</Cell>
</Row>
...
</Table>
</Worksheet>
</Workbook>

A description of each of the elements that make up this file is beyond the scope of this article. The file is in the form of an XML Spreadsheet, which is recognized by Excel.

For more information about XML and the XML Spreadsheet format, see the Microsoft Developer Network (MSDN) Web site.

When you open this file in Excel, you see the following:

Notice how the display of the data more closely resembles the same data display in Access.

You can download the XML Spreadsheet add-in for Access from Office Online.

To install the XML Spreadsheet add-in:

1. Double-click the file Msxmlss.exe.
2. In the Microsoft XML Spreadsheet Add-in for Access 2002 Setup dialog box, click Next.
3. Read and accept the license agreement, and then click Next.
4. Click Next to start the installation.
5. When installation is complete, click Finish.

To use the XML Spreadsheet add-in:

1. Open a database in Access 2002 or later.
   
   

    Note   When opening Access, you may receive an alert about the add-in containing macros and asking you whether you want to enable or disable macros. You must choose the enable macros option in order for the add-in to be available from the File menu.

2. Select a table or query, and then click Export to XML Spreadsheet on the File menu.
3. In the Export to XML Spreadsheet dialog box, type a destination for the file in the Save in box, and a file name in the File name box.
4. Start Excel and create a new workbook.
5. Open the XML file you created in step 3, using either of the following two methods:
   • Opening the file directly:
     1. On the File menu, click Open.
     2. In the Open dialog box, select All Files in the Files of type list, and then locate and select the XML file.
     3. Click Open to display and edit the file.
   • Importing the XML file you created in step 3:
     1. On the Data menu, point to Import External Data, and then click Import Data.
     2. In the Select Data Source dialog box, select XML Files in the Files of type list, locate the XML file, and then click Open.
     3. In the Import Data dialog box, click Open.