Auto Recalc - STRESS !!!

Post Reply
User avatar
Ajay
Posts: 125
Joined: Wed May 14, 2008 8:27 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010
Location: London

Auto Recalc - STRESS !!!

Post by Ajay » Fri May 16, 2008 8:50 am

Morning All,

Does anyone have a little .xla or something that defaults any spreadsheet to manual calculations when opened, instead of calc'g it ?

Regards
Ajay
x64bit 10.2.2, w2008R2 OS Enterprise Edition, 1TB HD, 384GB RAM, MS Excel 2010, Cognos 10 BI

Paul Segal
Community Contributor
Posts: 247
Joined: Mon May 12, 2008 8:11 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010

Re: Auto Recalc - STRESS !!!

Post by Paul Segal » Fri May 16, 2008 9:17 am

Hi Ajay,

Here's a interesting (for certain values of interesting) link to a Microsoft KB that illuminates the auto recalc behaviour in Excel: http://support.microsoft.com/default.as ... us;Q214395 which should be of help.

Regards
Paul

User avatar
Alan Kirk
Site Admin
Posts: 5830
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: 9.5.2 64 bit moving to 10.2.2
Excel Version: 2010
Location: Sydney, Australia
Contact:

Re: Auto Recalc - STRESS !!!

Post by Alan Kirk » Fri May 16, 2008 9:20 am

Does anyone have a little .xla or something that defaults any spreadsheet to manual calculations when opened, instead of calc'g it ?
Yeah, irritating, innit? There IS usually a way around it though which involves no VBA code at all.

As I understand it Excel will adopt the calculation setting of the last opened file at startup, though since calculation is a global setting (rather than a workbook one) it will ignore the settings of any subsequently opened files.

(The calculation mode is apparently buried somewhere in the file, even if it's not an exposed property.)

The secret is therefore to make sure that the last-opened file at startup has a manual calculation mode.

Most people don't open files at start-up (aside from the odd .xla perhaps) but rather start with a blank workbook, the ubiquitous "Book1". What you therefore have to do is make sure that the blank workbook has a manual calculation setting.

Unless you specify otherwise, Excel will use its own internal settings for blank workbooks, including automatic calculation. To substitute your own, you need to create a new global template as follows:
- Close down any workbooks that you have open in Excel;
- Open up a new blank workbook;
- Change its calculation setting to Manual;
- If you feel inclined, take the time to make any other changes that you like. Don't like Arial 10 as the default font? Redefine the Normal style. Want 4 sheets instead of 3? Add one, and so on.
- Find the folder called xlStart. This will typically be on a path like
C:\Documents and Settings\{UserName}\Application Data\Microsoft\Excel\XLSTART
- Save the modified workbook as an Excel template (.xlt) file named Book.xlt (NOT Book1.xlt, just book.xlt)

Thereafter when you open a session of Excel, the blank workbook that opens should have all of your updated styles and settings... including the manual calculation mode.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.

User avatar
Alan Kirk
Site Admin
Posts: 5830
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: 9.5.2 64 bit moving to 10.2.2
Excel Version: 2010
Location: Sydney, Australia
Contact:

Re: Auto Recalc - STRESS !!!

Post by Alan Kirk » Fri May 16, 2008 9:22 am

Alan Kirk wrote: As I understand it Excel will adopt the calculation setting of the last opened file at startup,
Oops, my memory lapse; as per the article mentioned by Paul, it's actually:
The first document opened uses the calculation mode with which it was last saved. Subsequently opened documents use the same mode.
Regardless, the book.xlt technique should still work.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.

User avatar
jim wood
Site Admin
Posts: 3600
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: TM1 10.2.2
Excel Version: 2007
Location: 1639 Route 10, Suite 107, Parsippany, NJ, USA
Contact:

Re: Auto Recalc - STRESS !!!

Post by jim wood » Fri May 16, 2008 9:55 am

I have had real issues with this myself as I have a set of data templates completed by users. I have found that somtimes (Even thogu you have autocalculate siwtched off.) it remebers the setting from when the file was last saved and they always start loading data.

The only solution I ahve found is make sure you are not connected to any TM1 server when opening the file. Even if it set to auto calculate it comes back really quickly,

Jim.
Struggling through the quagmire of life to reach the other side of who knows where.
Application Consulting Group (ACG) TM1 Consulting
OS: Windows 10 64-bit. TM1 Version: 10.2.2

User avatar
Steve Vincent
Site Admin
Posts: 1049
Joined: Mon May 12, 2008 8:33 am
OLAP Product: TM1
Version: 10.2.2 FP1
Excel Version: 2010
Location: UK

Re: Auto Recalc - STRESS !!!

Post by Steve Vincent » Fri May 16, 2008 10:15 am

Orrrr you could use the addin i created and demo'd at the "TM1 User Conference of the North" last year :D

This puts a drop down box in to the menu bar with 2 options, calc suppress ON and OFF. ON will ensure any new or opened wb will have calculation turned to manual. OFF will make it auto calc. Note it only makes this change when a wb is OPENED or CREATED. Just selecting the drop down will NOT change anything ;)

I'll add it to the thread of useful stuff later as well...
Attachments
TM1 Tools.zip
ensures auto calc is always on or off, based on users needs
(8.36 KiB) Downloaded 340 times
If this were a dictatorship, it would be a heck of a lot easier, just so long as I'm the dictator.
Production: TM1 64 bit 10.2.2, Windows 2008/2012 Server. Excel 2010, IE11 for t'internet

Post Reply