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.

So, when I having report opening event Excel performs calculation first
time with cells contains text data, than my add-in converts text to
values, and finally Excel calculates reports with the normal data
(dates, numbers).

I need to avoid time spending to perform first calculation when I have
text instead of values because it useless.

Do you have any suggestions?

BR,
Sergey.

Dave Peterson wrote:
Maybe you can try this.

Open excel
create a new workbook--so you can change calculation to manual.
Then open your addin.

Do you have the same problem?

I'm guessing that calculation is set to automatic. When the addin opens, the
calculation occurs _before_ your code is run (that's the way excel works). Then
your code toggles the calculation setting, does some work and toggles the
calculation mode--which causes the workbook to be recalculated a second time.

The only way I know to stop it is to make sure calculation is set to manual,
then open your addin.

You could do it manually or you could have another workbook that opens, changes
calculation to manual, opens your addin, and closes itself. Kind of helper
workbook.



Sergiy wrote:

Dear Colleagues,

Please advise with the subject, details are below.

I am getting exported data to excel and this data is in text format
which I am processing to get the values. The macro to process it is
located inside of add-in and it intercepts Application.WorkBookOpen
Event. The point is that excel is calculating twice, before my macro
runs and after. How I can prevent excel from double work? I have
workbooks with a lot of heavy formulas and to calculate all of them
take a time.

Below is my code:

Private Sub App_WorkbookOpen(ByVal wb As Workbook)
'Speed up the process
Application.Interactive = False
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'Exit from Sub when no workbooks opened
If Application.Workbooks.Count < 1 Then
Exit Sub
End If

'Perform report manipulations here
Call ctApplyFormatting()
Call ctConvertToValues()

'Restoring Application default behaviour
Application.Interactive = True
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Thank you in advance.

BR,
Sergiy

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson