Using the Excel 2003 XML Tools Add-in Version 1.1

 

James Rivera
Frank Rice
Microsoft Corporation

February 2005

Applies to:
    Microsoft Office Excel 2003

Summary: Learn how to use the Microsoft Office Excel 2003 XML Tools Add-in Version 1.1. With it, you can provide information about a selected cell's XML properties, create XSD files for XML maps, rename the <Root> and <Row> elements, or refresh all of the XML maps in a workbook at once. (3 pages)

Download OfficeExcelXMLToolAddin.exe.

Contents

Overview of the Excel XML Tools Add-in
Excel XML Tools Add-in Requirements
Installing and Setting Up the Excel XML Tools Add-in
Using the Excel XML Tools Add-in
Conclusion
Additional Resources

Overview of the Excel XML Tools Add-in

The Microsoft Office Excel 2003 XML Tools Add-in Version 1.1 is used to aid Excel 2003 developers in working with XML data, XML lists, and XML maps. With it, you can view information about a selected cell's XML properties, create XSD files for XML maps, rename the <Root> and <Row> elements, or refresh all of the XML maps in a workbook at once, all common tasks you'd use when working with XML lists and XML maps.

The Excel XML Tools Add-in also enables you to create XML lists. Excel lists consist of a series of rows of related data. An XML list is an Excel list that has been mapped to one or more XML elements. Each column in the XML list represents an XML element. A list provides features designed to make it easier to manage and analyze groups of related data in a worksheet.

Once you create an XML list, you can then import or export the data that matches the schema using the new Excel 2003 XML mapping. The XML list can also provide information about a selected cell's XML properties (such as XPath), create XML schema (XSD) files for the XML map at the selected cell, rename the <Root> and <Row> elements, or refresh all XML maps within a workbook at once.

Excel XML Tools Add-in Requirements

The Excel XML Tools Add-in (.xla) requires the XML mapping feature, which is included in any of the following programs in the Microsoft Office System:

  • Microsoft Office Excel 2003 (stand-alone version)
  • Microsoft Office Professional Edition 2003
  • Microsoft Office Professional Enterprise Edition 2003

The XML mapping feature is not included in the following versions:

  • Microsoft Office Standard Edition 2003
  • Microsoft Office Student and Teacher Edition 2003
  • Microsoft Office Basic Edition 2003
  • Microsoft Office Small Business Edition 2003

This Excel add-in is not compatible with these programs in the Microsoft Office System.

Installing and Setting Up the Excel XML Tools Add-in

To install the add-in, complete the following steps:

  1. Start Excel.

  2. On the Tools menu, click Add-Ins.

  3. Click the Browse button and navigate to the XmlTools.xla file.

  4. Select it and then click OK.

  5. Click Yes to any file copy or overwrite prompts.

  6. Verify the XmlTools box is selected.

    Note   To remove this add-in, repeat this procedure, but clear the XMLTools box.

  7. Click OK to use the add-in. A top-level menu entry, XML Tools should be present.

Using the Excel XML Tools Add-in

The Excel XML Tools Add-in enables you to automate several tasks that normally you need to perform manually. For example, refresh all of the XML maps in a worksheet or display the XML properties of the active cell. The following sections describe each of these tasks and more.

Convert a Range to an XML List

To convert a range to an XML list, complete the following steps:

  1. On the XML Tools menu, click Convert a Range to an XML List. The Convert range to XML list dialog box is displayed.
  2. In the text box, click the button on the right. A floating text box is displayed.
  3. Click the upper-left corner of the range and drag the mouse to the lower-right of the range. Notice that the range address is displayed in the text box. Click the icon on the right side of the text box.
  4. Select whether the first row of the selected range contains column header labels or actual data.
  5. If desired, you can change the names of the root and row elements under Advanced Options. This is useful for custom XML file creation for use with other apps that expect certain root and row names. It can also be useful in creating larger XML files created from several exports where there may be some nesting.

Create XSD Files for the XML Schema at the Active Cell

This task checks the active cell to see if it is associated with an XML map. If yes, then it creates an XSD file for each schema in the XML map using UTF-16 encoding. Finally, it opens the XSD file in Notepad. To save the XSD file, in NotePad, on the File menu, click Save As to save them on your computer.

If you end up with more than one schema file, the XML map has more than one namespace. To edit and use them in Excel, you must adjust the schema files by:

  • Manually adding paths to any imported data files.
  • Including or redefining references to the secondary schema files such as Schema2, Schema3, and so on.

Refresh All the XML Maps in the Active Workbook

If you have multiple XML maps in a workbook, normally you must select the mapped cell for each XML map and refresh the XML map individually by importing data from the current data-bound source URL for the XML map. This task iterates through all XML maps in a workbook and refreshes the data.

Note   No warning or errors are displayed if the tool cannot refresh the data. It is assumed that you may import some XML map files manually and therefore change names. An error would be cumbersome by interfering with the refresh action and negating the value of this feature. Refresh an XML map individually if you think a problem exists.

Display XML Properties of the Active Cell

This task displays the XPath, XSD DataType, Excel display format, and any namespaces for the XML map of the active cell. This is helpful because much of this information is only available from reading the schema file.

Conclusion

The Excel 2003 XML Tools Add-In version 1.1 helps you, the developer, make working with XML maps, cells, and ranges much easier. It facilitates the creation of XML lists, XSD files, and other tasks related to XSD and XML files.

Additional Resources

The following articles can help you develop more custom XML solutions using Excel: