Section 3.3. Save Workbooks as XML | Excel 2003 Programming: A Developers Notebook (Developers Notebook)

3.3. Save Workbooks as XML

In Excel 2003, you can now save a workbook as an XML spreadsheet or as XML data from the Save As dialog box (Figure 3-1).

Note: Saving as XML makes your workbook usable from non-Windows platforms and convertible to other interesting formats, such as HTML. It's also handy to understand the little trick Microsoft uses to make Excel's XML appear as a workbook in Windows.

Figure 3-1. Save As dialog box

3.3.1. How to do it

Choosing the XML Spreadsheet file type saves the workbook in an XML file that uses the Microsoft Office schema. Choosing the XML Data file type saves the workbook file in an XML file that uses a schema you provide through an XML map. Since it's a good idea to start simply, I'll discuss the XML Spreadsheet format here and the XML Data format later in "Use XML Maps" later in this chapter.

If you save a workbook as an XML spreadsheet, you can open the file in Notepad, edit it, and still reopen/edit it in Excel laterprovided you haven't broken any of the rules in the file's schema. A simple default workbook includes a lot of items that aren't required by the Office schema, and you can simply delete those items to see the simplified "core" of an XML spreadsheet, as shown in the following XML:

      <?xml version="1.0"?>                             }- Processing instruction     <?mso-application prog?>     <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"  }      xmlns:o="urn:schemas-microsoft-com:office:office"              }      xmlns:x="urn:schemas-microsoft-com:office:excel"               }      xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"           }-Namespaces      xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"        }      xmlns:html="http://www.w3.org/TR/REC-html40"                   }      xmlns:x2="http://schemas.microsoft.com/office/excel/2003/xml"> }      <Worksheet ss:Name="Sheet1">                                     }        <Table ss:ExpandedColumnCount="5" ss:ExpandedRowCount="2"       }        x:FullColumns="1"                                              }        x:FullRows="1">                                                }        <Column ss:Index="5" ss:AutoFitWidth="0" ss:Width="54.75"/>    }        <Row>                                                  }       }         <Cell><Data ss:Type="Number">1</Data></Cell>          }       }         <Cell><Data ss:Type="Number">2</Data></Cell> }- Cell  }-Row }-Worksheet         <Cell><Data ss:Type="Number">3</Data></Cell>          }       }         </Row>                                                }       }        <Row>                                                          }         <Cell><Data ss:Type="Number">4</Data></Cell>                  }         <Cell><Data ss:Type="Number">5</Data></Cell>                  }         <Cell><Data ss:Type="Number">6</Data></Cell>                  }         </Row>                                                        }       </Table>                                                        }      </Worksheet>                                                     }      </Workbook>

3.3.2. How it works

The preceding XML has these notable features:

The mso-application processing instruction tells the Microsoft Office XML Editor (MsoXmlEd.Exe) to open the file with Excel.
Office uses numerous namespace definitions to qualify the names used in its XML documents.
The path to data on a spreadsheet is Workbook/Worksheet/Table/Row/Cell/Data. The Cell node is used to contain formulas, formatting, and other information as attributes.
The Column element is not a parent of the Row or Cell elements as you might expect. Instead, it is mainly used to set the width of the columns on the worksheet.

You can experiment with the XML spreadsheet by making changes in Notepad and seeing the results. For instance, if you change the mso-application processing instruction to:

      <?mso-application prog?>

Now, the spreadsheet will open in Word 2003 if you double-click on the file in Solution Explorer. Change the progid to "InternetExplorer.Application" or delete the processing instruction and Windows will open the file as XML rather than as an Excel spreadsheet in Internet Explorer.

The mso-application processing instruction is ignored if you don't have Office 2003 installed. So, if you post an XML spreadsheet on a network, clients that don't have Office 2003 will see that file as XML rather than as a spreadsheet.

3.3. What you lose and how to keep it

When Excel saves a workbook as XML, it omits these types of data:

Charts, shapes, and OLE objects
Macros

Other types of data (numbers, text, formulas, comments, validation, formatting, sheet layout, window and pane positioning, etc.) are preserved, however. It is best to think of XML spreadsheets as vehicles for data, rather than as full-featured workbooks.

To preserve charts, shapes, OLE objects, or macros, save the workbook file first in XML Spreadsheet format, then in Excel Workbook format:

      ThisWorkbook.SaveAs , xlXMLSpreadsheet      ThisWorkbook.SaveAs , xlWorkbookNormal

By saving the file as a normal workbook last, you leave the current file type as .XLS, so if the user clicks Save the full version of the file is saved. Excel keeps the full workbook in memory even after you save it as an XML spreadsheet, so you don't lose data between the two saves. You are, however, prompted several timesfirst to overwrite existing files since you are using SaveAs, then to note that XML spreadsheets do not save contained objects. You can eliminate the first prompt by deleting the existing file before each step of the save, as shown below. You can only eliminate the second prompt by omitting non-saved items (such as macros) from the workbook:

      ' Requires reference to Microsoft Scripting Runtime      Dim fso As New FileSystemObject, xlsName As String, xmlName As String      xlsName = ThisWorkbook.fullname      base = fso.GetBaseName(xlsName)      xmlName = ThisWorkbook.path & "\" & base & ".xml"      If fso.FileExists(xmlName) Then _        fso.DeleteFile (xmlName)      ThisWorkbook.SaveAs xmlName, xlXMLSpreadsheet      fso.DeleteFile (xlsName)      ThisWorkbook.SaveAs xlsName, xlWorkbookNormal

The preceding code saves two versions of the workbook: one full version with an .XLS file type, and one XML spreadsheet version with an .XML file type.

3.3.4. What about...

To learn about	Look here
Office 2003 XML (schema documentation)	www.microsoft.com/office/ xml/default.mspx.