Using C# and Office Interop, I am opening 2 different copies of a
workbook (I manually copy workbook1.xls to workbook2.xls).Both
workbook1 and workbook2 have exactly same copies of the macros. From
the interop, I am calling macros in workbook1 and workbook2. I am
able to do this successfully but I am worried about 1 thing. At
different points in the macro, I call application.calculate and then
reset it back to manual. My worry is that the calculation settings in
one workbook would affect the processing of the other workbook.
Anybody can throw light on this?

my first thought is that it would only affect the workbook that is active at
the time the code is executed.

"snoopydoopy" wrote:

Using C# and Office Interop, I am opening 2 different copies of a
workbook (I manually copy workbook1.xls to workbook2.xls).Both
workbook1 and workbook2 have exactly same copies of the macros. From
the interop, I am calling macros in workbook1 and workbook2. I am
able to do this successfully but I am worried about 1 thing. At
different points in the macro, I call application.calculate and then
reset it back to manual. My worry is that the calculation settings in
one workbook would affect the processing of the other workbook.
Anybody can throw light on this?

The Application.Calculation property is Application-wide. This means all
workbooks are affected when it is changed. Moreover, the state of
Application.Calculation is stored with a workbook when it is saved, so next
time you open that workbook, Application.Calculation changes to the state it
was in when that workbook was last saved. Confusing and sometimes
problematic.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"JLGWhiz" wrote in message
...
my first thought is that it would only affect the workbook that is active
at
the time the code is executed.

"snoopydoopy" wrote:

Using C# and Office Interop, I am opening 2 different copies of a
workbook (I manually copy workbook1.xls to workbook2.xls).Both
workbook1 and workbook2 have exactly same copies of the macros. From
the interop, I am calling macros in workbook1 and workbook2. I am
able to do this successfully but I am worried about 1 thing. At
different points in the macro, I call application.calculate and then
reset it back to manual. My worry is that the calculation settings in
one workbook would affect the processing of the other workbook.
Anybody can throw light on this?

Application level calculation settings are just that, so changing the
setting may affect calculation in all workbooks in that Instance. Calling
Application.Calculate will do a full recalc of all open workbooks.
Worksheets also have individual an EnableCalculation property, maybe
disabling sheet calc's might serve your needs.

