Hi Dave,

Let me clarify, the Calculation mode again:

1) Is it a property stored at Excell level
or
2) Workbook level?

Where is this setting stored?

It seems not clear for me from now.


BR,
Sergey



Dave Peterson писал(а):

The bad news is that excel picks up this setting from the first workbook it
opens in that session.

So if they open a workbook that's been saved in manual calculation mode first,
you'll be fine.

But if they open a workbook that's been saved in automatic calculation mode
first, you'll be back to square one.

And for me, I've found that almost all my workbooks are saved in automatic
calculation mode. (Actually, it isn't almost all--it is all.)



Sergiy wrote:

Thanx Dave.
Your Idea works great.

At the same time I found one more solution without creation of new
workbook . It works in my case pretty well because during a day reports
generated quite offen.

The Idea is to set the calculation mode to manual before user
closing/saving workbook. At least for second time it will be opened
with already manual calculation mode.

Here what I did

Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As
Boolean)
Application.Calculation = xlCalculationManual
End Sub

Thanx again for inspiration

BR,
Serhij

Dave Peterson писал(а):

You have to have an open workbook to change calculation mode--if there isn't one
open, just create new one (like file|new).

Sergiy wrote:

Hi Dave!

How can I do that without workbook has been opened?
Otherwise I am getting error message.
BR,
Serhij

Dave Peterson писал(а):

Only to change the calculation mode before you open that workbook.

Or maybe have your addin provide a way to open the file (have the user stop
using file|open or double clicking). Then it could turn the calculation mode to
manual, open the file, do the work and change the calculation mode back to what
it was.

And instead of using an application event, you could just put all the process
into a macro called by an icon on a toolbar (invoked by the user).

If you could even password protect the other workbooks, then the users would
have to go through your open routine to get to their data.


Sergiy wrote:

Hi Dave.
It seems that you again helping me.
The way you propose may not be applicable in my case.

I have a database which makes reports export to excel files and just
after export completed it launches Excel to open the file
created/modified. Users have the possibility to create new reports with
any information they want. All of this is not under my control.

The worst thing is that all exported data is forwarded by apostrophe in
front of cell value (text, numbers, dates all of them are having this
apostrophes in front of). Its makes exported data treated as text and
it is not useful within any calculations performed by report. Any
formulas in spredsheet don't works in a proper way without a
conversion text to values.

Last time I asked a help to deal with App.WorkbookOpen event and with
your help I did it. I wrote add-in which monitors opened files and when
it founds specific mark (all these reports has named data range to
flush data in it) than it runs a macro to convert text with apostrophes
to values: if it founds that text is may be a number to number; if it
founds that text is may be a date to date; and all others to text.