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

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

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

IHi,
Its again me.
I forgot some important details
My add-in is located in XLStart folder on a server, so it runs when any
user opens Excel and remains active before Excel closed.
BR,
Sergey.

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

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

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

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

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

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.

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

--

Dave Peterson

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.

It's a combination.

It's stored with each workbook. But the application only changes when it opens
the first workbook in that session. And there's only one setting for all open
workbooks.

Sergiy wrote:

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.

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

--

Dave Peterson

--

Dave Peterson