I have a large Excel file that I believe was originally created in Lotus 123. The file has been converted over the past 10+ years to each new version of Excel etc. It's been actually "Converted" using the menus in Excel and has been "saved as" xlsm and xlsb. I think creating a fresh file that had all the same calcs etc would fix the problem, so I'm looking for a relatively easy way to do this (more below).
My problem is that the file occasionally hangs Excel on some computers and it takes over a minute to open. The long open time occurs after Excel displays the file and after I tell it not to update external links. Typically, I would expect a delay like this to be related to calculation time or rebuilding of the dependency tree, but this doesn't seem to be the case as Full calc time is roughly 0.06 seconds. A full calc with a rebuild of the dependency chain can be performed in much less than 0.2 seconds (I don't have a precise timer for this one...).
My file is not terribly complex. It is not too big, at 9MB as an xlsm. It has 98 sheets. It has external links, but breaking the links has no effect on load time. I've cleaned up the names and there are no external or bad names. There are about 200 valid names, all are simple cell references. There are no objects and a minimal number of cell comments. There's a BeforePrint macro, but no other macros. The problem occurs with no addins and with no other applications open. This phenomenon occurs in Excel 2007 and 2010.
I've gone through all the steps listed at http://www.decisionmodels.com/optspeedd.htm
I've used FastExcel to clean various things such as ranges, unused styles and number formats, clearing the temp folder, etc.
I suspect that the file is retaining some of the original Lotus architecture or something from a prior version of Excel. I know in older versions of Excel a sheet could become corrupted and would drag down the whole file. If you created a new workbook and copied everything into it, exactly the same, all issues were resolved. Due to the large number of sheets etc, it would be challenging to copy everything to a new file. Is there an easier way to force a true conversion to the latest file formats?
Thanks in advance.
Rob
My problem is that the file occasionally hangs Excel on some computers and it takes over a minute to open. The long open time occurs after Excel displays the file and after I tell it not to update external links. Typically, I would expect a delay like this to be related to calculation time or rebuilding of the dependency tree, but this doesn't seem to be the case as Full calc time is roughly 0.06 seconds. A full calc with a rebuild of the dependency chain can be performed in much less than 0.2 seconds (I don't have a precise timer for this one...).
My file is not terribly complex. It is not too big, at 9MB as an xlsm. It has 98 sheets. It has external links, but breaking the links has no effect on load time. I've cleaned up the names and there are no external or bad names. There are about 200 valid names, all are simple cell references. There are no objects and a minimal number of cell comments. There's a BeforePrint macro, but no other macros. The problem occurs with no addins and with no other applications open. This phenomenon occurs in Excel 2007 and 2010.
I've gone through all the steps listed at http://www.decisionmodels.com/optspeedd.htm
I've used FastExcel to clean various things such as ranges, unused styles and number formats, clearing the temp folder, etc.
I suspect that the file is retaining some of the original Lotus architecture or something from a prior version of Excel. I know in older versions of Excel a sheet could become corrupted and would drag down the whole file. If you created a new workbook and copied everything into it, exactly the same, all issues were resolved. Due to the large number of sheets etc, it would be challenging to copy everything to a new file. Is there an easier way to force a true conversion to the latest file formats?
Thanks in advance.
Rob
Last edited:
