Refresh connected (imported) data

A cell range, Excel table, PivotTable report, PivotChart report, text file, and Web query can all be connected to an external data source (data source: A stored set of "source" information used to connect to a database. A data source can include the name and location of the database server, the name of the database driver, and information that the database needs when you log on.). You can refresh the data in order to update data from this external data source. Each time you refresh the data, you see the most recent version of the information in the data source, including any changes that were made to the data.

Security  Connections to external data may be currently disabled on your computer. To refresh the data when you open a workbook, you must enable the data connections by using the Trust Center bar, or you must place the workbook in a trusted location.

For more information, see Create, remove, or change a trusted location for your files, Add, remove, or view a trusted publisher, and View my security settings in the Trust Center.

What do you want to do?

Refresh data from a Microsoft Query, the Data Connection Wizard, or Web query

Refresh data from an imported text file

Refresh an offline cube file

Refresh data in an imported XML file

Automatically refresh data when a workbook is opened

Automatically refresh data at regular intervals

Require a password to refresh an external data range

Run a query in the background or while you wait

Check the status of a refresh operation or cancel a refresh

Refresh data from a Microsoft Query, the Data Connection Wizard, or Web query

Do one of the following:

• To refresh a specific data connection in the workbook, click a cell in the external data range. On the Data tab, in the Connections group, click the arrow next to Refresh All, and then click Refresh.
• To refresh all data connections in the workbook, click Refresh All on the Data tab, in the Connections group.

 Note    If you have more than one workbook open, you must repeat the operation in each workbook.

 Top of Page

Refresh data from an imported text file

1. Select the worksheet that contains the imported text file.
2. On the Data tab, in the Connections group, click the arrow next to Refresh, and then click Refresh.
3. In the Import Text File dialog box, select your text file, and then click Import.

 Top of Page

Refresh an offline cube file

Refreshing an offline cube file (offline cube file: A file you create on your hard disk or a network share to store OLAP source data for a PivotTable or PivotChart report. Offline cube files allow you to keep working when you are not connected to the OLAP server.), which re-creates the file by using the most recent data from the server cube, can be time consuming and require a lot of temporary disk space. Start the process at a time when you do not need immediate access to other files, and make sure that you have adequate disk space to save the file again.

1. Click the PivotTable report that is based on the offline cube file.
2. On the Data tab, in the Connections group, click the arrow next to Refresh, and then click Refresh.

 Top of Page

Refresh data in an imported XML file

1. On the worksheet, click a mapped cell to select the XML map that you want to refresh.
2. If the Developer tab is not available, do the following to display it:
   1. Click the Microsoft Office Button , and then click Excel Options.
   2. 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.

3. On the Developer tab, in the XML group, click Refresh Data.

 Top of Page

Automatically refresh data when a workbook is opened

You can refresh an external data range automatically when you open the workbook. Optionally, you can save the workbook without saving the external data so that the file size of the workbook is reduced.

1. Click a cell in the external data range.
2. On the Data tab, in the Connections group, click the arrow next to Refresh, and then click Connection Properties.

3. Click the Usage tab.
4. Under Refresh control, select the Refresh data when opening the file check box.
5. If you want to save the workbook with the query definition but without the external data, select the Remove data from the external data range before saving the workbook check box.

 Note    To refresh the data when the workbook is opened for a PivotTable report, you can also select the Refresh data when opening the file check box. This check box is under the PivotTable Data section on the Data tab of the PivotTable Options dialog box.

For more information, see PivotTable options.

 Top of Page

Automatically refresh data at regular intervals

1. Click a cell in the external data range.
2. On the Data tab, in the Connections group, click the arrow next to Refresh, and then click Connection Properties.

3. Click the Usage tab.
4. Select the Refresh every check box, and then enter the number of minutes between each refresh operation.

 Top of Page

Require a password to refresh an external data range

Stored passwords are not encrypted and are not recommended. If your data source requires a password (password: A way to restrict access to a workbook, worksheet, or part of a worksheet. Excel passwords can be up to 255 letters, numbers, spaces, and symbols. You must type uppercase and lowercase letters correctly when you set and enter passwords.) to connect to it, you can require that users enter the password before they can refresh the external data range. The following procedure does not apply to data that is retrieved from a text file (.txt) or a Web query (.iqy).

Use strong passwords that combine uppercase and lowercase letters, numbers, and symbols. Weak passwords don't mix these elements. Strong password: Y6dh!et5. Weak password: House27. Passwords should be 8 or more characters in length. A pass phrase that uses 14 or more characters is better. For more information, see Help protect your personal information with strong passwords.

It is critical that you remember your password. If you forget your password, Microsoft cannot retrieve it. Store the passwords that you write down in a secure place away from the information that they help protect.

1. Click a cell in the external data range.
2. On the Data tab, in the Connections group, click the arrow next to Refresh, and then click Connection Properties.

3. Click the Definition tab.
4. Clear the Save password check box.

 Note    Excel prompts you for the password only the first time that the external data range is refreshed in each Excel session. The next time that you start Excel, you will be prompted for the password again if you open the workbook that contains the query and then attempt a refresh operation.

 Top of Page

Run a query in the background or while you wait

Running a query in the background enables you to use Excel while the query runs.

 Note    You cannot run an OLAP query in the background.

1. Click a cell in the external data range.
2. On the Data tab, in the Connections group, click the arrow next to Refresh, and then click Connection Properties.

3. Click the Usage tab.
4. Select the Enable background refresh check box to run the query in the background. Clear this check box to run the query while you wait.

 Note    While you are recording a macro (macro: An action or a set of actions that you can use to automate tasks. Macros are recorded in the Visual Basic for Applications programming language.) that includes a query, Excel does not run the query in the background. To change the recorded macro so that the query runs in the background, edit the macro in the Visual Basic Editor (Visual Basic Editor: An environment in which you write new and edit existing Visual Basic for Applications code and procedures. The Visual Basic Editor contains a complete debugging toolset for finding syntax, run-time, and logic problems in your code.). Change the refresh method for the QueryTable object from BackgroundQuery := False to BackgroundQuery := True. For information about editing macros, see Visual Basic for Applications Help.

 Top of Page

Check the status of a refresh operation or cancel a refresh

Do one or more of the following:

• Check the status of a query  A spinning icon appears on the status bar (status bar: A horizontal bar at the bottom of the screen that displays information about the current condition of the program, such as the status of items in the window, the progress of the current task, or information about the selected item.) to indicate that the query is running. Double-click the Background Refresh icon to check the status of the query.
• Stop a background refresh  To stop a query that is running in the background, double-click the Background Refresh icon on the status bar to display the External Data Refresh Status dialog box, and then click Stop Refresh.
• Stop a query  To stop a query from running when Enable background refresh is turned off, press ESC.

 Top of Page

See Also

• Connect to (import) an OLAP database
• Connection properties
• Create, edit, and manage connections to external data
• Exchange (copy, import, export) data between Excel and Access
• Import or export text files
• Remove the data connection from an external data range
• Use Microsoft Query to retrieve external data

Excel > Working with XML

XML for the uninitiated

Excel 2007

You may have heard of Extensible Markup Language (XML), and you may have heard many reasons why your organization should use it. But what is XML, exactly? This article explains the basics of XML — what it is and how it works.

In this article

A brief look at mark up, markup, and tags

So what makes it XML?

A word about well-formed data

A quick look at schemas

A quick look at transforms

A peek at XML in the Microsoft Office System

More information

A brief look at mark up, markup, and tags

To understand XML, it helps to understand the idea of marking up data. People have created documents for centuries, and for just as long they have marked up those documents. For example, school teachers mark up student papers all of the time. They tell students to move paragraphs, clarify sentences, correct misspellings, and so on. Marking up a document is how we define the structure, meaning, and visual appearance of the information in the document. If you have ever used the Track Changes feature in Microsoft Office Word, you have used a computerized form of mark up.

In computing, "mark up" has also evolved into "markup." Markup is the process of using codes called tags (or sometimes tokens) to define the structure, the visual appearance, and — in the case of XML — the meaning of any data.

The HTML code for this article is a good example of computer markup at work. If you browse through it (in Microsoft Internet Explorer, right-click the page, and then click View Source), you will see a mix of readable text and Hypertext Markup Language (HTML) tags, such as

<p>

and

<h2>

. Tags in HTML and XML documents are easy to recognize because they are surrounded by angle brackets. In the source code for this article, the HTML tags do a variety of jobs, such as define the beginning and end of each paragraph (

<p>

...

</p>

) and mark the location of each image.

 Top of Page

So what makes it XML?

HTML and XML documents contain data that is surrounded with tags, but that is where the similarities between the two languages end. In HTML, the tags define the look and feel of your data — the headlines go here, the paragraph starts there, and so on. In XML the tags define the structure and meaning of your data — what the data is.

When you describe the structure and meaning of your data, you make it possible to reuse that data in any number of ways. For example, if you have a block of sales data and each item in the block is clearly identified, you can load just the items that you need into a sales report and load other items into an accounting database. Put another way, you can use one system to generate your data and mark it up with XML tags, and then process that data in any number of other systems, regardless of the hardware platform or operating system. That portability is why XML has become one of the most popular technologies for exchanging data.

Remember these facts as you proceed:

• You cannot use HTML in place of XML. You can, however, wrap your XML data in HTML tags and display it in a Web page.
• HTML is limited to a predefined set of tags that all users share.
• XML allows you to create any tag that you need to describe your data and the structure of that data. For instance, say that you need to store and share information about pets. You can create the following XML code:
• <?xml version="1.0"?>
• <CAT>
•   <NAME>Izzy</NAME>
•   <BREED>Siamese</BREED>
•   <AGE>6</AGE>
•   <ALTERED>yes</ALTERED>
•   <DECLAWED>no</DECLAWED>
•   <LICENSE>Izz138bod</LICENSE>
•   <OWNER>Colin Wilcox</OWNER>

