Every time I open a csv file it recalculates every possible formula in my
workbook, taking forever to open it. Is there any way to disable this from
the Excel menu options rather than in VBA?

In xl2003, I can use:
tools|Options|calculatation tab
and change the calculation mode to manual.

johnmasvou wrote:
>
> Every time I open a csv file it recalculates every possible formula in my
> workbook, taking forever to open it. Is there any way to disable this from
> the Excel menu options rather than in VBA?

--

Dave Peterson

It's worth pointing out that even with manual calculation set you may till
sometimes get a recalc on opening a workbook under certain circumstances. If
the version or build of excel you're using has a different calculation engine
than the file was last saved in is the most common example.

It's not usually an issue for most folks but it's tripped up a couple of my
corporate banking clients big-time where they've been populating data using
an add-in on one box and expecting to rely on cached data elsewhere.


"Dave Peterson" wrote:

> In xl2003, I can use:
> tools|Options|calculatation tab
> and change the calculation mode to manual.
>
> johnmasvou wrote:
> >
> > Every time I open a csv file it recalculates every possible formula in my
> > workbook, taking forever to open it. Is there any way to disable this from
> > the Excel menu options rather than in VBA?
>
> --
>
> Dave Peterson
>

If you don't like that feature, you can make a change to the registry:

Jim Rech posted this:
http://groups.google.com/groups?thre...TNGP11.phx.gbl

Each recipient would have to make this same change.

James Snell wrote:
>
> It's worth pointing out that even with manual calculation set you may till
> sometimes get a recalc on opening a workbook under certain circumstances. If
> the version or build of excel you're using has a different calculation engine
> than the file was last saved in is the most common example.
>
> It's not usually an issue for most folks but it's tripped up a couple of my
> corporate banking clients big-time where they've been populating data using
> an add-in on one box and expecting to rely on cached data elsewhere.
>
> "Dave Peterson" wrote:
>
> > In xl2003, I can use:
> > tools|Options|calculatation tab
> > and change the calculation mode to manual.
> >
> > johnmasvou wrote:
> > >
> > > Every time I open a csv file it recalculates every possible formula in my
> > > workbook, taking forever to open it. Is there any way to disable this from
> > > the Excel menu options rather than in VBA?
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

That's slightly different issue from the one I mentioned, but very useful,
I'll have to make a note of that.

The problems I've worked with relate to the fact that the entire calculation
tree is different. The instances I've worked on have been specifically
between during migrations from versions that use sheet based calculation
chains (so pre-2003) to 2003 onwards where there is a unified calculation
chain. Basically the result cache format is totally different and
incompatible with the version it's being loaded into, meaning the recalc is
unavoidable regardless of save status.

"Dave Peterson" wrote:

> If you don't like that feature, you can make a change to the registry:
>
> Jim Rech posted this:
> http://groups.google.com/groups?thre...TNGP11.phx.gbl
>
> Each recipient would have to make this same change.
>
> James Snell wrote:
> >
> > It's worth pointing out that even with manual calculation set you may till
> > sometimes get a recalc on opening a workbook under certain circumstances. If
> > the version or build of excel you're using has a different calculation engine
> > than the file was last saved in is the most common example.
> >
> > It's not usually an issue for most folks but it's tripped up a couple of my
> > corporate banking clients big-time where they've been populating data using
> > an add-in on one box and expecting to rely on cached data elsewhere.
> >
> > "Dave Peterson" wrote:
> >
> > > In xl2003, I can use:
> > > tools|Options|calculatation tab
> > > and change the calculation mode to manual.
> > >
> > > johnmasvou wrote:
> > > >
> > > > Every time I open a csv file it recalculates every possible formula in my
> > > > workbook, taking forever to open it. Is there any way to disable this from
> > > > the Excel menu options rather than in VBA?
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

James,

1. The change to a global calculation chain was in Excel 2002, not Excel
2003.

2. I don't think it is correct that this change will trigger a recalc even
in Manual calculation mode:
try this test:

create a 2 sheet workbook using Excel 97 or Excel 2000

add this UDF

Option Explicit

Function Hello(theRange As Variant)
Application.Volatile
MsgBox "Hello " & theRange
End Function

