Saving Your Workbook as an XML File

XML is a file format that enables documents to share data dynamically on the Web or on an intranet. XML documents are text-based files that include codes for structuring and identifying the various data entries and controlling formatting. For example, an XML workbook file might contain a basic text code that labels a particular entry as Customer Name or Part Number. Because these codes are text-based, they are universally recognized by any program that supports XML. Unlike HTML, which controls formatting (the appearance of data onscreen and in print), XML focuses on identifying and structuring the data entries to make them more universally accessible.

Excel 2003 provides additional support for XML files, allowing you to save your workbooks as XML files, open any properly structured XML file in Excel, and create Web queries that extract data from other XML sources on the Internet or your company's intranet.

Fortunately, converting an Excel workbook into a properly coded XML file is easy: You simply save the file as an XML file. The following steps lead you through the process:

1. Open the Excel workbook file that you want to save as an XML file.

2. Open the File menu and choose Save As . The Save As dialog box appears, as shown in Figure 14.12.

   Figure 14.12. Use the Save As dialog box to save your workbook in the XML format.

   

3. Open the Save As Type list and choose XML Spreadsheet or XML Data . (Both options save the workbook as an XML file. XML Spreadsheet uses Excel stylesheets to structure the data and make it look more like an Excel worksheet when published to the Web. XML Data is a raw format that preserves only the data.)

4. If desired, choose a different location for the XML version of the workbook, and type a new name for the workbook file. (If you choose not to rename the workbook file, you will have two files with the same name: One file will have the XLS filename extension, which is Excel's default file format, and the other will have the XML extension.)

5. Click the Save button. Excel converts the file to the XML format and saves it.

In addition to enabling you to save workbooks as XML files, Excel provides tools to open XML files created in Excel and other programs and to import selected data from XML files. To import data from any XML file, follow these steps:

1. Open the workbook into which you want to import the data.

2. If the task pane is not displayed, open the View menu and choose Task Pane . The task pane appears on the right side of the screen.

3. Open the drop-down list at the top of the task pane, and choose XML Source . The XML Source task pane appears, as shown in Figure 14.13. The List and XML toolbar also appears; this toolbar enables you to import and export XML data and perform other XML related tasks .

   Figure 14.13. Use the XML Source task pane to import data.

   

4. Near the bottom of the XML Source task pane, click the XML Maps button. The XML Maps dialog box appears.

5. Click the Add button. The Select XML Source dialog box appears, prompting you to choose the file from which you want to import data.

6. Change to the drive and folder that contains the XML source file, and double-click the file's name. (The XML source can be on your computer, on a network server, or on the Internet.)

7. If the XML source file does not include its own custom schema (map), then Excel displays a message indicating that it can create a schema for the file.

8. Click OK . The file's location and name appear in the XML Maps dialog box.

9. Click OK . The XML Source task pane displays the XML map of the file's contents.

10. Scroll down the map to locate the Data folder. This folder contains the actual data and formatting instructions for the data.

11. Move the mouse pointer over the entry for the actual data you want to import, and then drag the entry to the desired location in your Excel workbook, as shown in Figure 14.14. Excel displays the Data heading as a column heading but does not display the data until the next step.

    Figure 14.14. You can drag entries from the map to your worksheet to import them.

    

12. Right-click the Data heading, point to XML , and click Refresh XML Data , as shown in Figure 14.15. Excel imports and displays the data.

    Figure 14.15. You must refresh the XML data to be able to view the imported data.

    

Note

When you import XML data, all the data is dumped directly into the cells below the Data heading. In most cases, you need to manually rearrange the data to organize it.

The Absolute Minimum

When you decide to forgo paper copies of your worksheet and opt instead to publish and distribute your worksheets electronically on the Web, turn to this chapter for a brief refresher course. In this chapter, you learned how to

Transform a workbook or any portion of it into a Web page.

Insert links that point to other files, Web pages, or e-mail addresses.

Publish your worksheets on the Web by saving them directly to a network server, Web server, or FTP server.

Import data from other Web sources and have that data automatically updated when the Web source data changes.

Send your worksheets or workbooks via e-mail to friends , relatives, clients , and colleagues.

Make your worksheets universally accessible by saving them as XML data files.

With the skills you acquired in this chapter, you can convert any workbook, worksheet, or range of data into an attractive Web page; add links to other Web pages; and publish your data on the Web or on your company's intranet. You can also e-mail your workbooks or worksheets to share them with other users.

In the next chapter, we shift gears and look at another exciting, high-end Excel feature that enables you to enter commands and data orally, rather than by typing or using your mouse.