</CAT>

You can see that XML tags make it possible to know exactly what kind of data that you are looking at. For example, you know this is data about a cat, and you can easily find the cat's name, age, and so on. The ability to create tags that define almost any data structure is what makes XML "extensible."

But don't confuse the tags in that code sample with tags in an HTML file. For instance, if you paste that XML structure into an HTML file and view the file in your browser, the results will look something like this:

Izzy Siamese 6 yes no Izz138bod Colin Wilcox

The browser ignores your XML tags and displays just the data.

 Top of Page

A word about well-formed data

You may hear someone from your IT department mention "well-formed" XML. A well-formed XML file conforms to a set of very strict rules that govern XML. If a file doesn't conform to those rules, XML stops working. For example, in the previous code sample, every opening tag has a closing tag, so the sample adheres to one of the rules for being well-formed. If you remove a tag and try to open that file in one of the Office programs, you will see an error message, and the program will stop you from using the file.

You don't necessarily need to know the rules for creating well-formed XML (though they are easy to understand), but you do need to remember that you can share XML data among programs and systems only if that data is well-formed. If you can't open an XML file, chances are that file isn't well-formed.

XML is also platform-independent, meaning that any program built to use XML can read and process your XML data, regardless of the hardware or operating system. For example, with the right XML tags, you can use a desktop program to open and work with data from a mainframe computer. And, regardless of who creates a body of XML data, you can work with the same data in several of the Microsoft Office 2003 and Microsoft Office Professional 2007 programs, including Microsoft Office Access 2007, Microsoft Office Word 2007, Microsoft Office InfoPath 2007, and Microsoft Office Excel 2007. Because it is so portable, XML has become one of the most popular technologies for exchanging data between databases and user desktops.

In addition to tagged, well-formed data, XML systems typically use two additional components: schemas and transforms. The following sections explain how these additional components work.

 Top of Page

A quick look at schemas

Don't let the term "schema" intimidate you. A schema is just an XML file that contains the rules for what can and cannot reside in an XML data file. Schema files typically use the .xsd file name extension, while XML data files use the .xml extension.

Schemas allow programs to validate data. They provide the framework for structuring data and ensuring that it makes sense to the creator and any other users. For example, if a user enters invalid data, such as text in a date field, the program can prompt the user to enter the correct data. As long as the data in an XML file conforms to the rules in a given schema, any program that supports XML can use that schema to read, interpret, and process the data. For example, as shown in the following illustration, Excel and Word can validate the

<CAT>

data against the CAT schema.

Schemas can become complex, and teaching you how to create one is beyond the scope of this article. (Besides, you probably have an IT department that knows how.) However, it helps to know what schemas look like. The following schema defines the rules for the <CAT> ... </CAT> tag set.

<xsd:element name="CAT"> 

  <xsd:complexType> 

    <xsd:sequence>

      <xsd:element name="NAME" type="xsd:string"/>

      <xsd:element name="BREED" type="xsd:string"/>

      <xsd:element name="AGE" type="xsd:positiveInteger"/>

      <xsd:element name="ALTERED" type="xsd:boolean"/>

      <xsd:element name="DECLAWED" type="xsd:boolean"/>

      <xsd:element name="LICENSE" type="xsd:string"/>

      <xsd:element name="OWNER" type="xsd:string"/>       

    </xsd:sequence>

  </xsd:complexType>

</xsd:element>

Don't worry about understanding everything in the sample. Just keep these facts in mind:

• The line items in the sample schema are called declarations. If you needed additional information about an animal, such as its color or markings, chances are that your IT department would add a declaration to the schema. You can change your XML system as your business needs evolve.
• Declarations provide a tremendous amount of control over the data structure. For instance, the

<xsd:sequence>

declaration means that the tags, such as

<NAME>

and

<BREED>

, have to occur in the order that they are listed above. Declarations can also control the types of data that users can enter. For example, the schema above requires a positive number for the cat's age, and Boolean (TRUE or FALSE) values for the ALTERED and DECLAWED tags.

• When the data in an XML file conforms to the rules provided by a schema, that data is said to be valid. The process of checking an XML data file against a schema is called (logically enough) validation. The big advantage to using schemas is that they can help prevent corrupted data. They also make it easy to find corrupted data because XML stops when it encounters a problem.

 Top of Page

A quick look at transforms

As we mentioned earlier, XML also provides powerful ways to use or reuse data. The mechanism for reusing data is called an Extensible Stylesheet Language Transformation (XSLT), or simply, a transform. Transforms are where XML can really get interesting. For example, after you validate a data file against a schema, you can apply a transform that makes the data work as a marketing brochure in Microsoft Office Word 2003 and apply another transform to create a sales report in Office Excel 2007.

You (okay, your IT department) can also use transforms to exchange data between back-end systems, such as databases. For instance, say that Database A stores the sales data in a table structure that works well for the sales department. Database B stores the revenue and expense data in a table structure that is tailored for the accounting department. Database B can use a transform to accept data from A and write that data to the correct tables.

The combination of data file, schema, and transform constitutes a basic XML system. The following illustration shows how such systems typically work. The data file is validated against the schema and then rendered in any number of usable ways by a transform. In this case, the transform deploys the data to a table in a Web page.

The following code sample shows one way to write a transform. It loads the <CAT> data into a table on a Web page. Again, the point of the sample isn't to show you how to write a transform, but to show you one form that a transform can take.

<?xml version="1.0"?>

<xsl:stylesheet version="1.0">

<TABLE>

  <TR>

    <TH>Name</TH>

    <TH>Breed</TH>

    <TH>Age</TH>

    <TH>Altered</TH>

    <TH>Declawed</TH>   

    <TH>License</TH>

    <TH>Owner</TH>

  </TR>

  <xsl:for-each select="CAT">

  <TR ALIGN="LEFT" VALIGN="TOP">

    <TD>

      <xsl:value-of select="NAME"/>

    </TD>

    <TD>

      <xsl:value-of select="BREED"/>

    </TD>

    <TD>

      <xsl:value-of select="AGE"/>

    </TD>

    <TD>

      <xsl:value-of select="ALTERED"/>

    </TD>

    <TD>

      <xsl:value-of select="DECLAWED"/>

    </TD>

    <TD>

      <xsl:value-of select="LICENSE"/>

    </TD>

    <TD>

      <xsl:value-of select="OWNER"/>

    </TD>

  </TR>

</xsl:for-each>

</TABLE>

This sample shows how one type of transform might look when it is coded, but remember that you can just describe what you need from the data in plain English. For example, you can go to your IT department and say that you need to print the sales data for particular regions for the past two years, "and I need it to look this way." Your IT department can then write (or change) a transform to do that job.

What makes all of this even more convenient is that Microsoft and a growing number of other vendors are creating transforms for jobs of all sorts. In the future, chances are that you will be able to download a transform that either meets your needs or that you can adjust to suit your purpose. That means XML will cost less to use over time.

 Top of Page

A peek at XML in the Microsoft Office System

The professional editions of Microsoft Office 2003 and 2007 Office release provide extensive XML support.

• Office Excel 2007, Office Word 2007, and Office PowerPoint 2007 use XML as their default file formats, a change that has several advantages:
  • Smaller file sizes. The new format uses ZIP and other compression technologies to reduce file size by as much as 75 percent compared to the binary formats that are used in earlier versions of Office.
  • Easier information recovery and greater security. XML is human readable, so if a file becomes damaged, you can open the file in Microsoft Notepad or another text reader and recover at least some of your information. Also, the new files are more secure because they cannot contain Visual Basic for Applications (VBA) code. If you use the new format to create templates, any ActiveX controls and VBA macros reside in a separate, more secure section of the file. In addition, you can use tools, such as Document Inspector, to remove any personal data. For more information about using Document Inspector, see the article Remove hidden data and personal information from Office documents.
  • Greater portability and flexibility. Because XML stores data in a text format instead of a proprietary binary format, your customers can define their own schemas and use your data in more ways, all without having to pay royalties. For more information about the new formats, see Introduction to Open XML File Formats.
• Each Office program furnishes a different set of tools. The user interfaces and processes that you follow in Word differ from the user interfaces and processes that you use in Excel or PowerPoint. Why? Because what works for Word doesn't necessarily work for Excel, and so on.
• The Office programs can work with schemas, transforms, and data from other suppliers as long as the XML is well-formed.
• Some of the Office programs use XML in the background, and some, such as Microsoft Office OneNote™, don't support it at all. The best way to learn how an Office program supports XML is to start the online Help for that program and search on XML.

So far so good, but what if you have XML data with no schema? The Office programs that support XML have their own approaches to helping you work with the data. For instance, if you open an XML file in Word without an attached schema, Word displays the tags and data and enables you to apply a transform if, for example, the file's creator or your IT department provides one. At the least, you can read the tags and data in the file.

In contrast, Excel infers a schema if you open an XML file that doesn't already have one. Excel then gives you the option of loading this data into a read-only file or of mapping the data into either an XML list (in Microsoft Office Excel 2003) or an XML table (in Office Excel 2007). You can use the XML lists and tables to sort, filter, or add calculations to the data.

Office Professional 2007 and Microsoft Office 2003 provide the same sets of XML tools. In Office Professional 2007, you must first enable XML support, and then you start the tools from different locations. However, after you start the tools, they work the same in Microsoft Office 2003 and Office Professional 2007. The following steps explain how to start the XML tools for Office Excel 2007 and Office Word 2007.

 Note    Microsoft Office Access 2007 enables its XML tools by default, so you can skip the first steps if you use Access.

