File takes TWO HOURS to open

[DFStoneJr]

I'm having another problem that's driving me nuts.

WinNT, Excel 2000
512Mb RAM, P3-550
The file is stored locally, not onm a networked drive.

I have a 20Mb file that takes two hours to open. Here's what I have
done so far:

1) Cleaned out all .TMP and .EMF files wherever they reside.
2) Cleaned out all .xls files left over from sending and receiving
file in Outlook.
3) Defragged the hard disk (although the file also takes this long to
load when copied to another computer).
4) Deleted most of the array formulae in the workbook, including five
sheets that contained a lot of array formulae -- of course, I resaved
the workbook to another name and tried to retrieve it, with no effect
on the retrieval period.
5) Visited each sheet and deleted rows and columns below and to the
right of the last cell I've used on that sheet.
6) Closed all other applications.
7) Used CodeCleaner to clean out the VBA modules.
Deleted all add-ins.

I have other, larger files that retrieve in a minute at most.
Recalculation is set to manual. Some cells are linked to another
file, but the total linked cells are less than 100.

I've visited the Google archives and the MSKB, and I'm at a loss as to
what I can do to open this file more quickly.

What other ideas or solutions might there be?

[KniteMare]

While I've had files that open slowly I've never had one take Two hours.
Is there code runing on Open or linked to any other event? If there is code can you (Save a copy of the file firs before you do this!!)save it out to text files delete all Modules and code form the workbook. then open the file and see if it still takes forever to open. Then reimport back the code. Excel has a funy way of leaving junk behind during the creation process as we edit our code and tweak the files (ie. fix the bugs). There are several utilities available for down load to help clean up your code if that turns out to be the problem.

If it is not!! There is a MS bullitin (I forget the number, that states that under certain conditions Excel will open and close slowly. I'd search MS for this and see what they say. I believe part of the problem had to due with networking issues but it has been a while and I could be mistaken. I'll look up my old research and give you the links I went to. In the mean time I just wanted to let you know your not alone others have had issues with Slow Excel opening in the past.

[Dave Hawley]

Hi

Try this:

Open the Workbook (yeah I know, but it must be done)
Right click on sheet index 1 and Copy it to an new Workbook. Now save that new Workbook, close it and re-open it. Do the same now for sheet index 2 but copy it to the first Workbook you created. Repeat this process closing and opening the new workbook each time. Soon as the new Workbook 'hangs'upon opening you have found the corrupt Worksheet(this what I suspect it is).

A problem I've struck with slow opening files (up to an hour) even with manual calculation has been where the file has been saved with a large amount of data and it has been left with a particular filter set. I fixed my problem by making sure the filter was set to "All" before saving as excel seems to go cell by cell through the entire data before opening.

Dave is probably onto something, but would it also help to set calculation to manual and then hit F9 after the file has opened?