Prevent calculation on open

[littlefb]

Is there a way to prevent calculations from running when opening a file? I
can't get the file to open at all because the calculations take hours and
hours. I need to prevent them from running as I open the file.

 

[Stefi]

Try to set Calculation to Manual before opening this workbook
(Tools>Options>Calculation tab)!

Regards,
Stefi


„littlefb†ezt írta:

 

[littlefb]

I'm using Excel 2007 (which has this in a different place), but I already
tried and it didn't work.

 

[Jarek Kujawa]

right click on a sheet tab
go to View Code
in Project - VBA Project window double click on ThisWorkbook
select Workbook in a window where (General) is shown, select Open form
the window to the right of it
this is what you should see:

Private Sub Workbook_Open()

End Sub


then in between those a/m 2 lines insert the following:

Application.Calculation=xlCalculationManual

the whole procedure should read as follows:

Private Sub Workbook_Open()
Application.Calculation=xlCalculationManual
End Sub

on next opening of the file the calculation will be set to manual

pls click YES if this was helpful

 

[Gord Dibben]

First of all...............what size of workbook and what types of
calculation take hours and hours?

Maybe you should re-design?

Secondly............Excel takes the calculation mode of the first workbook
opened.

Are you opening another workbook set to automatic calc before opening the
offending workbook?

If that's the case, setting the offender to manual calc means nothing.


Gord Dibben MS Excel MVP

 

[littlefb]

This would probably work if I could get the file open in the first place.
The problem is that I can't get it to finish opening the document since it's
trying to run the calculations first.

 

[littlefb]

The file has ~54,000 rows and 10 columsn of data, and the formula is a 4 part
COUNTIFS. That seemed like a good idea at the time (I think I was tired).
But now I can't get the file to finish opening (it's hogging both processors
completely for hours). I've tried setting the currently open workbook
("Book1") to manual, then open the file - no luck. I've tried using a macro
that first sets Application.Calculation to xlManual, then it opens the file.
Still no luck. I basically need a way for Excel to open the file without
trying to make sense of those formulas.

 

[Sheeloo]

I think what Gord is saying is open any other Excel file... set the mode to
Manual... save... close Excel... Open this file and then open your file...

 

[Dave Peterson]

I'd try it again.

I'm using Excel 2007 (which has this in a different place), but I already
tried and it didn't work.

 

[littlefb]

I've tried it this way (with an existing file rather than the unsaved
"Book1"). It's still trying to process that calculation when it opens. I've
had the processors nearly maxed out for over 20 minutes (and I'm trying it on
a faster computer than before). I think I'm giving up and seeing if I can
import just the data itself through MSAccess. Any other ideas that don't
involve additional variations of "set calculation to manual" would be
appreciated!

 

[littlefb]

The import into MSAccess worked beautifully. I was able to salvage all my
data (and even the values from the misguided COUNTIFS formulas). None of the
methods suggested to get Excel to open this file worked. Thanks for the help
anyway!