in sheet1!a1 put
22
in sheet2 somewhere put
=Hello(Sheet1!a1)

Switch to Manual calculation and turn off Recalculate before save.

Press F9 to verify that you get a message on Recalc

Save the workbook as Book1 and close Excel.

Open Excel 2003
Open Book1

On my systems I do not get a Hello message until I press F9

Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"James Snell" <(E-Mail Removed)> wrote in message
news:5B768701-DD90-42E8-A86C-(E-Mail Removed)...
> That's slightly different issue from the one I mentioned, but very useful,
> I'll have to make a note of that.
>
> The problems I've worked with relate to the fact that the entire
> calculation
> tree is different. The instances I've worked on have been specifically
> between during migrations from versions that use sheet based calculation
> chains (so pre-2003) to 2003 onwards where there is a unified calculation
> chain. Basically the result cache format is totally different and
> incompatible with the version it's being loaded into, meaning the recalc
> is
> unavoidable regardless of save status.
>
> "Dave Peterson" wrote:
>
>> If you don't like that feature, you can make a change to the registry:
>>
>> Jim Rech posted this:
>> http://groups.google.com/groups?thre...TNGP11.phx.gbl
>>
>> Each recipient would have to make this same change.
>>
>> James Snell wrote:
>> >
>> > It's worth pointing out that even with manual calculation set you may
>> > till
>> > sometimes get a recalc on opening a workbook under certain
>> > circumstances. If
>> > the version or build of excel you're using has a different calculation
>> > engine
>> > than the file was last saved in is the most common example.
>> >
>> > It's not usually an issue for most folks but it's tripped up a couple
>> > of my
>> > corporate banking clients big-time where they've been populating data
>> > using
>> > an add-in on one box and expecting to rely on cached data elsewhere.
>> >
>> > "Dave Peterson" wrote:
>> >
>> > > In xl2003, I can use:
>> > > tools|Options|calculatation tab
>> > > and change the calculation mode to manual.
>> > >
>> > > johnmasvou wrote:
>> > > >
>> > > > Every time I open a csv file it recalculates every possible formula
>> > > > in my
>> > > > workbook, taking forever to open it. Is there any way to disable
>> > > > this from
>> > > > the Excel menu options rather than in VBA?
>> > >
>> > > --
>> > >
>> > > Dave Peterson
>> > >
>>
>> --
>>
>> Dave Peterson
>>

Thanks charles -

Excellent post - I'll need some cream for my humble pie and I think I'd
better get some ketchup to go with my hat which I shall eat later.

(1) Appears to just be me being mentally deficient.

(2) The second part came from a guy I know at MS who's fairly high up
technically, so there must have been something else in the mix with the
clientthat we both missed. They had calculation definitely set to manual but
were getting #ref's when they opened up a set of xl2000 worksheets in later
versions and the cached data was getting dropped. I was told in good faith
that the result cache was different in the app versions and so wouldn't open
up, but that appears to be wrong.

~James


"Charles Williams" wrote:

> James,
>
> 1. The change to a global calculation chain was in Excel 2002, not Excel
> 2003.
>
> 2. I don't think it is correct that this change will trigger a recalc even
> in Manual calculation mode:
> try this test:
>
> create a 2 sheet workbook using Excel 97 or Excel 2000
>
> add this UDF
>
> Option Explicit
>
> Function Hello(theRange As Variant)
> Application.Volatile
> MsgBox "Hello " & theRange
> End Function
>
> in sheet1!a1 put
> 22
> in sheet2 somewhere put
> =Hello(Sheet1!a1)
>
> Switch to Manual calculation and turn off Recalculate before save.
>
> Press F9 to verify that you get a message on Recalc
>
> Save the workbook as Book1 and close Excel.
>
> Open Excel 2003
> Open Book1
>
> On my systems I do not get a Hello message until I press F9
>
> Charles
> __________________________________________________
> The Excel Calculation Site
> http://www.decisionmodels.com
>
> "James Snell" <(E-Mail Removed)> wrote in message
> news:5B768701-DD90-42E8-A86C-(E-Mail Removed)...
> > That's slightly different issue from the one I mentioned, but very useful,
> > I'll have to make a note of that.
> >
> > The problems I've worked with relate to the fact that the entire
> > calculation
> > tree is different. The instances I've worked on have been specifically
> > between during migrations from versions that use sheet based calculation
> > chains (so pre-2003) to 2003 onwards where there is a unified calculation
> > chain. Basically the result cache format is totally different and
> > incompatible with the version it's being loaded into, meaning the recalc
> > is
> > unavoidable regardless of save status.
> >
> > "Dave Peterson" wrote:
> >
> >> If you don't like that feature, you can make a change to the registry:
> >>
> >> Jim Rech posted this:
> >> http://groups.google.com/groups?thre...TNGP11.phx.gbl
> >>
> >> Each recipient would have to make this same change.
> >>
> >> James Snell wrote:
> >> >
> >> > It's worth pointing out that even with manual calculation set you may
> >> > till
> >> > sometimes get a recalc on opening a workbook under certain
> >> > circumstances. If
> >> > the version or build of excel you're using has a different calculation
> >> > engine
> >> > than the file was last saved in is the most common example.
> >> >
> >> > It's not usually an issue for most folks but it's tripped up a couple
> >> > of my
> >> > corporate banking clients big-time where they've been populating data
> >> > using
> >> > an add-in on one box and expecting to rely on cached data elsewhere.
> >> >
> >> > "Dave Peterson" wrote:
> >> >
> >> > > In xl2003, I can use:
> >> > > tools|Options|calculatation tab
> >> > > and change the calculation mode to manual.
> >> > >
> >> > > johnmasvou wrote:
> >> > > >
> >> > > > Every time I open a csv file it recalculates every possible formula
> >> > > > in my
> >> > > > workbook, taking forever to open it. Is there any way to disable
> >> > > > this from
> >> > > > the Excel menu options rather than in VBA?
> >> > >
> >> > > --
> >> > >
> >> > > Dave Peterson
> >> > >
> >>
> >> --
> >>
> >> Dave Peterson
> >>
>
>
>

Thank you very much for the background info!

I am not very technical in Excel myself but I would like to add that:

1. I am using Excel 2002 to save and reopen these csv files

2. The calculation mode is always set to Manual (without recalculation
before save)

Hence neither of the two seems to be the problem..

Do you have any ideas of how I can fix it for good? or do I need to learn to
live with it :P?


"James Snell" wrote:

