File takes TWO HOURS to open

DFStoneJr

New Member
Joined
Mar 7, 2002
Messages
1
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.
:cool: 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?
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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.
 
Upvote 0
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).
 
Upvote 0
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.
 
Upvote 0
Dave is probably onto something, but would it also help to set calculation to manual and then hit F9 after the file has opened?
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top