Excel 2003 Programming: A Developer's Notebook

Transform XML Spreadsheets

XML spreadsheets provide Excel data in a format that can be easily used by other applications or transformed into presentation documents, such as HTML web pages. For either task, you often need to modify the content of the XML spreadsheet, and the best way to do that is with XSLT.

You can use XSLT to perform a wide variety of transformations, such as:

• Extract specific items from a spreadsheet—such as retrieving only worksheets containing data

• Transform the spreadsheet into HTML

• Make global changes to the spreadsheet

• Highlight significant items, such as high or low outlier numbers

Note

Now that you’ve saved a workbook as XML, what can you do with it? XSLT lets you transform XML into other formats, extract or highlight information, or make other changes.

How to do it

To transform an XML spreadsheet, follow these general steps:

1. Create an XSLT file to perform the transformation, using Notepad or some other editor.

2. Perform the transformation in code, from the command line, or by including a processing instruction.

3. Save the result.

There are three different ways to perform the transformation. The following table compares each of those techniques, and the sections that follow describe each of the preceding steps in more detail.

Transformation	Use to	Advantages	Disadvantages
Code	Automatically generate the result from within Visual Basic.	Can be performed with a single click by the user, or in response to an event.	Requires Excel to be running.