> Thanks charles -
>
> Excellent post - I'll need some cream for my humble pie and I think I'd
> better get some ketchup to go with my hat which I shall eat later.
>
> (1) Appears to just be me being mentally deficient.
>
> (2) The second part came from a guy I know at MS who's fairly high up
> technically, so there must have been something else in the mix with the
> clientthat we both missed. They had calculation definitely set to manual but
> were getting #ref's when they opened up a set of xl2000 worksheets in later
> versions and the cached data was getting dropped. I was told in good faith
> that the result cache was different in the app versions and so wouldn't open
> up, but that appears to be wrong.
>
> ~James
>
>
> "Charles Williams" wrote:
>
> > James,
> >
> > 1. The change to a global calculation chain was in Excel 2002, not Excel
> > 2003.
> >
> > 2. I don't think it is correct that this change will trigger a recalc even
> > in Manual calculation mode:
> > try this test:
> >
> > create a 2 sheet workbook using Excel 97 or Excel 2000
> >
> > add this UDF
> >
> > Option Explicit
> >
> > Function Hello(theRange As Variant)
> > Application.Volatile
> > MsgBox "Hello " & theRange
> > End Function
> >
> > in sheet1!a1 put
> > 22
> > in sheet2 somewhere put
> > =Hello(Sheet1!a1)
> >
> > Switch to Manual calculation and turn off Recalculate before save.
> >
> > Press F9 to verify that you get a message on Recalc
> >
> > Save the workbook as Book1 and close Excel.
> >
> > Open Excel 2003
> > Open Book1
> >
> > On my systems I do not get a Hello message until I press F9
> >
> > Charles
> > __________________________________________________
> > The Excel Calculation Site
> > http://www.decisionmodels.com
> >
> > "James Snell" <(E-Mail Removed)> wrote in message
> > news:5B768701-DD90-42E8-A86C-(E-Mail Removed)...
> > > That's slightly different issue from the one I mentioned, but very useful,
> > > I'll have to make a note of that.
> > >
> > > The problems I've worked with relate to the fact that the entire
> > > calculation
> > > tree is different. The instances I've worked on have been specifically
> > > between during migrations from versions that use sheet based calculation
> > > chains (so pre-2003) to 2003 onwards where there is a unified calculation
> > > chain. Basically the result cache format is totally different and
> > > incompatible with the version it's being loaded into, meaning the recalc
> > > is
> > > unavoidable regardless of save status.
> > >
> > > "Dave Peterson" wrote:
> > >
> > >> If you don't like that feature, you can make a change to the registry:
> > >>
> > >> Jim Rech posted this:
> > >> http://groups.google.com/groups?thre...TNGP11.phx.gbl
> > >>
> > >> Each recipient would have to make this same change.
> > >>
> > >> James Snell wrote:
> > >> >
> > >> > It's worth pointing out that even with manual calculation set you may
> > >> > till
> > >> > sometimes get a recalc on opening a workbook under certain
> > >> > circumstances. If
> > >> > the version or build of excel you're using has a different calculation
> > >> > engine
> > >> > than the file was last saved in is the most common example.
> > >> >
> > >> > It's not usually an issue for most folks but it's tripped up a couple
> > >> > of my
> > >> > corporate banking clients big-time where they've been populating data
> > >> > using
> > >> > an add-in on one box and expecting to rely on cached data elsewhere.
> > >> >
> > >> > "Dave Peterson" wrote:
> > >> >
> > >> > > In xl2003, I can use:
> > >> > > tools|Options|calculatation tab
> > >> > > and change the calculation mode to manual.
> > >> > >
> > >> > > johnmasvou wrote:
> > >> > > >
> > >> > > > Every time I open a csv file it recalculates every possible formula
> > >> > > > in my
> > >> > > > workbook, taking forever to open it. Is there any way to disable
> > >> > > > this from
> > >> > > > the Excel menu options rather than in VBA?
> > >> > >
> > >> > > --
> > >> > >
> > >> > > Dave Peterson
> > >> > >
> > >>
> > >> --
> > >>
> > >> Dave Peterson
> > >>
> >
> >
> >

I do not know of a simple way to stop this happening.

The only way I have found is to use VBA to switch .EnableCalculation to
false for each worksheet in the slow workbook. Here are 2 macros to do that.
The usage scenario is:

Open your slow workbook.
Run the SwitchOff macro
open you CSV files

If you want to recalculate your slow workbook then
run the switchOn macro and press F9

Sub SwitchOff()
Dim osht As Worksheet
For Each osht In ActiveWorkbook
osht.EnableCalculation = False
Next osht

Set osht = Nothing
End Sub

Sub SwitchOn()
Dim osht As Worksheet
For Each osht In ActiveWorkbook
osht.EnableCalculation = True
Next osht

Set osht = Nothing
End Sub

regards
Charles

