Clearing Obstacles for MS Excel Application Upgrading to 2007

,Recently an Excel application wrote in 2003 version needs to be converted into 2007 following the network infrastructure upgrade. It is noticed there are three parts needed to be attended.

In VBA micro of old application, value of attribute FileFormat for ActiveWorkbook.SaveAs was xlNormal. It is no longer allowed in Excel 2007. The FileFormat now needs to be explicitly expressed. Common values are:

51 = xlOpenXMLWorkbook (without macro's in 2007, xlsx)
52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007, xlsm)
50 = xlExcel12 (Excel Binary Workbook in 2007 with or without macro's, xlsb)
56 = xlExcel8 (97-2003 format in Excel 2007, xls)

In this case, value of 52 is used. For more information, please refer to http://www.rondebruin.nl/saveas.htm.

File name is also an issue. This application automatically archives data everyday into a file, of which there is a space in file’s name. Now VBA in Excel 2007 converted the space to ‘%’ sign. Accordingly, the archiving file’s name needs to be changed, as well as the file type changed from .xls to .xlsm.

Originally archiving function involved large chunk of cells being copied from one worksheet to another. After conversion to Excel 2007, it is noticed the file’s size increase drastically without apparent reason. The educated guessing is somehow the application accumulated its clipboard during the automatic archiving process which involved copy-paste and save-as. However, nothing was found either in worksheets or clipboard. Web search shows no such error had been reported to Microsoft. After rewrote codes in this part to remove format as part of clipboard, the problem is resolved, although the reason is still not be found.