Not sure what you want to recalc, note you can also recalc individual sheets
or even a range (subject the sheet's EnableCalculation = true).

If you are changing application level settings normally you should reset it
to original state when done. Resetting calc to automatic will do a full
recalc of all open workbooks.

Regards,
Peter T


"snoopydoopy" wrote in message
...
Using C# and Office Interop, I am opening 2 different copies of a
workbook (I manually copy workbook1.xls to workbook2.xls).Both
workbook1 and workbook2 have exactly same copies of the macros. From
the interop, I am calling macros in workbook1 and workbook2. I am
able to do this successfully but I am worried about 1 thing. At
different points in the macro, I call application.calculate and then
reset it back to manual. My worry is that the calculation settings in
one workbook would affect the processing of the other workbook.
Anybody can throw light on this?

On Apr 7, 12:47 pm, snoopydoopy wrote:
Using C# and Office Interop, I am opening 2 different copies of a
workbook (I manually copy workbook1.xls to workbook2.xls).Both
workbook1 and workbook2 have exactly same copies of the macros. From
the interop, I am calling macros in workbook1 and workbook2. I am
able to do this successfully but I am worried about 1 thing. At
different points in the macro, I call application.calculate and then
reset it back to manual. My worry is that the calculation settings in
one workbook would affect the processing of the other workbook.
Anybody can throw light on this?

I just realized that in the Macro, I am doing lot of copy paste. So
if there are multiple instances or excel running and each of them is
executing macros with lot of copy and paste commands then the result
is going to be very screwed up. So I need to write code which would
do cell by cell explicit copy.

It also makes me think that you're going to want to watch out for any use of
ActiveWorkbook or ActiveSheet or any 'Active' object, and go with
'ThisWorkbook' during definition/assignment of objects and actions.

"snoopydoopy" wrote:

On Apr 7, 12:47 pm, snoopydoopy wrote:
Using C# and Office Interop, I am opening 2 different copies of a
workbook (I manually copy workbook1.xls to workbook2.xls).Both
workbook1 and workbook2 have exactly same copies of the macros. From
the interop, I am calling macros in workbook1 and workbook2. I am
able to do this successfully but I am worried about 1 thing. At
different points in the macro, I call application.calculate and then
reset it back to manual. My worry is that the calculation settings in
one workbook would affect the processing of the other workbook.
Anybody can throw light on this?

I just realized that in the Macro, I am doing lot of copy paste. So
if there are multiple instances or excel running and each of them is
executing macros with lot of copy and paste commands then the result
is going to be very screwed up. So I need to write code which would
do cell by cell explicit copy.

On Apr 7, 7:42 pm, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote:
It also makes me think that you're going to want to watch out for any use of
ActiveWorkbook or ActiveSheet or any 'Active' object, and go with
'ThisWorkbook' during definition/assignment of objects and actions.

"snoopydoopy" wrote:
On Apr 7, 12:47 pm, snoopydoopy wrote:
Using C# and Office Interop, I am opening 2 different copies of a
workbook (I manually copy workbook1.xls to workbook2.xls).Both
workbook1 and workbook2 have exactly same copies of the macros. From
the interop, I am calling macros in workbook1 and workbook2. I am
able to do this successfully but I am worried about 1 thing. At
different points in the macro, I call application.calculate and then
reset it back to manual. My worry is that the calculation settings in
one workbook would affect the processing of the other workbook.
Anybody can throw light on this?

I just realized that in the Macro, I am doing lot of copy paste. So
if there are multiple instances or excel running and each of them is
executing macros with lot of copy and paste commands then the result
is going to be very screwed up. So I need to write code which would
do cell by cell explicit copy.

Yeah, good point. I had taken care of part of it but need to remove
the selection portion for printing and stuff. I find it fascinating
that other people haven't felt the need to run things in parallel to
exploit the multiprocessor scenario. I know excel 2007 does threading
behind the scenes to speed up calculations but still I think there is
a definite need to run lot of calculations in parallel.

On Apr 7, 8:35 pm, snoopydoopy wrote:
On Apr 7, 7:42 pm, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote:



It also makes me think that you're going to want to watch out for any use of
ActiveWorkbook or ActiveSheet or any 'Active' object, and go with
'ThisWorkbook' during definition/assignment of objects and actions.

"snoopydoopy" wrote:
On Apr 7, 12:47 pm, snoopydoopy wrote:
Using C# and Office Interop, I am opening 2 different copies of a
workbook (I manually copy workbook1.xls to workbook2.xls).Both
workbook1 and workbook2 have exactly same copies of the macros. From
the interop, I am calling macros in workbook1 and workbook2. I am
able to do this successfully but I am worried about 1 thing. At
different points in the macro, I call application.calculate and then
reset it back to manual. My worry is that the calculation settings in
one workbook would affect the processing of the other workbook.
Anybody can throw light on this?

I just realized that in the Macro, I am doing lot of copy paste. So
if there are multiple instances or excel running and each of them is
executing macros with lot of copy and paste commands then the result
is going to be very screwed up. So I need to write code which would
do cell by cell explicit copy.

Yeah, good point. I had taken care of part of it but need to remove
the selection portion for printing and stuff. I find it fascinating
that other people haven't felt the need to run things in parallel to
exploit the multiprocessor scenario. I know excel 2007 does threading
behind the scenes to speed up calculations but still I think there is
a definite need to run lot of calculations in parallel.

I had to change all the copy and paste commands to explicit cell by
cell assignment and this reduced the overall speed of my
calculations.

Without seeing any of your code, it's hard to tell where you might make
improvements. I can't see that cell by cell is more reliable than range by
range. Could you do range by range transfer of .value, instead of
copy/paste? For example:

Worksheets(1).Range("A1:D10").Value = Worksheets(2).Range("F21:I30").value

No clipboard required.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"snoopydoopy" wrote in message
...
On Apr 7, 8:35 pm, snoopydoopy wrote:
On Apr 7, 7:42 pm, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote:



It also makes me think that you're going to want to watch out for any
use of
ActiveWorkbook or ActiveSheet or any 'Active' object, and go with
'ThisWorkbook' during definition/assignment of objects and actions.

"snoopydoopy" wrote:
On Apr 7, 12:47 pm, snoopydoopy wrote:
Using C# and Office Interop, I am opening 2 different copies of a
workbook (I manually copy workbook1.xls to workbook2.xls).Both
workbook1 and workbook2 have exactly same copies of the macros.
From
the interop, I am calling macros in workbook1 and workbook2. I am
able to do this successfully but I am worried about 1 thing. At
different points in the macro, I call application.calculate and
then
reset it back to manual. My worry is that the calculation settings
in
one workbook would affect the processing of the other workbook.
Anybody can throw light on this?

I just realized that in the Macro, I am doing lot of copy paste. So
if there are multiple instances or excel running and each of them is
executing macros with lot of copy and paste commands then the result
is going to be very screwed up. So I need to write code which would
do cell by cell explicit copy.

Yeah, good point. I had taken care of part of it but need to remove
the selection portion for printing and stuff. I find it fascinating
that other people haven't felt the need to run things in parallel to
exploit the multiprocessor scenario. I know excel 2007 does threading
behind the scenes to speed up calculations but still I think there is
a definite need to run lot of calculations in parallel.

I had to change all the copy and paste commands to explicit cell by
cell assignment and this reduced the overall speed of my
calculations.

On Apr 8, 3:11 pm, "Jon Peltier"
wrote:
Without seeing any of your code, it's hard to tell where you might make
improvements. I can't see that cell by cell is more reliable than range by
range. Could you do range by range transfer of .value, instead of
copy/paste? For example:

Worksheets(1).Range("A1:D10").Value = Worksheets(2).Range("F21:I30").value

No clipboard required.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

"snoopydoopy" wrote in message

...

On Apr 7, 8:35 pm, snoopydoopy wrote:
On Apr 7, 7:42 pm, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote:

It also makes me think that you're going to want to watch out for any
use of
ActiveWorkbook or ActiveSheet or any 'Active' object, and go with
'ThisWorkbook' during definition/assignment of objects and actions.

"snoopydoopy" wrote:
On Apr 7, 12:47 pm, snoopydoopy wrote:
Using C# and Office Interop, I am opening 2 different copies of a
workbook (I manually copy workbook1.xls to workbook2.xls).Both
workbook1 and workbook2 have exactly same copies of the macros.
From
the interop, I am calling macros in workbook1 and workbook2. I am
able to do this successfully but I am worried about 1 thing. At
different points in the macro, I call application.calculate and
then
reset it back to manual. My worry is that the calculation settings
in
one workbook would affect the processing of the other workbook.
Anybody can throw light on this?

I just realized that in the Macro, I am doing lot of copy paste. So
if there are multiple instances or excel running and each of them is
executing macros with lot of copy and paste commands then the result
is going to be very screwed up. So I need to write code which would
do cell by cell explicit copy.

Yeah, good point. I had taken care of part of it but need to remove
the selection portion for printing and stuff. I find it fascinating
that other people haven't felt the need to run things in parallel to
exploit the multiprocessor scenario. I know excel 2007 does threading
behind the scenes to speed up calculations but still I think there is
a definite need to run lot of calculations in parallel.

I had to change all the copy and paste commands to explicit cell by
cell assignment and this reduced the overall speed of my
calculations.

Jon, yes this would be just fabulous. I was not aware that I could
write it like that. Also, I am thinking that I would be better of
using Excel Computation Services rather than writing my own c# code to
achieve parallelism. Will most more developments here.

On Apr 8, 4:20 pm, snoopydoopy wrote:
On Apr 8, 3:11 pm, "Jon Peltier"
wrote:



Without seeing any of your code, it's hard to tell where you might make
improvements. I can't see that cell by cell is more reliable than range by
range. Could you do range by range transfer of .value, instead of
copy/paste? For example:

Worksheets(1).Range("A1:D10").Value = Worksheets(2).Range("F21:I30").value

No clipboard required.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

"snoopydoopy" wrote in message

...

On Apr 7, 8:35 pm, snoopydoopy wrote:
On Apr 7, 7:42 pm, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote:

It also makes me think that you're going to want to watch out for any
use of
ActiveWorkbook or ActiveSheet or any 'Active' object, and go with
'ThisWorkbook' during definition/assignment of objects and actions.

"snoopydoopy" wrote:
On Apr 7, 12:47 pm, snoopydoopy wrote:
Using C# and Office Interop, I am opening 2 different copies of a
workbook (I manually copy workbook1.xls to workbook2.xls).Both
workbook1 and workbook2 have exactly same copies of the macros.
From
the interop, I am calling macros in workbook1 and workbook2. I am
able to do this successfully but I am worried about 1 thing. At
different points in the macro, I call application.calculate and
then
reset it back to manual. My worry is that the calculation settings
in
one workbook would affect the processing of the other workbook.
Anybody can throw light on this?

I just realized that in the Macro, I am doing lot of copy paste. So
if there are multiple instances or excel running and each of them is
executing macros with lot of copy and paste commands then the result
is going to be very screwed up. So I need to write code which would
do cell by cell explicit copy.

Yeah, good point. I had taken care of part of it but need to remove
the selection portion for printing and stuff. I find it fascinating
that other people haven't felt the need to run things in parallel to
exploit the multiprocessor scenario. I know excel 2007 does threading
behind the scenes to speed up calculations but still I think there is
a definite need to run lot of calculations in parallel.

I had to change all the copy and paste commands to explicit cell by
cell assignment and this reduced the overall speed of my
calculations.

Jon, yes this would be just fabulous. I was not aware that I could
write it like that. Also, I am thinking that I would be better of
using Excel Computation Services rather than writing my own c# code to
achieve parallelism. Will most more developments here.

Jon, Does it matter if I ma using Application.ActivePrinter in the
macro and more than one workbook can be printing at the same time?

On Apr 8, 3:11 pm, "Jon Peltier"
wrote:
Without seeing any of your code, it's hard to tell where you might make
improvements. I can't see that cell by cell is more reliable than range by
range. Could you do range by range transfer of .value, instead of
copy/paste? For example:

Worksheets(1).Range("A1:D10").Value = Worksheets(2).Range("F21:I30").value

No clipboard required.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

"snoopydoopy" wrote in message

...

On Apr 7, 8:35 pm, snoopydoopy wrote:
On Apr 7, 7:42 pm, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote:

It also makes me think that you're going to want to watch out for any
use of
ActiveWorkbook or ActiveSheet or any 'Active' object, and go with
'ThisWorkbook' during definition/assignment of objects and actions.

"snoopydoopy" wrote:
On Apr 7, 12:47 pm, snoopydoopy wrote:
Using C# and Office Interop, I am opening 2 different copies of a
workbook (I manually copy workbook1.xls to workbook2.xls).Both
workbook1 and workbook2 have exactly same copies of the macros.
From
the interop, I am calling macros in workbook1 and workbook2. I am
able to do this successfully but I am worried about 1 thing. At
different points in the macro, I call application.calculate and
then
reset it back to manual. My worry is that the calculation settings
in
one workbook would affect the processing of the other workbook.
Anybody can throw light on this?

I just realized that in the Macro, I am doing lot of copy paste. So
if there are multiple instances or excel running and each of them is
executing macros with lot of copy and paste commands then the result
is going to be very screwed up. So I need to write code which would
do cell by cell explicit copy.

Yeah, good point. I had taken care of part of it but need to remove
the selection portion for printing and stuff. I find it fascinating
that other people haven't felt the need to run things in parallel to
exploit the multiprocessor scenario. I know excel 2007 does threading
behind the scenes to speed up calculations but still I think there is
a definite need to run lot of calculations in parallel.

I had to change all the copy and paste commands to explicit cell by
cell assignment and this reduced the overall speed of my
calculations.

My copy code is as follows:

Sub CopyRange(startRange As Range, EndRangeOrAddress As Range)
Range(EndRangeOrAddress.Address,
EndRangeOrAddress.Offset(startRange.Rows.Count - 1,
startRange.Columns.Count - 1).Address).Value = startRange.Value
End Sub

And I am calling this macro like

Call
CopyRange(ThisWorkbook.Sheets("sheet1").Range("a1: b10"),ThisWorkbook.Sheets("sheet2").Range("a1"))

If I use .Value and I have formula in the source range, the data does
not get copied.

If I use .value2 then the values do not show up in the cells but I can
see them in the watch window of the debugger. This is really bizarre.

I will have to debug this more to find out what is going on.

I would assume the OS handles printer scheduling.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"snoopydoopy" wrote in message
...
On Apr 8, 4:20 pm, snoopydoopy wrote:
On Apr 8, 3:11 pm, "Jon Peltier"
wrote:



Without seeing any of your code, it's hard to tell where you might make
improvements. I can't see that cell by cell is more reliable than range
by
range. Could you do range by range transfer of .value, instead of
copy/paste? For example:

Worksheets(1).Range("A1:D10").Value =
Worksheets(2).Range("F21:I30").value

No clipboard required.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

"snoopydoopy" wrote in message

...

On Apr 7, 8:35 pm, snoopydoopy wrote:
On Apr 7, 7:42 pm, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote:

It also makes me think that you're going to want to watch out for
any
use of
ActiveWorkbook or ActiveSheet or any 'Active' object, and go with
'ThisWorkbook' during definition/assignment of objects and
actions.

"snoopydoopy" wrote:
On Apr 7, 12:47 pm, snoopydoopy wrote:
Using C# and Office Interop, I am opening 2 different copies
of a
workbook (I manually copy workbook1.xls to workbook2.xls).Both
workbook1 and workbook2 have exactly same copies of the
macros.
From
the interop, I am calling macros in workbook1 and workbook2.
I am
able to do this successfully but I am worried about 1 thing.
At
different points in the macro, I call application.calculate
and
then
reset it back to manual. My worry is that the calculation
settings
in
one workbook would affect the processing of the other
workbook.
Anybody can throw light on this?

I just realized that in the Macro, I am doing lot of copy paste.
So
if there are multiple instances or excel running and each of
them is
executing macros with lot of copy and paste commands then the
result
is going to be very screwed up. So I need to write code which
would
do cell by cell explicit copy.

Yeah, good point. I had taken care of part of it but need to remove
the selection portion for printing and stuff. I find it fascinating
that other people haven't felt the need to run things in parallel to
exploit the multiprocessor scenario. I know excel 2007 does
threading
behind the scenes to speed up calculations but still I think there
is
a definite need to run lot of calculations in parallel.

I had to change all the copy and paste commands to explicit cell by
cell assignment and this reduced the overall speed of my
calculations.

Jon, yes this would be just fabulous. I was not aware that I could
write it like that. Also, I am thinking that I would be better of
using Excel Computation Services rather than writing my own c# code to
achieve parallelism. Will most more developments here.

Jon, Does it matter if I ma using Application.ActivePrinter in the
macro and more than one workbook can be printing at the same time?

Simplify (watch the line wrap, this is one line)...

startRange.Resize(EndRangeOrAddress.Rows.Count,
EndRangeOrAddress.Columns.Count).Value = EndRangeOrAddress.Value

or

With EndRangeOrAddress
startRange.Resize(.Rows.Count, .Columns.Count).Value = .Value
End With

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"snoopydoopy" wrote in message
...
On Apr 8, 3:11 pm, "Jon Peltier"
wrote:
Without seeing any of your code, it's hard to tell where you might make
improvements. I can't see that cell by cell is more reliable than range
by
range. Could you do range by range transfer of .value, instead of
copy/paste? For example:

Worksheets(1).Range("A1:D10").Value =
Worksheets(2).Range("F21:I30").value

No clipboard required.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

"snoopydoopy" wrote in message

...

On Apr 7, 8:35 pm, snoopydoopy wrote:
On Apr 7, 7:42 pm, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote:

It also makes me think that you're going to want to watch out for
any
use of
ActiveWorkbook or ActiveSheet or any 'Active' object, and go with
'ThisWorkbook' during definition/assignment of objects and actions.

"snoopydoopy" wrote:
On Apr 7, 12:47 pm, snoopydoopy wrote:
Using C# and Office Interop, I am opening 2 different copies of
a
workbook (I manually copy workbook1.xls to workbook2.xls).Both
workbook1 and workbook2 have exactly same copies of the macros.
From
the interop, I am calling macros in workbook1 and workbook2. I
am
able to do this successfully but I am worried about 1 thing. At
different points in the macro, I call application.calculate and
then
reset it back to manual. My worry is that the calculation
settings
in
one workbook would affect the processing of the other workbook.
Anybody can throw light on this?

I just realized that in the Macro, I am doing lot of copy paste.
So
if there are multiple instances or excel running and each of them
is
executing macros with lot of copy and paste commands then the
result
is going to be very screwed up. So I need to write code which
would
do cell by cell explicit copy.

Yeah, good point. I had taken care of part of it but need to remove
the selection portion for printing and stuff. I find it fascinating
that other people haven't felt the need to run things in parallel to
exploit the multiprocessor scenario. I know excel 2007 does threading
behind the scenes to speed up calculations but still I think there is
a definite need to run lot of calculations in parallel.

I had to change all the copy and paste commands to explicit cell by
cell assignment and this reduced the overall speed of my
calculations.

My copy code is as follows:

Sub CopyRange(startRange As Range, EndRangeOrAddress As Range)
Range(EndRangeOrAddress.Address,
EndRangeOrAddress.Offset(startRange.Rows.Count - 1,
startRange.Columns.Count - 1).Address).Value = startRange.Value
End Sub

And I am calling this macro like

Call
CopyRange(ThisWorkbook.Sheets("sheet1").Range("a1: b10"),ThisWorkbook.Sheets("sheet2").Range("a1"))

If I use .Value and I have formula in the source range, the data does
not get copied.

If I use .value2 then the values do not show up in the cells but I can
see them in the watch window of the debugger. This is really bizarre.

I will have to debug this more to find out what is going on.