"johnmasvou" <(E-Mail Removed)> wrote in message
news:0D521C0A-E339-4499-B269-(E-Mail Removed)...
> Thank you very much for the background info!
>
> I am not very technical in Excel myself but I would like to add that:
>
> 1. I am using Excel 2002 to save and reopen these csv files
>
> 2. The calculation mode is always set to Manual (without recalculation
> before save)
>
> Hence neither of the two seems to be the problem..
>
> Do you have any ideas of how I can fix it for good? or do I need to learn
> to
> live with it :P?
>
>
> "James Snell" wrote:
>
>> Thanks charles -
>>
>> Excellent post - I'll need some cream for my humble pie and I think I'd
>> better get some ketchup to go with my hat which I shall eat later.
>>
>> (1) Appears to just be me being mentally deficient.
>>
>> (2) The second part came from a guy I know at MS who's fairly high up
>> technically, so there must have been something else in the mix with the
>> clientthat we both missed. They had calculation definitely set to manual
>> but
>> were getting #ref's when they opened up a set of xl2000 worksheets in
>> later
>> versions and the cached data was getting dropped. I was told in good
>> faith
>> that the result cache was different in the app versions and so wouldn't
>> open
>> up, but that appears to be wrong.
>>
>> ~James
>>
>>
>> "Charles Williams" wrote:
>>
>> > James,
>> >
>> > 1. The change to a global calculation chain was in Excel 2002, not
>> > Excel
>> > 2003.
>> >
>> > 2. I don't think it is correct that this change will trigger a recalc
>> > even
>> > in Manual calculation mode:
>> > try this test:
>> >
>> > create a 2 sheet workbook using Excel 97 or Excel 2000
>> >
>> > add this UDF
>> >
>> > Option Explicit
>> >
>> > Function Hello(theRange As Variant)
>> > Application.Volatile
>> > MsgBox "Hello " & theRange
>> > End Function
>> >
>> > in sheet1!a1 put
>> > 22
>> > in sheet2 somewhere put
>> > =Hello(Sheet1!a1)
>> >
>> > Switch to Manual calculation and turn off Recalculate before save.
>> >
>> > Press F9 to verify that you get a message on Recalc
>> >
>> > Save the workbook as Book1 and close Excel.
>> >
>> > Open Excel 2003
>> > Open Book1
>> >
>> > On my systems I do not get a Hello message until I press F9
>> >
>> > Charles
>> > __________________________________________________
>> > The Excel Calculation Site
>> > http://www.decisionmodels.com
>> >
>> > "James Snell" <(E-Mail Removed)> wrote in message
>> > news:5B768701-DD90-42E8-A86C-(E-Mail Removed)...
>> > > That's slightly different issue from the one I mentioned, but very
>> > > useful,
>> > > I'll have to make a note of that.
>> > >
>> > > The problems I've worked with relate to the fact that the entire
>> > > calculation
>> > > tree is different. The instances I've worked on have been
>> > > specifically
>> > > between during migrations from versions that use sheet based
>> > > calculation
>> > > chains (so pre-2003) to 2003 onwards where there is a unified
>> > > calculation
>> > > chain. Basically the result cache format is totally different and
>> > > incompatible with the version it's being loaded into, meaning the
>> > > recalc
>> > > is
>> > > unavoidable regardless of save status.
>> > >
>> > > "Dave Peterson" wrote:
>> > >
>> > >> If you don't like that feature, you can make a change to the
>> > >> registry:
>> > >>
>> > >> Jim Rech posted this:
>> > >> http://groups.google.com/groups?thre...TNGP11.phx.gbl
>> > >>
>> > >> Each recipient would have to make this same change.
>> > >>
>> > >> James Snell wrote:
>> > >> >
>> > >> > It's worth pointing out that even with manual calculation set you
>> > >> > may
>> > >> > till
>> > >> > sometimes get a recalc on opening a workbook under certain
>> > >> > circumstances. If
>> > >> > the version or build of excel you're using has a different
>> > >> > calculation
>> > >> > engine
>> > >> > than the file was last saved in is the most common example.
>> > >> >
>> > >> > It's not usually an issue for most folks but it's tripped up a
>> > >> > couple
>> > >> > of my
>> > >> > corporate banking clients big-time where they've been populating
>> > >> > data
>> > >> > using
>> > >> > an add-in on one box and expecting to rely on cached data
>> > >> > elsewhere.
>> > >> >
>> > >> > "Dave Peterson" wrote:
>> > >> >
>> > >> > > In xl2003, I can use:
>> > >> > > tools|Options|calculatation tab
>> > >> > > and change the calculation mode to manual.
>> > >> > >
>> > >> > > johnmasvou wrote:
>> > >> > > >
>> > >> > > > Every time I open a csv file it recalculates every possible
>> > >> > > > formula
>> > >> > > > in my
>> > >> > > > workbook, taking forever to open it. Is there any way to
>> > >> > > > disable
>> > >> > > > this from
>> > >> > > > the Excel menu options rather than in VBA?
>> > >> > >
>> > >> > > --
>> > >> > >
>> > >> > > Dave Peterson
>> > >> > >
>> > >>
>> > >> --
>> > >>
>> > >> Dave Peterson
>> > >>
>> >
>> >
>> >