Enable the XML tools in Office Excel 2007 and Office Word 2007

1. In Excel or Word, click the Microsoft Office Button , and then click Excel Options or Word Options, depending on the program that you have open.
2. Click Personalize.
3. Under Top options for working with application name, select Show Developer tab in the Ribbon, and then click OK.

 Note    The Ribbon is part of the Microsoft Office Fluent user interface

Start the XML tools in Office Excel 2007 and Office Word 2007

• In either program, on the Developer tab, click any available command in the XML group.

Start the XML tools in Office Access 2007

1. Click the External Data tab.
2. Do one of the following:
   • In the Import group, click XML File.
   • In the Export group, click More, and then click XML File.

 Top of Page

More information

The links in the following sections take you to information about using XML in various Office programs and about writing XML code.

Using XML in 2007 Office release

Using XML in Microsoft Office 2003

Writing XML code

• XML Developer's Center (MSDN)

Books about XML

• Faster Smarter HTML & XML
• HTML & XML for Beginners

• XML Step by Step, Second Edition
• Microsoft® .NET Web Services Step by Step
• XML Pocket Consultant

 Top of Page

Excel > Working with XML

Overview of XML in Excel

Excel 2007

Microsoft Office Excel makes it easy to import Extensible Markup Language (XML) (Extensible Markup Language (XML): A condensed form of Standard Generalized Markup Language (SGML) that enables developers to create customized tags that offer flexibility in organizing and presenting information.) data that is created from other databases and applications, to map XML elements from an XML schema (XML Schema: A formal specification, written in XML, that defines the structure of an XML document, including element names and rich data types, which elements can appear in combination, and which attributes are available for each element.) to worksheet cells, and to export revised XML data for interaction with other databases and applications. Think of these XML features as turning Office Excel into an XML data file generator with a familiar user interface.

In this article

Why use XML in Excel?

XML data and schema files

Key XML and Excel scenarios

The basic process of using XML data in Excel

Working with XML maps

Using the XML Source task pane

Element types and their icons

Working with single-mapped cells

Working with repeating cells in XML tables

XML map security considerations

Importing XML data

Working with an inferred schema

Exporting XML data

Using the Excel macro-enabled Office Open XML Format file

Why use XML in Excel?

XML is a technology that is designed for managing and sharing structured data in a human-readable text file. XML follows industry-standard guidelines and can be processed by a variety of databases and applications. Using XML, application designers can create their own customized tags, data structures, and schemas. In short, XML greatly eases the definition, transmission, validation, and interpretation of data between databases, applications, and organizations.

 Top of Page

XML data and schema files

Excel works primarily with two types of XML files:

• XML data files (.xml), which contain the custom tags and structured data.
• Schema files (.xsd), which contain schema tags that enforce rules, such as data type and validation.

 Note    The XML standard also defines Extensible Stylesheet Language Transformation (XSLT) (XSL Transformation (XSLT): A file that is used to transform XML documents into other types of documents, such as HTML or XML. It is designed for use as part of XSL.) (.xslt) files, which are used to apply styles and transform XML data into different presentation formats. You can apply these transforms before you import XML files into Excel and after you export XML files from Excel. If XSLT files are linked to XML data files that you import into Excel, you do have the option to apply or not apply the formatting before the data is added to the worksheet, but only when you open an XML file by using the Open command on the Microsoft Office Button .

 Top of Page

Key XML and Excel scenarios

By using XML and Excel, you can manage workbooks and data in ways that were previously impossible or very difficult. By using XML maps, you can easily add, identify, and extract specific pieces of business data from Excel documents. For example, an invoice that contains the name and address of a customer or a report that contains last quarter's financial results are no longer just static reports. You can easily import this information from databases and applications, revise it, and export it to the same or other databases and applications.

The following are key scenarios that the XML features are designed to address:

• Extend the functionality of existing Excel templates by mapping XML elements onto existing cells. This makes it easier to get XML data into and out of your templates without having to redesign them.
• Use XML data as input to your existing calculation models by mapping XML elements onto existing worksheets.
• Import XML data files into a new workbook.
• Import XML data from a Web service into your Excel worksheet.
• Export data in mapped cells to XML data files independent from other data in the workbook.

 Top of Page

The basic process of using XML data in Excel

The following diagram shows how the different files and operations work together when you use XML with Excel. Essentially, there are five phases to the process:

Adding an XML schema file (.xsd) to a workbook

Mapping XML schema elements to individual cells or XML tables

Importing an XML data file (.xml) and binding the XML elements to mapped cells

Entering data, moving mapped cells, and leveraging Excel functionality, while preserving XML structure and definitions

Exporting revised data from mapped cells to an XML data file

 Top of Page

Working with XML maps

You can create or open a workbook in Excel, attach an XML schema file (.xsd) to the workbook, and then use the XML Source task pane to map XML elements of the schema to individual cells or tables. After you map the XML elements to your worksheet, you can import and export XML data into and out of the mapped cells.

When you add an XML schema file (.xsd) to your workbook, you create an XML map. In general, XML maps are used to create mapped cells and to manage the relationship between mapped cells and individual elements in the XML schema. In addition, these XML maps are used to bind the contents of mapped cells to elements in the schema when you import or export XML data files (.xml).

There are two kinds of mapped cells that you can create: single-mapped cells and repeating cells (which appear as XML tables). To make designing your worksheet more flexible, you can drag the mapped cells anywhere on a worksheet and into any order — even one different from the XML schema. You can also choose which elements to map and not map.

The following rules about using XML maps are important to know:

• A workbook can contain one or more XML maps.
• You can only map one element to one location in a workbook at a time.
• Each XML map is an independent entity, even if multiple XML maps in the same workbook refer to the same schema.
• An XML map can only contain one root element. If you add a schema that defines more than one root element, you are prompted to choose the root element to use for the new XML map.

 Top of Page

Using the XML Source task pane

You use the XML Source task pane to manage XML maps. To open it, on the Developer tab, in the XML group, click Source. The following diagram shows the main features of this task pane.

 Lists XML maps that were added to the workbook

 Displays a hierarchical list of XML elements in the currently listed XML map

 Sets options when working with the XML Source task pane and the XML data, such as how to preview the data and control headings

 Opens the XML Maps dialog box, which you can use to add, delete, or rename XML maps

 Verifies whether you can export XML data through the current XML map

 Top of Page

Element types and their icons

The following table summarizes each type of XML element that Excel can work with and the icon that is used to represent each type of element.

 Top of Page

Working with single-mapped cells

A single-mapped cell is a cell that has been mapped to a nonrepeating XML element. You create a single-mapped cell by dragging a nonrepeating XML element from the XML Source task pane onto a single cell in your worksheet.

When you drag a nonrepeating XML element onto the worksheet, you can use a smart tag to choose to include the XML element name as a heading above or just to the left of the single-mapped cell, or you can use an existing cell value as a heading.

You can also use a formula in a single-mapped cell, if the cell is mapped to an XML element with an XML Schema Definition (XSD) data type that Excel interprets as a number, date, or time.

 Top of Page

Working with repeating cells in XML tables

XML tables are similar in appearance and functionality to Excel tables. An XML table is an Excel table that has been mapped to one or more XML repeating elements. Each column in the XML table represents an XML element.

An XML table is created when you:

• Use the Import command (in the XML group on the Developer tab) to import an XML data file.
• Use the Open command (on the Microsoft Office Button ) to open an XML data file — and then select As an XML table in the Open XML dialog box.
• Use the From XML Data Import command (from the From Other Sources command button, in the Get External Data group, on the Data tab) to import an XML data file — and then select XML table in existing worksheet or New worksheet in the Import Data dialog box.
• Drag one or more repeating elements from the XML Source task pane to a worksheet.

When you create an XML table, the XML element names are automatically used as column headings. You can change these to any column headings that you want. However, the original XML element names are always used when you export data from the mapped cells.

Two options under the Options button in the XML Source task pane are useful when you work with XML tables:

• Automatically Merge Elements When Mapping  When selected, Excel creates one XML table from multiple fields as they are dropped onto the worksheet. This option works as long as the multiple fields are dropped on the same row, one adjacent to the other. When this option is cleared, each element appears as its own XML table.
• My Data Has Headings  When selected, existing heading data is used as column headings for repeating elements that you map to your worksheet. When this option is cleared, the XML element names are used as column headings.

Using XML tables, you can easily import, export, sort, filter, and print data based on an XML data source. However, XML tables do have some limitations regarding how they can be arranged on the worksheet.

• XML tables are row-based, meaning that they grow from the header row down. You cannot add new entries above existing rows.
• You cannot transpose an XML table so that new entries will be added to the right.

You can use formulas in columns that are mapped to XML elements with an XML Schema Definition (XSD) data type that Excel interprets as a number, date, or time. Just as in an Excel table, formulas in an XML table are filled down the column when new rows are added to the table.

 Top of Page

XML map security considerations

An XML map and its data source information are saved with the Excel workbook, not a specific worksheet. A malicious user can view this map information by using a Microsoft Visual Basic for Applications (VBA) macro. Furthermore, if you save your workbook as a macro-enabled Excel Office Open XML Format File, this map information can be viewed through Microsoft Notepad or through another text-editing program.

If you want to keep using the map information but remove the potentially sensitive data source information, you can delete the data source definition of the XML schema from the workbook, but still export the XML data, by clearing the Save data source definition in workbook check box in the XML Map Properties dialog box, which is available from the Map Properties command in the XML group on the Developer tab.

