About the Microsoft XML Spreadsheet Add-in for Access 2002

Download the odc_msxmlss.exe sample file. (188 KB) This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Frank C. Rice
Microsoft Corporation

August 2001

Applies to:
     Microsoft Access 2002
     Microsoft Excel 2002

Summary: This article explains the Microsoft XML Spreadsheet Add-in, which can be used to quickly get your Access 2002 data into a format that can be easily recognized and used by Microsoft Excel 2002 or any application that recognizes the XML Spreadsheet format. A brief description of the Microsoft Visual Basic code that makes up the add-in is also included. (8 printed pages)

Download odc_msxmlss.exe.

Contents

Introduction
Displaying XML Data in Excel
Displaying an XML Spreadsheet File in Excel
Obtaining and Installing the Add-in
Using the Add-in
The Add-in Code
Conclusion

Introduction

You can now export the data in your tables, queries, forms and reports from Microsoft® Access 2002 to an XML Spreadsheet formatted file by using the Microsoft XML Spreadsheet Add-in (odc_msxmlss.exe). This add-in provides an easy way for you to quickly get your Access data into a format that can easily be recognized and used by Microsoft Excel or any application that recognizes the XML Spreadsheet format. This article describes the difference between displaying a standard XML data file and an XML Spreadsheet formatted file in Excel, as well as information on how to obtain, install, and use the add-in. It also briefly describes the Microsoft Visual Basic® code that comprises the add-in.

Displaying XML in Excel

Even without the XML Spreadsheet Add-in, Access 2002 enables you to save your tables, queries, forms and reports as XML data and schema files. When an XML data file is opened in Excel, the application looks for a reference to an XML Stylesheet Language (XSL) file with the data file. An XSL file describes how the data is to be displayed. If the stylesheet is available, Excel then prompts you with a choice to apply the style sheet or not. 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 no XSL style sheet is referenced in the XML data file, or you choose not to apply the XSL style sheet, Excel applies an XML spreadsheet flattener algorithm to display the data. The first element in the XML file is used like a title for 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. The following example shows a portion of the Employees table from the Northwind sample database:

When exported as an XML data file from Access:

<?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>

This file would be displayed in Excel as:

Now we'll look at displaying the same data in the XML Spreadsheet format in Excel.

Displaying an XML Spreadsheet File in Excel

When we export the same data by using the Microsoft 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. However, it is sufficient to say that the file is formatted as an XML Spreadsheet that is recognized by Excel. More information about XML and the XML Spreadsheet format can be found at the MSDN XML Developer Center and the MSDN Office Developer Center.

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

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

Obtaining and Installing the Add-in

The Microsoft XML Spreadsheet Add-in is available as a download with this article.

To install the Microsoft XML Spreadsheet Add-in:

1. Open the file msxmlss.exe.
2. Click Next in the Microsoft XML Spreadsheet Add-in for Access 2002 Setup dialog box.
3. Read and accept the license agreement and then click Next.
4. Click Next to start the installation.
5. Click Finish to exit once the installation is successful.

Using the Add-in

Before using the add-in, a few limitations should be noted:

• Any Access 2002 currency format (Euro, percent, fixed, standard, etc.) will be changed to US dollars when converted to an XMLSS format.
• Any Access 2002 date format will be changed to General Date format when converted to an XMLSS format.
• Because each record has to be converted individually to an XMLSS format, record sets larger than approximately 15,000 records will require a significant amount of time to process. The time will vary based on your system configuration, but large records sets could take several hours to process. While processing records, Access 2002 will be unavailable for other operations.

To use the add-in:

1. Open a database in Access 2002.
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, enter a destination for the file in the Save in list, and a filename in the File name list.
4. Start Excel 2002, create a new workbook, and then do one of the following:
   • Open the Employees.xml file directly:
     • Click Open from the File menu.
     • In the Open dialog box, select All Files in the Files of type list, and then locate and select the XML file.
     • Click Open to display and edit the file.
   • Import the Employees.xml file:
     • Point to Import External Data on the Data menu and then click Import Data.
     • In the Select Data Source dialog box, select XML Files in the Files of type list, locate the XML file, and then click Open.
     • Click Open in the Import Data dialog box.

The Add-in Code

The code that makes up the Microsoft XML Spreadsheet Add-in is divided into a number of Visual Basic subroutines and functions. We'll begin looking at the code (you can see the procedures by opening the file msxmlspreadsheet.vbg in a Visual Basic Project) starting with the ExecuteXMLSpreadsheet subroutine. This subroutine uses a series of If statements to determine the type of object being exported in order to construct the Export XML dialog box title.

If gobjAppInstance.CurrentObjectType = acTable Then
    ac = acExportTable
    DlgTitle = DlgTitle & "Table"
ElseIf gobjAppInstance.CurrentObjectType = acQuery Then
    ac = acExportQuery
    DlgTitle = DlgTitle & "Query"
ElseIf gobjAppInstance.CurrentObjectType = acServerView Then
    ac = acExportServerView
    DlgTitle = DlgTitle & "View"
    ...
Else
    MsgBox "Please select a table or query to export.", , _
           "Export to XML Spreadsheet"
    Exit Sub
End If
DlgTitle = DlgTitle & " '" + TableName + "' To XML Spreadsheet..."

The procedure exports the XML data file by using the new ExportXML method in Access 2002 and, after determining the location of the XSL style sheet file from the Windows registry, calls the TransformXML function with the name of an XSL style sheet as a parameter.

Note   The XSL style sheet (od2ss.xsl) actually transforms the XML data file structure into the XML Spreadsheet format. The XSL style sheet contains Microsoft JScript® and XSL Transformations (XSLT) commands that perform the transformation. More information about XSLT can be found at the MSDN XML Developer Center.

The TransformXML function encapsulates all of the processing that needs to occur to transform the raw XML data into the XML Spreadsheet format and save the result to a text file. The function first loads the XML data file and the XSL style sheet and then calls the TransformNode method of the Document Object Model (DOM).

Note   Interfaces to the methods, objects, and properties of the DOM are available in the Microsoft XML parser/XSL processor (MSXML). In addition, MSXML contains an XML parser, an XSL processor, an XPath processor that can be used with XSL style sheets or directly with the DOM, and a schema processor. Information on these terms and obtaining the MSXML parser/processor is available from the MSDN XML Developer Center.

The SaveTextFile function is then called to save the string result of the transformation to a text file.

The add-in also has a procedure (CreateAddInCommandBarButton) for creating a new command bar button or removing the command bar button if the user uninstalls the add-in.

Finally, the AddinErr subroutine displays pertinent information about any errors that occur while creating or removing the command bar.

Conclusion

In this article, we introduced the Microsoft XML Spreadsheet Add-in. This add-in provides an easy way for you to quickly get your Access 2002 data into a format that can easily be recognized and used by Excel or any application that recognizes the XML Spreadsheet format. We also looked briefly at the code that comprises the add-in. An understanding of the code and the XML/XSL files involved can provide you with a starting point to create your own add-in.