Excel 2002/2003 XML Spreadsheet: Random crashing

[richard.tallent]

I've been successfully creating and using XML Spreadsheets
programmatically for some time now, without issues, even with
spreadsheets that are 40-80MB large.

However, for a recent project, we created a spreadsheet (in Excel) and
saved it in the XML Spreadsheet format. It contains several dozen
worksheets with financial calculations (nothing fancy, no add-ins or
macros) and numerous lookup functions to worksheets in the same file
(no external references or named regions).

Unforunately, we are running into CONSTANT issues with Excel completely
crashing while saving the file in XML Spreadsheet format. In some
cases, we can save it in normal Excel format, in others we must create
a new workbook and move the worksheets to it before even being able to
save in the native format. This occurs in both Excel 2002 (SP1 IIRC)
and 2003 (SP1 and SP2).

I'm aware that some Excel features, such as macros, are not supported
by the XML format, but in ACTUAL USE, does anyone know of specific
features, formulas, or formats that are likely to cause Excel to barf
while saving?

Regards,

Richard Tallent

 

[richard.tallent]

BTW, I was able to track down at least one culprit: use of AutoFilter
on a large table (several dozen columns, several thousand rows) borks
Excel's ability to save in XML Spreadsheet format. I'm sure there are
others, however, that people have run into that we should take care to
avoid.

--Richard