Why does it take so long to open?

[Henry]

I have this huge complicated template, 94 Mbs its not a format issue, more a
complex long string of nested IF statements. The problem is that opening the
file takes about 15 mins.. not kidding. Saving the file takes about 5 mins.
Calc the file takes about 5 mins, even if I change all the variables.
My question is why does it take that long to open? The file is on my local
HD, so you would think it would be faster. Can someone explain?

 

[JoeU2004]

What happens if you disable the virus scanning software, if you have any?

Also, it appears to take longer to read from disk than it takes to write to
disk. In fact, it is just the opposite. But most O/Ses buffer writes; that
is, the writes physically complete some time after the system says they did,
unless you set certain high-reliability options. Of course, for reading,
Excel must wait until all data is transferred to memory.

Finally, for normal recalculation, Excel will only recalculate what has
changed (including volatile functions) and their dependencies. In contrast,
when you open a file, Excel might have to visit every cell in order to build
its dependency graph. That's a WAG. The dependency graph might be saved in
the file. I would hope so; but perhaps there is a good design reason why it
shouldn't be.

Of course, there could be other factors involved. For example, do you have
any event macros that might be executed when a file is opened?


----- original message -----

 

[Bill Sharpe]

I have this huge complicated template, 94 Mbs its not a format issue, more a
complex long string of nested IF statements. The problem is that opening the
file takes about 15 mins.. not kidding. Saving the file takes about 5 mins.
Calc the file takes about 5 mins, even if I change all the variables.
My question is why does it take that long to open? The file is on my local
HD, so you would think it would be faster. Can someone explain?

Excel version?
How much free memory?
How fast is your processor?
Short on HD space?
94 mb is somewhat excessive. There must be some way to clean up your logic.

Bill

 

[Henry]

It is Excel 2007.
I have 2 GB RAM, Quad Core machine with 200GB HD, hardware does not seem to
be the problem.
Logic been through it a million times, the complex formula is complicated
because there are many exceptions to get to the answer, I have broken it down
to their minor sections and pieced it all back together, used AND OR (nested)
to cut down on the number of IFs statements.. so I don't think thats the
problem.
I think Joe may have explained it, it may have to do with MSE looking
through every cell and formula on opening to generate the dependency graph.
I'll research it some more. Thanks for the help.

Henry

 

[Henry]

Hi guys,

Just to update you on this issue, I looked into it some more and found
nothing new. However, I renamed my file and now it opens and saves under 20
secs. Can anyone explain that? Actually I woud love MS to explain that one.

Henry