If you delete a worksheet before you delete a map, the map information about the data sources, and possibly other sensitive information, is still saved in the workbook. If you are updating the workbook to remove sensitive information, make sure that you delete the XML map before you delete the worksheet, so that the map information is permanently removed from the workbook.

 Top of Page

Importing XML data

You can import XML data into an existing XML map in your workbook. When you import data, you bind the data from the file to an XML map that is stored in your workbook. This means that each data element in the XML data file has a corresponding element, in the XML schema, that you mapped from an XML Schema file or inferred schema. Each XML map can only have one XML data binding, and an XML data binding is bound to all of the mappings that were created from a single XML map.

You can display the XML Map Properties dialog box (Click Map Properties in the XML group on the Developer tab.), which has three options, all selected by default, that you can set or clear to control the behavior of an XML data binding:

• Validate data against schema for import and export  Specifies whether Excel validates data against the XML map when importing data. Click this option when you want to ensure that the XML data that you import conforms to the XML schema.
• Overwrite existing data with new data  Specifies whether data is overwritten when you import data. Click this option when you want to replace the current data with new data, for example, when up-to-date data is contained in the new XML data file.
• Append new data to existing XML tables  Specifies whether the contents of the data source are appended to the existing data on the worksheet. Click this option, for example, when you are consolidating data from several similar XML data files into an XML table, or you do not want to overwrite the contents of a cell that contains a function.

When you import XML data, you may want to overwrite some mapped cells but not others. For example, some mapped cells may contain formulas and you don't want to overwrite the formula when you import an XML file. There are two approaches that you can take:

• Unmap the elements that you don't want overwritten, before you import the XML data. After you import the XML data, you can remap the XML element to the cells containing the formulas, so that you can export the results of the formulas to the XML data file.
• Create two XML maps from the same XML schema. Use one XML map for importing the XML data. In this "Import" XML map, don't map elements to the cells that contain formulas or other data that you don't want overwritten. Use another XML map for exporting the data. In this "Export" XML map, map the elements that you want to export to an XML file.

 Note    The ability to import XML data from a Web service by using a Data Retrieval Service Connection (.uxdc) file to connect to a data source is no longer supported in Microsoft Office Excel 2007 through the user interface. If you open a workbook that was created in Office Excel 2003, you can still view the data, but you cannot edit or refresh the source data.

 Top of Page

Working with an inferred schema

If you import XML data without first adding a corresponding XML schema to create an XML map, Excel tries to infer a schema for you based on the tags that are defined in the XML data file. The inferred schema is stored with the workbook, and the inferred schema allows you to work with XML data if an XML schema file isn't associated with the workbook.

When you work with imported XML data that has an inferred schema, you can also customize the XML Source task pane. Select the Preview Data in Task Pane option from the Options button to display the first row of data as sample data in the element list, if you imported XML data associated with the XML map in the current session of Excel.

You cannot export the Excel inferred schema as a separate XML schema data file (.xsd). Although there are XML schema editors and other methods for creating an XML schema file, you may not have convenient access to them or know how to use them. As an alternative, you can use the Excel 2003 XML Tools Add-in Version 1.1, which can create a schema file from an XML map. For more information, see Using the Excel 2003 XML Tools Add-in Version 1.1.

 Top of Page

Exporting XML data

You export XML data by exporting the contents of mapped cells on the worksheet. When you export data, Excel applies the following rules to determine what data to save and how to save it:

• Empty items are not created when blank cells exist for an optional element, but empty items are created when blank cells exist for a required element.
• Unicode Transformation Format-8 (UTF-8) encoding is used to write the data.
• All namespaces are defined in the Root XML element.
• Excel overwrites existing namespace prefixes. The default namespace is assigned a prefix of ns0. Successive namespaces are designated ns1, ns2 to ns<count> where <count> is the number of namespaces written to the XML file.
• Comment nodes are not preserved.

You can display the XML Map Properties dialog box (Click Map Properties in the XML group on the Developer tab.) and then use the Validate data against schema for import and export option (active by default) to specify whether Excel validates data against the XML map when exporting data. Click this option when you want to ensure that the XML data you export conforms to the XML schema.

 Top of Page

Using the Excel Macro-enabled Office Open XML Format File

You can save an Excel workbook in a variety of file formats, including the Excel macro-enabled Office Open XML Format File (.xlsm). Excel has a defined XML schema that defines the contents of an Excel workbook, including XML tags that store all workbook information, such as data and properties, and define the overall structure of the workbook. Custom applications can use this Excel macro-enabled Office XML Format File. For example, developers may want to create a custom application to search for data in multiple workbooks that are saved in the this format and create a reporting system based on the data found.

 Top of Page

See Also

• Append or overwrite mapped XML data
• Export XML data
• Import XML data
• Map and unmap XML elements
• Overview of XML in Excel
• Validate XML data against an XML schema
• XML for the uninitiated
• XML Schema Definition (XSD) data type support

Excel > Working with XML

Map and unmap XML elements

Excel 2007

To import and export XML data files, you must create an XML map, and then map and unmap XML elements to cells to get the results that you want.

What do you want to do?

Locate XML Schema and XML data files

Create an XML map

Map XML elements

Unmap XML elements

Locate XML Schema and XML data files

There are several ways to obtain XML Schema and XML data files.

Get the files from another database or application

Often, you have access to XML Schema (.xsd) (XML Schema: A formal specification, written in XML, that defines the structure of an XML document, including element names and rich data types, which elements can appear in combination, and which attributes are available for each element.) and XML data (.xml) files that were created in another database or application. For example, a line-of-business application may export data into XML file format, a commercial Web site or Web service may supply an XML file, or a customized application developed by your IT department may create an XML file as one of its functions.

Convert a text file

If you have a text file that you want to use, you can convert the text file to an XML file and then map it in Microsoft Office Excel 2007. For example, you can use Microsoft Office Access and Microsoft Office Excel to convert a text file to an XML file:

1. Start Access.
2. Create a linked table to the text file that you want to convert.
3. Export the data from the linked table to an XML data file and an XML Schema file.
4. Exit Access.
5. Start Excel.
6. Create an XML map based on the XSD file that you exported from Access. If the Multiple Roots dialog box appears, make sure that you choose dataroot to create an XML table. For more information, see Create an XML map.
7. Create an XML table by mapping the dataroot element. For more information, see Map XML elements.
8. Import the XML file that you exported from Access. For more information, see Import XML data.

Use sample files

If you don't have convenient access to XML files, the following sample XML files contain basic XML elements and structures that you can use to test XML maps.

Sample XML data (Expenses.xml)

Sample XML Schema (Expenses.xsd)

How to save the samples as files

1. Start Microsoft Notepad.
2. Select the sample text, and then copy and paste the sample text to Notepad.
3. Save the file with the file name and extension that you want.
4. Exit Notepad.

 Top of Page

Create an XML map

You create an XML map by adding an XML Schema to a workbook. The schema can be copied from an XML Schema file (.xsd), or Excel can attempt to infer one from an XML data file (.xsd).

1. If the Developer tab is not available, do the following to display it:
   1. Click the Microsoft Office Button , and then click Excel Options.
   2. 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.

2. On the Developer tab, in the XML group, click Source.

The XML Source task pane is displayed.

3. Click XML Maps.
4. Click Add.
5. In the Look in list, click the drive, folder, or Internet location that contains the file that you want to open.
6. Click the file, and then click Open. If you open:
   1. An XML Schema file, XML creates an XML map based on the XML Schema.

If the Multiple Roots dialog box appears, choose one of the root nodes that is defined in the XML Schema file.

2. An XML data file, Excel tries to infer the XML Schema from the XML data, and then creates an XML map.
7. Click OK.

The XML map is displayed in the XML Source task pane.

Issue: When I add an XML map to my workbook, a message tells me that the XML Schema cannot be added to my workbook.

 Top of Page

Map XML elements

You map XML elements to single-mapped cells and repeating cells in XML tables so that you can create a relationship between the cell and the XML data element in the XML Schema.

1. If the Developer tab is not available, do the following to display it:
   1. Click the Microsoft Office Button , and then click Excel Options.
   2. 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.
2. On the Developer tab, in the XML group, click Source.

The XML Source task pane is displayed.

3. To map one or more elements to your worksheet, select the elements in the XML Source task pane. To select nonadjacent elements, click one element, and then hold down CTRL and click each element that you want to map.
4. To map the elements, do the following:
   1. Right-click the selected elements, and then click Map an element on the shortcut menu.
   2. In the Map XML elements dialog box, select a cell, and then click OK.

Tip  You can also drag the selected elements to the worksheet location where you want them to appear.

Each element appears in bold type in the XML Source task pane to indicate that the element is mapped.

5. Decide how you want handle labels and column headings:
   1. When you drag a nonrepeating XML element onto the worksheet to create a single-mapped cell, a smart tag with three commands is displayed, which you can use to control the placement of the heading or label:

My Data Already Has a Heading  Click this option to ignore the XML element heading, because the cell already has a heading (to the left of the data or above the data).

Place XML Heading to the Left  Click this option to use the XML element heading as the cell label (to the left of the data).

Place XML Heading Above  Click this option to use the XML element heading as the cell heading (above the data).

2. When you drag a repeating XML element onto the worksheet to create repeating cells in an XML table, the XML element names are automatically used as column headings for the table. However, you can change the column headings to any headings that you want by editing the column header cells.

In the XML Source task pane, you can click Options to further control XML table behavior:

Automatically Merge Elements When Mapping  When this check box is selected, XML tables are automatically expanded when you drag an element to a cell adjacent to the XML table.

