Programming Excel with VBA and .NET

Data Excel Omits from XML

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 Excel workbook format, then in XML spreadsheet format as shown here:

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 times—first 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 next. You can eliminate the second prompt only by omitting nonsaved items (such as macros) from the workbook:

' Requires reference to Microsoft Scripting Runtime Dim fso As New FileSystemObject xlsName = ThisWorkbook.fullname base = fso.GetBaseName(xlsName) xmlName = ThisWorkbook.path & "\" & base & ".xml" fso.DeleteFile (xmlName) ThisWorkbook.SaveAs ...