If you have ever looked at the source code for a Web page, you are familiar with HTML tags. Near the top of a Web page’s HTML source, you will see a tag that defines the Web page title to appear in the blue bar at the top of the browser window. The tag might look something like this:

<TITLE>Excel Tips from MrExcel</TITLE>

A Web page can contain tags to identify titles, paragraphs, tables, rows within tables, and so on. This is the language of HTML.

XML is like HTML on steroids. With XML, you can define absolutely any fields in XML. The following XML file contains information about today’s orders received. There is no magic in creating an XML file; I actually typed this into Notepad:

<TodaysOrders>
    <SalesOrder>
        <Customer>BCA Co</Customer>
        <Address>123 North</Address>
        <City>Stow</City>
        <State>OH</State>
        <Zip>44224</Zip>
        <ItemSKU>23456</ItemSKU>
        <Quantity>500</Quantity>
        <UnitPrice>21.75</UnitPrice>
    </SalesOrder>
    <SalesOrder>
        <Customer>DEF Co</Customer>
        <Address>234 Carapace Lane</Address>
        <City>South Bend</City>
        <State>IN</State>
        <Zip>44685</Zip>
        <ItemSKU>34567</ItemSKU>
        <Quantity>20</Quantity>
        <UnitPrice>50.00</UnitPrice>
    </SalesOrder>
</TodaysOrders>

If you are familiar with HTML, you need to aware of a few simple rules that differentiate XML from HTML:

For a more thorough discussion of XML, see Benoit Marchal’s XML by Example (978-0-7897-2504-2).

For many years, CSV was considered the universal file format. Just about any application could produce data in comma-separated values, and just about any spreadsheet could read in CSV data. XML promises to become the universal file format of the future, and it is much more powerful.

You may deal with CSV data every day. As developers, we need to talk to the other developer who is generating the CSV data, and we have to understand that the fifth column contains a zip code, and the sixth column contains a product SKU. If the source system ever leaves out a field, we are likely to end up with wrong results. For years, CSV has been the universal format to get data from another system into a spreadsheet. With CSV, both developers must understand that the fifth column contains a zip code, and the sixth column contains a product SKU (see Figure 17.1).

Figure 17.1. Open a CSV file and you may have this confusing view of the data.

XML is much more powerful. If you use Excel to open the simple XML file shown in Figure 17.1, Excel can offer headings and can even infer something called the schema of the data. A schema is a separate file that describes the columns and relationships inherent in the data. If the source system would happen to be missing a zip code for a record, we do not have to worry about the SKU moving over from Column F. Compare Figure 17.2 with the CSV example shown in Figure 17.1. Excel can present a much more meaningful view of the data.

Figure 17.2. Data can be written in XML format by any system and intelligently read into Excel. Unlike CSV data, the format of XML data is something Excel can understand.

If you insert new records into the XML list in Excel, you can save the file back out as just the XML data. Use Office Icon, Save As, Other Formats. In the Save As Type drop-down, choose XML Data (*.xml). Excel can natively read from and write to XML files, allowing you to share data between applications.

After you save the document as an XML table, Excel nicely formats the XML with an appropriate structure and tab characters for subelements. This allows the data to be viewed in a simple text editor such as Notepad (see Figure 17.3).

Figure 17.3. After you add records to the XML data in Excel and save, the resulting XML is neatly formatted.

Note that in the preceding example we had only an XML file, and Excel was able to accurately read the data, allow editing, and write the data back out for use by another application.

Two additional file types—schemas and transforms—enhance XML files.

Although the XML file contains the data and field names, an XML schema file defines data relationships and data validation requirements. For example, a zip code field could require five numeric digits. XML schemas are typically stored in XSD files.

XSL files are called transforms or solutions. A transform describes how the fields in the XML file should be mapped to your document. If your data contains 20 elements, you can define in the transform file that you want to see only particular elements in this spreadsheet. You can have many XSL files for a particular schema to enable many views of the same data.

If you are reading someone else’s XML data, XSD and XSL files have probably been provided for you. If you are reading your own XML data, Excel actually infers a data schema for you. After opening the XML file with TodaysOrders introduced at the start of this chapter, you can go to the immediate pane of the VB Editor and retrieve the XSD file. Type the following:

Print ActiveWorkbook.XmlMaps(1).Schemas(1).xml

Copy the result to a Notepad window and save as TodaysOrders.xsd. Figure 17.4 shows the inferred XSD file, although I have added line breaks and spacing to improve the readability.

Figure 17.4. Excel is able to infer a default schema for any XML file that it encounters. Having an XSD schema file is a requirement to use any higher-level XML features such as repurposing data.

The final step to fully use XML in Office 2007 is to create XSL transform files. There is no easy way to create this with the tools in Excel. For a discussion of the rather awkward process of manually creating an XSL file, see www.mrexcel.com/tip064.shtml.