My Data Has Headings  When this check box is selected, existing data can be used as column headings when you map repeating elements to your worksheet.

Issue: All of the XML commands are dimmed, and I cannot map XML elements to any cells.

Issue: I cannot copy an XML table to another workbook.

1. Add an XML map to the new workbook by using the .xml or .xsd file that you used to create the original XML map.

 Note    You should save these files if you want to add XML maps to other workbooks.

2. Map the XML elements to the table to make it an XML table.

Issue: When I map a repeating element to a merged cell, Excel unmerges the cell.

 Top of Page

Unmap XML elements

You unmap XML elements from a mapped cell or XML table because you decide not to use them, or because you don't want the contents of cells to be overwritten when you import XML data. For example, you may want to unmap an XML element if it is currently mapped to a single-mapped cell or repeating cells that contain formulas and you don't want to overwrite the formula when you import an XML file. After you import XML data, you can remap the XML element to the cells that contain the formulas, so that you can export the results of the formulas to the XML data file.

1. If the Developer tab is not available, do the following to display it:
   1. Click the Microsoft Office Button , and then click Excel Options.
   2. 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.
2. On the Developer tab, in the XML group, click Source.

The XML Source task pane is displayed.

3. Right-click the element name.
4. Click Remove an element on the shortcut menu.

 Top of Page

See Also

• Delete XML map information from a workbook
• Export XML data
• Import XML data
• Overview of XML in Excel
• Rename an XML map
• Validate XML data against an XML schema
• XML for the uninitiated
• XML Schema Definition (XSD) data type support

Excel > Working with XML

Append or overwrite mapped XML data

Excel 2007

1. Click a mapped cell to select the XML map that you want.
2. If the Developer tab is not available, do the following to display it:
   1. Click the Microsoft Office Button , and then click Excel Options.
   2. 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.

3. On the Developer tab, in the XML group, click Map Properties.

4. In the XML Map Properties dialog box, click one of the following options:
   1. Overwrite existing data with new data  When XML data is refreshed or reimported into a map, existing data in the mapped cells, whether a single-mapped cell (single-mapped cell: A cell that has been linked to a non-repeating element in an XML map.) or an XML table, is overwritten by the new data.
   2. Append new data to existing XML tables  When XML data is refreshed or reimported into a map:
      • For an XML table, the new data is appended to the end of an XML table.
      • For a single mapped cell, the current data is not overwritten (and no data is appended).

See Also

• Import XML data
• Map and unmap XML elements
• Overview of XML in Excel
• XML for the uninitiated

Excel > Working with XML

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

Step 2: Convert a cell range to an XML Table

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

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

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 , and then click Excel Options.
4. Click the Add-Ins category.
5. In the Manage box, click Excel Add-ins, and then click Go.
6. 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.

7. Verify that the XmlTools check box is selected in the Add-Ins available list, and then click OK to load the add-in.
8. 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

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.

4. 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

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 , and then click Excel Options.
   2. 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.

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

 Note    If a small Export XML dialog box with only an Open 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.

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

 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.

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

 Top of Page

See Also

• Export XML data
• Format the data and layout of an XML table
• Load or unload add-in programs
• Map and unmap XML elements
• Overview of XML in Excel
• XML Schema Definition (XSD) data type support

Excel > Working with XML

Delete XML map information from a workbook

Security   An XML map and its data source information are saved with the workbook, not with a specific worksheet. A malicious user could view this potentially sensitive map information by using a Visual Basic for Applications (VBA) macro. Furthermore, if you save your workbook as a Macro-enabled Excel Open XML Format File, this map information can be viewed through Microsoft Notepad or through another text-editing program.

What do you want to do?

Delete the data source definition of the XML map from the workbook

Delete an XML map from a workbook

Delete the data source definition of the XML map from the workbook

If you want to keep using the map information but remove the potentially sensitive data source information, you can delete the data source definition of the XML schema from the workbook. In this case, you can still export the XML data.

 Caution    This procedure permanently removes the current data source definition from the XML map.

1. Click a mapped cell to select the XML map that you want.
2. If the Developer tab is not available, do the following to display it:
   1. Click the Microsoft Office Button , and then click Excel Options.
   2. 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.

3. On the Developer tab, in the XML group, click Map Properties.

4. In the XML Map Properties dialog box, under Data source, clear the Save data source definition in workbook check box.

This option is selected by default. Clearing this option does not delete any data from the worksheet.

Important   The data source definition contains connection information about the imported XML data. Removing this information prevents others from viewing the information, but you can still export the mapped data to an XML file. However, if you reimport the XML data file, the data source definition is once again saved with the file.

 Top of Page

Delete an XML map from a workbook

If you delete a worksheet before deleting an XML map, the map information, about the data sources and possibly other sensitive information, is still saved in the workbook. If you are updating the workbook to remove sensitive information, make sure that you delete the XML map before you delete the worksheet so that the map information is permanently removed from the workbook.

1. If the Developer tab is not available, do the following to display it:
   1. Click the Microsoft Office Button , and then click Excel Options.
   2. 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.
2. On the Developer tab, in the XML group, click Source.

3. In the XML Source task pane, click XML Maps.

The XML Maps dialog box is displayed.

4. Select the XML map that you want to delete.
5. Click Delete, and then click OK.

 Note     Deleting an XML map removes the XML map only and does not remove any data that the XML map is currently mapped to.

 Top of Page

See Also

• Overview of XML in Excel
• Rename an XML map
• XML for the uninitiated

Excel > Working with XML

Export XML data

Excel 2007

After you have imported XML data, mapped the data to cells in your worksheet, and made changes to the data, you often want to export or save the data to an XML file. There are two ways to export XML data from a Microsoft Office Excel 2007 worksheet:

• We recommend using the Export command in the XML group on the Developer tab to get XML data out of your worksheet.
• For backward compatibility with earlier XML functionality, you can still use the Other Formats command on the Save As submenu of the Microsoft Office Button .

In this article

The basic process of using XML data in Excel

Export XML data in mapped cells to an XML data file

Save XML data in mapped cells to an XML data file

Issues with exporting XML data

The basic process of using XML data in Excel

The following diagram shows how the different files and operations work together when you use XML with Excel. Essentially, there are five phases to the process:

Adding an XML schema file (.xsd) to a workbook

Mapping XML schema elements to individual cells or XML tables

Importing an XML data file (.xml) and binding the XML elements to mapped cells

Entering data, moving mapped cells, and leveraging Excel functionality, while preserving XML structure and definitions

Exporting revised data from mapped cells to an XML data file

 Top of Page

Export XML data in mapped cells to an XML data file

1. If the Developer tab is not available, do the following to display it:
   1. Click the Microsoft Office Button , and then click Excel Options.
   2. 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.

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

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

The Export XML dialog box will only appear if an XML table is not selected and if the workbook contains more than one XML map.

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

 Note    There may be headings or labels in your worksheet that are different from the XML element names in the XML map. However, the XML element names are always used when you export or save XML data from the worksheet.

 Top of Page

Save XML data in mapped cells to an XML data file

1. Click the Microsoft Office Button , and then point to the arrow next to Save.

This step ensures that any changes that are made to your workbook won't be lost when the workbook is saved as an XML data file.

2. On the Microsoft Office Button , point to the arrow next to Save As, and then click Other Formats.
3. In the File name box, type a name for the XML data file.
4. In the Save as type list, click XML Data, and then click Save.
5. If you receive an alert stating that saving the file as XML data may result in the loss of features, click Continue.

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

The Export XML dialog box will only appear if an XML table is not selected and the workbook contains more than one XML map.

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

 Note    There may be headings or labels in your worksheet that are different from the XML element names in the XML map. However, the XML element names are always used when you export or save XML data from the worksheet.

 Top of Page

Issues with exporting XML data

The following sections explain issues that you can encounter when you export XML data.

I get a message saying that the map can be exported but some required elements are not mapped.

There may be several reasons for this message:

• The XML map that is associated with this XML table contains one or more required elements that are not mapped to the XML table.

The hierarchical list of elements in the XML source task pane indicates required elements by the red asterisk on the top-right corner of the icon to the left of each element. To map a required element, drag it to the worksheet location where you want it to appear.

• The element is a recursive structure.

A common example of a recursive structure is a hierarchy of employees and managers in which the same XML elements are nested several levels. Although you may have mapped all of the elements in the XML Source task pane, Excel does not support recursive structures more than one level deep and therefore cannot map all of the elements.

• The XML table contains mixed content.

Mixed content occurs when an element contains a child element and simple text outside of a child element. One common case is where formatting tags (such as the bold tags) are being used to mark up data within an element. Although the child element (if Excel supports it) can be displayed, the text content will be lost when the data is imported, is not available when the data is exported, and therefore cannot be round-tripped.

When I export XML data, a message tells me that the XML maps in my workbook are not exportable.

An XML mapping cannot be exported if the mapped element’s relationship with other elements cannot be preserved. This relationship may not be preserved for the following reasons:

• The schema definition of a mapped element is contained within a sequence with the following attributes:
  • The maxoccurs attribute is not equal to 1.
  • The sequence has more than one direct child element defined, or has another compositor as a direct child.
• Nonrepeating sibling elements with the same repeating parent element are mapped to different XML tables.
• Multiple repeating elements are mapped to the same XML table, and the repetition is not defined by an ancestor element.
• Child elements from different parents are mapped to the same XML table.

Additionally, the XML mapping cannot be exported if it contains one of the following XML schema constructs:

• List of lists  One list of items contains a second list of items.
• Denormalized data  An XML table contains an element that has been defined in the schema to occur once (the maxoccurs attribute is set to 1). When you add such an element to an XML table, Excel fills the table column with multiple instances of the element.
• Choice  A mapped element is part of a <choice> schema construct.

