Opening and Importing XML Documents into an Excel Worksheet

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

To open an XML document from the Excel application, select File, Open and then choose the desired XML file (.xml file extension) from the Open dialog box. After selecting a file, you will be asked if you want to open the file as an XML list, read-only workbook, or to use the XML Source Task Pane (see Figure 8.3). Typically, you load the data into a worksheet as an Excel list in order to take advantage of the data management features a list provides.

Please select how you would lite to open this file: ® As an XML list O As a read-only workbook O Use the XML Source taskpane

If the XML file does not reference an existing schema document (.xsd file extension), Excel will automatically create one (you may be notified of this fact as shown in Figure 8.4) and store it internally with the workbook. You don't have to see the schema, or know how it describes your XML document, but you should know that it's there working in the background defining your data elements for Excel.

Selecting the data format when opening an XML file.

Microsoft Office Excel X

Opening an XML file with no referenced schema.

Microsoft Office Excel X

Opening an XML file with no referenced schema.

When you open an XML file as a list, Excel adds the data to a worksheet and creates a list (normally created from the Data menu). An Excel list provides additional features and formatting that makes it easy to identify and modify the list. Figure 8.5 shows data from an XML document that describes a list of words and topics (something you might use in the project for Chapter 7). The list is highlighted with a blue border, and a filter (normally selected from the Data menu) is automatically applied. In addition, an asterisk marks the next available row for inserting data into the list. The following XML code defines the basic structure of the XML file opened in Figure 8.5—the data was omitted for brevity.

<?xml version="1.0" encoding="UTF-8"?> <word_find>


<topic></topic> <word></word> </topic_word_pair> <!--repeat topic_word_pair element-> </word_find>


Opening an XML file as a list.

E3 Microsoft Excel - Book!

fate E.dfc iiew Lnswt Fermât Lorfs final » 30 v jTJ / U ■




11 [Slates VERMONT


13 31atei VAJNE 1 i Slates CONNECTICUT









23 Slates LLHOIS

il\ShötllX Sii^tlS /ShzieLl /

You can manage the list and the data it contains from the XML selection on the Data menu and/or the Source Task Pane (see Figures 8.6 and 8.7). For example, you can export changes to the list to the XML file, refresh the data in the list, edit the properties of the XML map, and more. As you will see shortly, Excel provides several objects that allow your VBA programs to accomplish these same tasks.

The XML menu selection in Excel.

The XML menu selection in Excel.

The XML Source Task Pane.

The XML Source Task Pane.

Excel also uses the provided (or created) XML schema to create an XML map that serves to map the elements in the XML file to specific ranges in the worksheet. The map, shown in the Source Task Pane in Figure 8.7, was created automatically when I opened the XML file. The topicID element is mapped to the range A1:A23 in the worksheet and word is mapped to B1:B23. The map tells Excel how changes to the list must be saved in the XML file such that it preserves its original structure.

You can also import data from an XML file into any existing worksheet by selecting Data, XML, Import (see Figure 8.6) from the application window. Again, a schema will be automatically created (if one is not referenced) and you will be prompted to select a range in the worksheet telling where you want the data inserted.

Was this article helpful?

0 -3
Biorhythm Awareness

Biorhythm Awareness

Who else wants to take advantage of biorhythm awareness to avoid premature death, escape life threatening diseases, eliminate most of your life altering mistakes and banish catastrophic events from your life.

Get My Free Ebook

Post a comment