Excel 2000 files can crash Excel 2002, 2003

Some Excel workbooks created with Excel 97 or 2000 won't run under Excel 2002 or 2003. The problem came to light with a workbook containing macros and a single "ActiveX" command button; everything was fine under Excel 97 and 2000, but when the user upgraded to Excel 2003 it came badly unravelled.

The problem was that Excel 2003 (and 2002 as well, it transpired) was losing track of the Visual Basic code and ActiveX controls embedded in the workbook. Excel's automatic "repair" strategy consisted of ditching all the macros and turning the command button into a picture of one.

The solution: the following procedure got the workbook going again under later versions of Excel, and users afflicted with this problem can try it for themselves:

  1. Open the offending file in Excel 97 or 2000
  2. Note the properties of any command buttons or other ActiveX controls, as these will be lost later
  3. Open the VB project (Alt-F11)
  4. Export all code modules and delete them from the project
  5. If you have code attached to worksheets or the workbook, copy the code into a text file (insert a note of the sheet name before each block of code; don't use the sheet numbers because these may change later)
  6. Save and close the workbook and the text file containing sheet and workbook code
  7. Reopen the file using Excel 2002 or 2003
  8. Excel may still die; if so, allow it to repair the file (yeah, right) and open the "repaired" version
  9. Open the VB project window
  10. Insert a module
  11. If you had a Module1 in the original file, rename the new Module1 to something else
  12. Import the previously-exported code modules
  13. Paste any worksheet code back from the previously-saved text file into the corresponding sheets; likewise any code attached to "ThisWorkbook"
  14. If you had command buttons on any of your worksheets, they will have been turned into pictures of themselves. Replace them with real buttons and redefine their properties
  15. Save and close the rebuilt workbook
  16. Cross your fingers and reopen the file...
What could be easier?

Vilnis Vesma, 13 May 2005