Microsoft has been rapidly improving their support for XML:

How Excel 2007 Stores Workbooks with XML

Excel 2007 introduces new file formats such as XLSX, XLSM, and XLSB. The last file format is still a proprietary binary format. However, the XLSX and XLSM file formats are 100 percent XML formats. Excel saves the workbook, including charts, SmartArt graphics, formulas, formatting, and numbers in various XML files. All the XML formats are then zipped into a single file and the extension is changed from .zip to .xlsk.

In addition, the XLSM format includes support for macros, modules, and userforms.

You can easily explore the inner workings of an Excel 2007 file by following these steps.

1. Create an Excel file. Add formulas, formatting, a chart, and a bit of VBA macro code.

2. Save the file as an XLSM file.

3. Close the file.

4. Using Windows Explorer, browse to the folder that contains the file.

5. Rename the file to have a .zip extension. You may be warned that this might make the file unusable. This is okay.

6. Open the file with WinZip or your favorite unzipping utility. You will see that the file is composed of many XML parts. The chart and the VBA will each get its own XML part. You will have an XML part for each worksheet. The calculation tree is now fully exposed and explorable in CalcChain.xml.

Figure 17.6 shows the original workbook, the files in the zip file and one of the XML parts. Excel stores shared strings here in order to make the file size smaller.

Figure 17.6. In Excel 2007, Microsoft stores most spreadsheet parts in plain text XML files, and then zips them into a single file.

Using XML Data from Amazon.com

The promise is that all sorts of XML data will soon be available. One high-profile example available now is Amazon.com. They now offer a wide variety of queryable data that is returned in XML format.

It is possible to use VBA to query data from Amazon and have the results returned to Excel.

Amazon.com now offers more than 30 different XML streams that you can query as well as complete documentation of the information available at http://amazon.com/webservices.

Starting with a blank Excel workbook, first attach the schema to the workbook:

1. From the Developer tab of the ribbon, select Source from the XML group.

2. In the XML Source pane, click the XML Maps button.

3. In the XML Maps Dialog, click the Add button.

4. In the File Name box, type the URL for the Amazon heavy XSD file: http://xml.amazon.com/schemas3/dev-heavy.xsd. Select Open.

5. In the Multiple Roots dialog, select the ProductInfo as the root note of interest.

6. Click OK to close the XML Maps dialog.

All the fields available in the Amazon.com ProductInfo schema now appear in the XML Source pane. Drag any fields of interest to your spreadsheet, as shown in Figure 17.7.

Figure 17.7. After the XSD schema is attached to the workbook, you can drag fields of interest from the XML Source pane to the spreadsheet.

Now that the workbook is set up, you can switch to VBA to enter the code needed to retrieve data from Amazon.com.

The first step is to go to Tools, References and add a reference to Microsoft WinHTTP Services.

The Amazon Development Kit provides many sample URL strings to return various searches by author, publisher, ISBN, and so on.

After defining the proper URL string, the string is sent as a WinHttpRequest. The results are then mapped to the worksheet through the use of the .ImportXML method.

This sample code queries Amazon for all books written by Bill Jelen and published in 2006:

Sub QuerybyAuthor()
    ' Query all Bill Jelen books published in 2002
    Dim ws As Worksheet
    Dim whr As New WinHttpRequest
    Dim lobj As ListObject
    Dim nCount As Integer
    Dim objSelected As Object

    Dim sURI As String

    Set objSelected = Selection
    Set ws = Worksheets("Sheet1")
    Set lobj = ws.ListObjects(1)

    Application.Cursor = xlWait

    sURI = "http://xml.amazon.com/onca/xml2?t=webservices-20" _
        & "&dev-t=D3VCCO47XZEQFA" _
        & "&PowerSearch=author:Bill Jelen and pubdate:2006" _
        & "&mode=books&type=heavy&page=1&f=xml"

    whr.Open "GET", sURI
    whr.Send

    ActiveWorkbook.XmlMaps(1).ImportXml whr.ResponseText

    Application.Cursor = xlDefault

End Sub

After the query has run, the results are written to the List object on sheet1. Note that because I asked for an Author field and Author is a repeating element along the ProductInfo node, any titles with two authors appear twice, one with each author (see Figure 17.8).

Figure 17.8. The .ImportXML method returns the search results to the worksheet. Only the mapped fields that were dragged from the XML Source pane are displayed.

It is interesting to note that the ProductInfo_Map offers six dozen fields and the results of the query are easily changed by dragging and dropping new data from the XML Source pane to the workbook. Without changing one line of VBA code, the query can easily focus on SalesRank or Lists or the number of Marketplace vendors offering the book for sale.

 

Although XML enables you to transfer data between unrelated applications, you already can transfer data programmatically between applications in the Microsoft Office suite. Chapter 18, “Automating Word,” looks at using Excel VBA to automate and control Microsoft Word.