Create an XML data file and XML schema file from worksheet data

If you want to create an XML data file and XML schema file from a cell range on a worksheet, you can use version 1.1 of the Excel 2003 XML Tools Add-in to enhance the existing XML features in Microsoft Office Excel 2007.

What do you want to do?


Step 1: Download the XML Tools Add-in and activate it

  1. To download the add-in, click this link, Excel 2003 XML Tools Add-in, and then follow the instructions on the download page.
  2. Start Office Excel 2007.
  3. Click the Microsoft Office Button Button image, and then click Excel Options.
  1. Click the Add-Ins category.
  2. In the Manage box, click Excel Add-ins, and then click Go.
  3. In the Add-Ins dialog box, click Browse, locate the XmlTools.xla file, select the file, and then click OK.

By default, this file is stored in the following folder on your hard drive:
\Office Samples\OfficeExcel2003XMLToolsAddin.

  1. Verify that the XmlTools check box is selected in the Add-Ins available list, and then click OK to load the add-in.
  2. To verify that the add-in is active, on the Add-ins tab, in the Menu commands category, make sure that the XML Tools command menu appears.

 Note   This add-in was developed for Office Excel 2003. The documentation and the user interface refer to lists, which are now called Excel tables in Office Excel 2007.

For more information about how to use this add-in, see the article Using the Excel 2003 XML Tools Add-in Version 1.1.

Top of Page Top of Page

Step 2: Convert a cell range to an XML Table

  1. Enter the data for which you want to create the XML data file and XML schema file. The data must be in a tabular format of columns and rows (also called flat data).
  2. On the Add-ins tab, in the Menu commands group, click the arrow next to XML Tools, and then click Convert a Range to an XML List.
  3. Enter the cell range of the data that you want to convert as an absolute reference in the text box.

 Tip   To make it easier to enter the correct cell reference, in the dialog box, click the button to the right of the text box, click and hold the upper-left cell of the range, and then drag to the lower-right cell of the range.

  1. Under Use first row as column names, select No if the first row contains data or Yes if the first row contains column headers, and then click OK.

Excel automatically creates an XML schema, maps the cells to the schema, and creates an XML table.

 Note   To see all the XML maps in the workbook, on the Developer tab, in the XML group, click Source to display the XML Source task pane. In the lower portion of the task pane, click XML Maps.

Top of Page Top of Page

Step 3: Export the XML table to an XML data file (.xml)

  1. If the Developer tab is not available, do the following to display it:
    1. Click the Microsoft Office Button Button image, and then click Excel Options.
  1. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

 Note   The Ribbon is a component of the Microsoft Office Fluent user interface.

  1. On the Developer tab, in the XML group, click Export.

XML group in Ribbon

 Note   If a small Export XML dialog box with only an OK and Cancel button appears, click the XML map that you want to use, and then click OK.

This small Export XML dialog box appears only if no XML table is selected and the workbook contains more than one XML map.

  1. In the large Export XML dialog box, in the File name box, type a name for the XML data file.
  2. Click Export.

Top of Page Top of Page

Step 4: Save the XML schema to an XML schema file (.xsd)

  1. Select any cell in a mapped Excel table.
  2. On the Add-ins tab, in the Menu commands group, click the arrow next to XML Tools, and then click Create XSD files for the XML Schema at the active cell.

Excel copies the XML schema and pastes it into Notepad.

  1. In Notepad, click File, and then click Save As.
  2. Type a file name and file type, such as ProdOrders.xsd, and then click Save.

Top of Page Top of Page

 
 
Applies to:
Excel 2007