When I save my workbook as XML data, a message tells me that Excel cannot save the workbook because it does not contain any XML mappings.

Excel cannot save your workbook in the XML Data file unless you create one or more mapped ranges (mapped range: A range in an XML list that has been linked to an element in an XML map.).

If you added an XML map to your workbook, follow these steps to map XML elements to your worksheet:

1. If the Developer tab is not available, do the following to display it:
   1. Click the Microsoft Office Button , and then click Excel Options.
   2. 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.
2. On the Developer tab, in the XML group, click Source.

The XML Source task pane is displayed.

3. To map one or more elements to your worksheet, select the elements in the XML Source task pane. To select nonadjacent elements, click one element, and then hold down CTRL and click each element.
4. Drag the selected elements to the worksheet location where you want them to appear.

If you haven't yet added an XML map to your workbook, or if your data is not contained within an XML table, save your workbook in the Excel Macro-Enabled Workbook file format (.xlsm).

 Top of Page

See Also

• Append or overwrite mapped XML data
• Create an XML data file and XML schema file from worksheet data
• Import XML data
• Map and unmap XML elements
• Overview of XML in Excel
• Validate XML data against an XML schema
• XML for the uninitiated
• XML Schema Definition (XSD) data type support

Excel > Working with XML

Turn around expense reports in a snap with Excel 2007 and XML

Excel 2007

Two words that may make you run for cover: expense reports. You dread them, you defer them, and you despise them, especially when Accounting returns them for minute corrections.

Put those fears aside and learn to positively love expense reports. Impossible, you say? Not if you team up with Microsoft Office Excel 2007 and Extensible Markup Language (XML) to turn those expense reports around in no time flat. This article explains how you can automate expense reporting by using XML data in an Excel worksheet. We even provide a sample expense report, sample XML schema and data, and sample code to get you started.

In this article

Expense reports: the old-fashioned and new-fashioned way

How expense reports get done from the top down

Take a stroll through the process

The bottom line

Expense reports: the old-fashioned and new-fashioned way

Are you handling your expense reports by printing an Excel template, writing in your expenses by hand, and then turning it in with crumpled receipts and crossed fingers? I sure hope not. But even if your Information Technology (IT) department has expense report automation at the bottom of its backlog, don't despair. You can quickly push expense report automation up that stack of requested applications.

How, you ask? Two words that may make you leap for joy: smart client. In a traditional client/server application, data is stored on a network server database (the back end), and forms are displayed on a client computer (the front end). A smart client adds built-in support for XML, leverages the full power of a personal computer, offers a familiar user interface, and has a rich set of features that you can use to analyze, publish, report, and share data. Sounds like we just defined Excel, not to mention the Microsoft Office System.

Most users are already familiar with entering data into Excel. Besides, an Excel-based form is a natural for expense reports. You can take advantage of formulas (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).), Excel tables, comments, data validation (data validation: An Excel feature that you can use to define restrictions on what data can or should be entered in a cell, and to display messages that prompt users for correct entries and notify users about incorrect entries.), cell and worksheet protection (protect: To make settings for a worksheet or workbook that prevent users from viewing or gaining access to the specified worksheet or workbook elements.), and even smart tags (smart tags: Data recognized and labeled as a particular type. For example, a person's name or the name of a recent Microsoft Outlook e-mail message recipient is a type of data that can be recognized and labeled with a smart tag.), to name a few features. And then, of course, there's XML: the open, industry standard that makes it easy to define, transmit, validate, and interpret structured data between applications and organizations.

Now toss the custom XML Schema (XML Schema: A formal specification, written in XML, that defines the structure of an XML document, including element names and rich data types, which elements can appear in combination, and which attributes are available for each element.) feature into the mix. This makes it possible to map an XML schema file (.xsd) to specific cells in a worksheet, save mapped cell data from your worksheet into an XML data file, or import an XML data file and bind the data to these mapped cells. You can think of Excel as an XML data file generator, a powerful front end to new or even earlier applications. In many cases, you won't even need to write code, either Microsoft Visual Basic for Applications (VBA) or Extensible Stylesheet Language Transformation (XSLT). If you do need code, you don't need much.

By combining the time-tested features of Excel with the XML schema-mapping features in Excel, you can fill out that expense report and submit it faster than you can say "Show me the money!" IT folks take notice: Custom XML schemas and XML data make it simple to retrofit an existing front end and connect with back-end databases. Your custom applications don't have to be continually redesigned from the ground up.

 Top of Page

How expense reports get done from the top down

The following diagram illustrates a typical workflow for expense report processing. This workflow uses Excel as a smart client on the front end and XML to communicate with a back-end database on a server.

You fill out the expense report by using an Excel worksheet that has been mapped to a custom XML schema and save the data as an XML data file.

You submit the expense report data in the XML data file to the back-end database.

Accounting scrupulously checks the expense report for accuracy and adherence to internal business rules and spending limits.

If Accounting approves, you get a gold star (and a reimbursement check).

If Accounting does not approve (that fancy restaurant didn't pass muster), they send an e-mail message back to you telling you to adjust the expense report and resubmit it.

Let's look at the "Fill out expense report" step more closely.

First, an IT developer creates a custom XML schema, defined in an XML schema file, and maps the appropriate cells in the Excel worksheet. Note that this is done only once. In practice, users obtain the expense report with the schema already mapped, often by downloading it from an internal Web site.

Then, users enter identification information and line-by-line details into an Excel worksheet that looks just like an expense report.

Finally, after completing the expense report, users save the data to an XML file.

Take a stroll through the process

Got motivation? Then put your walking shoes on and learn how to use the sample Excel workbook and XML schema file, step by step.

First, you'll add the schema to the Excel workbook and map specific data elements to corresponding cells in the expense report worksheet. Then, you can practice typing data into the expense report, and save the data to an XML file for further processing. Finally, you'll learn how you can submit an expense report and how to revise it, if necessary.

Examine ExpenseReport.xlsx

Now, let's look at a real Excel workbook (.xlsx) just to show we're not pulling your leg. You can use this workbook as a model for designing your own expense report workbook.

To download the workbook, do the following:

1. Click the link Expense Report Template.
2. In the File Download dialog box, click Save.

 Note    Do not click Open because doing so will display a warning message and open a read-only version of the workbook.

3. In the Save As dialog box, click Save.
4. When the file download is complete, click Open.
5. Save the file as ExpenseReport.xlsx.

The expense report is an Excel worksheet that takes advantage of built-in features of Excel to streamline filling out an expense report.

Use comments to provide context-sensitive assistance for each cell.

Link users to an internal Web site for detailed information about how to use the expense report.

Visually highlight cells that contain formulas.

Lock cells that contain formulas, to protect them from users who are accident-prone.

Locally validate data, such as special codes.

In your application, make sure that IT developers protect the entire workbook and create a password before users obtain the workbook, so no one jimmies those locked cells.

Savvy IT developers can even add smart tags to the Excel template and turn a smart client into a bona-fide genius. For example, you can use a smart tag to pre-load the expense report with user information, validate cost center codes from an external database, or link to Web pages for detailed information about a cell.

Examine ExpenseReportSchema.xsd

The following XML schema file defines the data that you want to capture in the expense report and send to Accounting (the folks with the white shirts and green eyeshades). Usually, IT developers create this schema because they understand best what data needs to be defined and captured.

ExpenseReportSchema.xsd

For detailed information about how to create an XML schema, see XML Schema Part 0: Primer, published by the World Wide Web Consortium (W3C).

Add the sample schema to the workbook

1. Save the sample schema to a file called, ExpenseReportSchema.xsd.
2. Open ExpenseReport.xlsx.
3. If the Developer tab is not available, do the following to display it:
   1. Click the Microsoft Office Button , and then click Excel Options.
   2. 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.

4. On the Developer tab, in the XML group, click Source.

The XML Source task pane is displayed.

5. Click XML Maps.
6. Click Add.
7. In the Look in list, click the drive, folder, or intranet location that contains ExpenseReport.xsd.
8. Click ExpenseReport.xsd, and then click Open.
9. Click OK to close the XML Maps dialog box.

The XML map is displayed in the XML Source task pane. The task pane displays a simple hierarchical view of the XML schema that makes it easy to view and map elements to cells.

Map the nonrepeating data to single-mapped cells

Nonrepeating data, such as information about the user or the purpose of the trip, is mapped to single cells. In an Excel worksheet, cells that contain nonrepeating data are called single-mapped cells.

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

The XML Source task pane is displayed.

2. For each of the elements — user information (under ns1:Meta) and summary data (under ns1:Summary) — select the elements that you want in the XML Source task pane, and drag them to their corresponding cells.

3. By default, a single-mapped cell is visually identified with a blue border.
4. In general, when you drag a nonrepeating XML element onto the worksheet, you can choose to include the element name as a heading above or just to the left of the single-mapped cell. In the expense report, the existing cell value already has a heading or label.

Map the repeating data to an XML table

Repeating data elements, such as the line item information, are mapped to rows of data. In an Excel worksheet, rows that contain repeating data are XML tables.

1. Select the ns1:ExpenseItem element in the XML Source task pane, and drag it to the B10 cell on the worksheet.
2. To clear the default Excel table formatting, on the Design tab, in the Table Styles group, click the More arrow in the bottom right-hand corner, and then click Clear at the bottom of the Gallery window.

Congratulations! You just mapped all the line item columns in a single step, and you also created an XML table. An XML table has the added advantage of automatically expanding when users add new rows to the table in the worksheet.

 Note    Repeating data elements are mapped together into an XML table when the XML Map property Automatically Merge Elements When Mapping is selected (this is the default value). To modify this and other XML map properties, right-click on the XML table, click XML, and then click XML Map Properties.

Enter data into the Excel expense report

Users of the expense report can now organize their crumpled receipts and type the data into the spreadsheet. Still got motivation? Type the following sample data into the expense report and feel the love.

Submit the expense report data

After you submit the XML data file to a pickup folder, the data can be processed in many different ways, including the system that your enterprise uses to handle expense reports, of which there can be quite a variety. For example, the XML data file could be sent to the Accounting department, perhaps to a dedicated e-mail account that retrieves the XML data and stores it in an equally dedicated folder. The XML file could also be imported into any number of databases, including Microsoft Office Access, Microsoft SQL Server, and third party databases. Or, you may have an Office SharePoint Server custom workflow operation that uses Microsoft Office InfoPath and Forms Server. There are many possibilities, but this attests to the remarkable flexibility of XML.

Here are two ways you can submit the data, one without code, and the other with code.

Save the expense report data as an XML data file without code

When you manually type (or import) data into a mapped workbook, that data becomes part of the workbook when it is saved. But it is not automatically saved as an XML data file. To create an XML data file, do the following:

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

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

The Export XML dialog box will only appear if an XML table is not selected and if the workbook contains more than one XML map.

2. If the Export XML dialog box appears, click the XML map that you want to use and then click OK.

The Export XML dialog box appears only if an XML table is not selected and the workbook contains more than one XML map.

3. In the File name box, type ExpenseReportData.xml for the XML data file.
4. Click Export.

If all is well, your data should look like the following file.

ExpenseReportData.xml

Save the expense report data as an XML data file with code

With just a few lines of VBA code, you or your IT developer can add a Submit button that would export the data for you and send it to a pickup folder for subsequent processing and verification. Here's an example that creates a time-stamped XML data file and saves the workbook with the same timestamp so you and Accounting can keep track of each go round:

VBA code example

To add this code, do the following:

1. In ExpenseReport.xlsx, on the Developer tab, in the Code group, click Macros.
2. In the Macro name box, enter ExportXMLToFile, and then click Create.
3. Copy and paste the code above into the ExportXMLToFile Sub procedure.
4. Close the VBA window.
5. On the Developer tab, in the Controls group, click Insert, and then under Form Controls, click Button .
6. Click the worksheet location where you want the upper-left corner of the button to appear.
7. To assign the ExportXMLToFile macro to the button, in the Assign Macro dialog box, select ExportXMLToFile, and then click OK.
8. To specify a label for the button, replace the default text with the text, "Submit", and then click a blank cell outside the button.
9. To save the workbook as a macro-enabled workbook, Click the Microsoft Office Button , point to Save As, click Excel Macro-Enabled Workbook, and then save it to ExpenseReport.xlsm.
10. To test the macro, click the button, and then follow the directions.

Revise the expense report data

If you need to make changes to your expense report later, because Accounting sent you an e-mail with corrections, you can revise it by opening the workbook, Expensereport.xlsm, making the necessary changes in the workbook, and then resubmitting the data. It's that easy.

The bottom line

Expense reports in your company are now a breeze. At that recent business conference in Chicago, you were a hero with your fellow road warriors. Why, just last week, the entire IT department delivered flowers to your office and personally thanked you for lightening their load. The other day, you courted an important customer with an expensive dinner and finally closed that big sale. This morning, even your boss patted you on the back, complimented you on that paisley tie, and said…

"Don't forget to fill out those expense reports."

See Also

• Export XML data
• Format the data and layout of an XML table
• Import XML data
• Map and unmap XML elements
• Overview of XML in Excel
• XML Schema Definition (XSD) data type support

Excel > Working with XML

Format the data and layout of an XML table

1. If the Developer tab is not available, do the following to display it:
   1. Click the Microsoft Office Button , and then click Excel Options.
   2. 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.

2. Select a cell in the XML table.
3. Click on a mapped cell to select the XML map that you want.
4. On the Developer tab, in the XML group, click Map Properties.

5. In the XML Map Properties dialog box, do one or more of the following:

Adjust column width  

1. To automatically adjust column widths for best fit when you refresh XML data, select this option. This is the default option.

 Note     The maximum column width is two-thirds of the width of the screen.

2. To prevent the automatic adjustment of column widths, for best fit, when you refresh XML data, clear this option.

Preserve column filter  

3. To preserve sort order and applied filters when you refresh XML data, select this option. This is the default option.
4. To ignore sort order and applied filters when you refresh XML data, clear this option.

Preserve number formatting  

5. To preserve number formatting when you refresh XML data, select this option. This is the default option.
6. To ignore number formatting when you refresh XML data, clear this option.

See Also

• Change the column width and row height
• Import XML data
• Map and unmap XML elements
• Overview of XML in Excel
• Wrap text in a cell
• XML for the uninitiated

Excel > Working with XML

Import XML data

Excel 2007

Before you import XML data into a worksheet, consider that there are several methods that you can use to import XML data and several commands that you can use to import XML data. Use the following table to help you decide which method of importing XML data is the best approach for you.

Import an XML data file into mapped cells

1. If the Developer tab is not available, do the following to display it:
   1. Click the Microsoft Office Button , and then click Excel Options.
   2. 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.
2. Select one of the mapped cells that you want to import XML data into.
3. On the Developer tab, in the XML group, click Import.

The Import XML dialog box is displayed.

4. On a computer that is running Windows Vista  
   1. In the Address bar, click the drive, folder, or Internet location that contains the XML data file (.xml) that you want to import.

On a computer that is running Microsoft Windows XP  

2. In the Look in list, click the drive, folder, or Internet location that contains the XML data file (.xml) that you want to import.
5. Click the file, and then click Import.

 Top of Page

Import an XML data file as an XML table

1. If the Developer tab is not available, do the following to display it:
   1. Click the Microsoft Office Button , and then click Excel Options.
   2. 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.

2. On the Developer tab, in the XML group, click Import.

The Import XML dialog box is displayed.

3. On a computer that is running Windows Vista  
   1. In the Address bar, click the drive, folder, or Internet location that contains the XML data file (.xml) that you want to import.

On a computer that is running Microsoft Windows XP  

2. In the Look in list, click the drive, folder, or Internet location that contains the XML data file (.xml) that you want to import.
4. Click the file, and then click Import.

If the XML data file does not refer to a schema, then Microsoft Office Excel infers the schema from the XML data file.

The Import Data dialog box is displayed.

5. Select one of the following options:
   1. XML table in existing worksheet

The contents of the XML data file are imported into an XML table in the existing worksheet at the specified cell location.

2. XML table in new worksheet

The contents of the file are imported into an XML table in a new worksheet starting at cell A1. The schema of the XML data file is displayed in the XML Source task pane.

6. If the XML data file does not refer to a schema, then Excel infers the schema from the XML data file.
7. To control the behavior of XML data, such as data binding, format, and layout, click Properties, which displays the XML Map properties dialog box. For example, existing data in a mapped range will be overwritten when you import data by default, but you can change this.

 Top of Page

Import multiple XML data files

1. If the Developer tab is not available, do the following to display it:
   1. Click the Microsoft Office Button , and then click Excel Options.
   2. 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.
2. Do one of the following:
   1. To import multiple XML files into a single set of mapped cells, select one of the mapped cells that you want to import XML data to.
   2. To import multiple XML files into multiple sets of mapped cells, make sure that no mapped cell is selected by clicking a cell anywhere on the worksheet that is not mapped.
3. On the Developer tab, in the XML group, click Import.

The Import XML dialog box is displayed.

4. On a computer that is running Windows Vista  
   1. In the Address bar, click the drive, folder, or Internet location that contains the XML data file (.xml) that you want to import.

On a computer that is running Microsoft Windows XP  

2. In the Look in list, click the drive, folder, or Internet location that contains the XML data file (.xml) that you want to import.
5. Select the files as follows:
   1. If the files are contiguous, press SHIFT, and then click the first and the last file in the list.
   2. If the files are not contiguous, press CTRL, and then click each file that you want to import in the list.
6. Click Import.

If you chose step:

1. 2a.    All of the data from the XML files is imported and appended to the mapped cells.
2. 2b.   In the Importing <filename>.xml dialog box, for each file, select the XML map that corresponds to the XML data file that you are importing.

To use a single map for all of the selected files that are not yet imported, select Use this XML map for all selected files of this schema.

 Top of Page

Import multiple XML data files as external data

If you want to import multiple XML files that use the same namespace but different XML schemas, you can use the From XML Data Import command, which was designed so that multiple XML files with the same namespace can use multiple XML schemas. Excel creates a unique XML map and XML mapping for each XML data file that you import.

 Note    If you are importing multiple XML files that do not define a namespace, these XML files are treated as if they use the same namespace.

1. On the Data tab, in the Get External Data group, click From Other Sources, and then click From XML Data Import.
2. On a computer that is running Windows Vista  
   • In the Address bar, click the drive, folder, or Internet location that contains the XML data file (.xml) that you want to import.

On a computer that is running Microsoft Windows XP  

• In the Look in list, click the drive, folder, or Internet location that contains the XML data file (.xml) that you want to import.
3. Click the file, and then click Open.

The Import Data dialog box is displayed.

4. Click one of the following options:
   • XML table in existing worksheet

An XML table is created in a new worksheet.

The contents of the file are imported into a new XML table.

If the XML data file does not refer to a schema, then Excel infers the schema from the XML data file.

• Existing worksheet

The XML data is flattened or transformed into a two-dimensional table comprising rows and columns. The XML tags appear as column headings, and the data appears in rows below the appropriate column headings. The first element (the root node) is used like a title and is displayed in the specified cell location. The rest of the tags are sorted alphabetically across the second row.

In this case, Excel does not infer a schema, and you cannot use an XML map.

• New worksheet

Excel adds a new worksheet to your workbook and automatically puts the XML data in the upper-left corner of the new worksheet.

If the XML data file does not refer to a schema, then Excel infers the schema from the XML data file.

5. To control the behavior of XML data, such as data binding, format, and layout, click Properties, which displays the XML Map properties dialog box. For example, existing data in a mapped range is overwritten when you import data by default, but you can change this.

 Top of Page

Open an XML data file

1. Click the Microsoft Office Button , and then click Open.

The Open dialog box is displayed.

2. On a computer that is running Windows Vista  
   • In the Address bar, click the drive, folder, or Internet location that contains the file that you want to open.

On a computer that is running Microsoft Windows XP  

• In the Look in list, click the drive, folder, or Internet location that contains the file that you want to open.
3. In the folder list, locate and open the folder that contains the file.
4. Click the file, and then click Open.
5. Choose one of the following, depending on which dialog box is displayed:

The Import XML dialog box  

 Note    This dialog box appears if the XML file that you open refers to one or more Extensible Stylesheet Language Transformation (XSLT) (XSL Transformation (XSLT): A file that is used to transform XML documents into other types of documents, such as HTML or XML. It is designed for use as part of XSL.) style sheets.

Click one of the following options:

• Open the file without applying a style sheet

The XML data is flattened or transformed into a two-dimensional table comprising rows and columns. The XML tags appear as column headings, and the data appears in rows below the appropriate column headings. The first element (the root node) is used like a title and is displayed in the specified cell location. The rest of the tags are sorted alphabetically across the second row.

• Open the file with the following style sheet applied (select one)

Select the style sheet that you want to apply, and then click OK. The XML data is formatted according to the style sheet that you selected.

 Note    The XML data is opened as read-only in Excel so that you don't accidentally save your original source file in the Excel Macro-Enabled Workbook file format (.xlsm). In this case, Excel does not infer a schema, and you cannot use an XML map.

The Open XML dialog box  

 Note    This dialog box appears if the XML file has no XSLT style sheet references.

Click one of the following options:

• As an XML table

An XML table is created in a new workbook.

The contents of the file are imported into the XML table. If the XML data file does not refer to a schema, then Excel infers the schema from the XML data file.

• As a read-only workbook

The XML data is flattened or transformed into a two-dimensional table comprising rows and columns. The XML tags appear as column headings, and the data appears in rows below the appropriate column headings. The first element (the root node) is used like a title and is displayed in the specified cell location. The rest of the tags are sorted alphabetically across the second row.

The XML data is opened as read-only in Excel so that you don't accidentally save your original source file in the Excel Macro-Enabled Workbook file format (.xlsm). In this case, Excel does not infer a schema, and you cannot use an XML map.

• Use the XML Source task pane

The schema of the XML data file is displayed in the XML Source task pane. You can then drag elements of the schema to the worksheet to map those elements to the worksheet.

If the XML data file does not refer to a schema, then Excel infers the schema from the XML data file.

 Top of Page

Issues with importing XML data

The following sections explain issues that you can encounter when you import XML data.

I get errors when I import XML data.

Microsoft Office Excel 2007 displays the XML Import Error dialog box when it cannot validate data according to the XML map. Click the Details button in this dialog box for additional information about each error. The following table describes important errors that you can encounter.

When I open an XML file, I get the message, "The specified XML file does not refer to a schema".

You are opening an XML file that doesn't refer to an XML schema (XML Schema: A formal specification, written in XML, that defines the structure of an XML document, including element names and rich data types, which elements can appear in combination, and which attributes are available for each element.). Excel must create a schema based on the contents of the XML file in order to work with the XML data that is contained in the file.

If the schema that is created by Excel is incorrect or insufficient for your needs, then you should create an XML schema file and then edit the XML data file so that the XML data file refers to the schema.

 Note    You cannot export the Excel inferred schema as a separate XML schema data file (.xsd). Although there are XML schema editors and other methods for creating an XML schema file, you may not have convenient access to them or know how to use them. As an alternative, you can use the Microsoft Office Excel 2003 XML Tools Add-in Version 1.1, which can create a schema file from an XML map. For more information, see Using the Excel 2003 XML Tools Add-in Version 1.1.

Follow these steps to remove the schema that Excel created from your workbook:

1. If the Developer tab is not available, do the following to display it:
   1. Click the Microsoft Office Button , and then click Excel Options.
   2. 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.
2. On the Developer tab, in the XML group, click Source.

3. In the XML Source task pane, click XML Maps.

The XML Maps dialog box is displayed.

4. Click the XML map that represents the schema that was created by Excel, and then click Delete.

I am having problems importing multiple XML files that use the same namespace but different schemas.

In general, when you work with multiple XML data files and XML schemas, it is common to create an XML map for each schema, map the elements that you want, and then import each XML data file to the appropriate XML map. The Import command (in the XML group on the Developer tab) was designed so that if you import multiple XML files with the same namespace, you can only use one XML schema. If you use this command to import multiple XML files that use the same namespace but different schemas, you can get unexpected results. For example, data may get overwritten, or the operation may fail.

If you want to import multiple XML files that use the same namespace but different XML schemas, you can use the From XML Data Import command (from the From Other Sources button, in the Get External Data group, on the Data tab), which was designed so that multiple XML files with the same namespace can use multiple XML schemas. Excel creates a unique XML map and XML mapping for each XML data file that you import.

 Note    If you are importing multiple XML files that do not define a namespace, these XML files are treated as if they use the same namespace.

 Top of Page

See Also

• Append or overwrite mapped XML data
• Export XML data
• Format the data and layout of an XML table
• Map and unmap XML elements
• Overview of XML in Excel
• Rename an XML map
• Validate XML data against an XML schema
• XML for the uninitiated
• XML Schema Definition (XSD) data type support

Excel > Working with XML

Rename an XML map

When you add an XML map to a workbook, Microsoft Office Excel 2007 provides default XML map names in the following way: Root_Map, Root_Map1, Root_Map2, and so on. To avoid confusion, you may want to provide more meaningful names to the XML map, especially if you are working with more than one XML map.

1. If the Developer tab is not available, do the following to display it:
   1. Click the Microsoft Office Button , and then click Excel Options.
   2. 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.

2. On the Developer tab, in the XML group, click Source.

3. In the XML Source task pane, click XML Maps.
4. Click the XML map that you want to rename.
5. Click Rename.
6. Type the new name, and then press ENTER.
7. Click OK.

The new name is displayed in the XML maps in this workbook list at the top of the XML Source task pane.

 Note     Because element names are based on the XML schema file (.xsd) or the inferred schema that Excel creates when you don't have an XML schema file, you cannot change the element names after you create an XML map.

See Also

• Delete XML map information from a workbook
• Export XML data
• Import XML data
• Map and unmap XML elements
• Overview of XML in Excel
• XML for the uninitiated

Excel > Working with XML

Validate XML data against an XML schema

You validate XML data against an XML schema to ensure that any XML data that you import to or export from one or more cells in a mapped range in a worksheet conforms to the XML schema in the XML Source task pane.

1. If the Developer tab is not available, do the following to display it:
   1. Click the Microsoft Office Button , and then click Excel Options.
   2. 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.

2. On the Developer tab, in the XML group, click Source.

3. In the XML Map Properties dialog box, select Validate data against schema for import and export.

This check box is cleared by default.

 Note    This option does not validate data when you enter data in mapped cells. To make sure that the data that you enter conforms to any enumerations that are defined in the mapped schema, you may need to further validate the data by using the Data Validation command in the Data Tools group on the Data tab. For more information, see XML Schema Definition (XSD) data type support and Prevent invalid data entry in a worksheet.

See Also

• Export XML data
• Import XML data
• Map and unmap XML elements
• Overview of XML in Excel
• XML for the uninitiated
• XML Schema Definition (XSD) data type support

Excel > Working with XML

XML Schema Definition (XSD) data type support

It is important to understand how XML Schema Definition (XSD) data types are handled when you import or export XML data, so that the data is appropriately converted according to your needs.

What do you want to do?

Learn how Excel handles XSD data types when you import XML data

Learn how Excel display formats map to XSD data types when you export XML data

Learn how Excel handles XSD data types when you import XML data

Important  If an XML schema file (.xsd) does not define a specific data type attribute for an element (such as xsd:decimal), then Microsoft Office Excel 2007 formats the cell as text by default when the XML data is imported. Formatting as text ensures that the characters stored in the cell are exactly the same as the data that is stored in the XML file (.xml). For example, leading zero (0) values in an ID or credit card field are removed when they are formatted as a number, but not removed when they are formatted as text. However, data that is formatted as text does not evaluate. If you want the data to evaluate because it contains a formula, you must explicitly provide a numeric data type attribute, such as xsd:decimal or xsd:integer.

The following table lists the display formats that are applied when an item with a particular XSD data type is imported into an Excel worksheet. Data with an XSD format listed in the Unsupported formats column is imported as text values.

 Top of Page

Learn how Excel display formats map to XSD data types when you export XML data

When you export XML data, the exported data will match the data that is displayed in the worksheet, under the following circumstances:

• The Excel display format is Text.
• You have not changed the display format of the data.

If you change the display format of a cell that has numeric, date, or time data, then the underlying value of the cell is exported. For example, if you apply a Percentage display format to a cell that results in the display of 51.50%, the cell's value will be exported as .515.

 Top of Page