Hello All,

I've got a routine which opens 40 workbooks - copies in some data -
copies out some data - then closes each workbook saving changes. Each
Save takes approx 40sec due to the size of each wkbk and many
calculations involved. The macro therefore takes 25mins to run.

Not worrying too much about the length of time, I went ahead and wrote
the macro, as I thought I'd just run it in a second open instance of
Excel and still be able to do other things in another instance.

Problem is that the Copying & Pasting means that even if I'm working
on SQL Server, whilst this macro is running, if I try to copy and
paste in other applications it seems like the routine takes over the
clipboard. Sometimes if I copy/paste in another app the routine will
crash - again as I think the clipboard is causing a problem.

Anyone ever come across this before and know a way around it.

Any help much appreciated
Jason

(Using Excel 2007)

You can assign data from one range to another directly, or store it in an
array until needed

Sub test()
Dim rSource As Range, rDest1 As Range

Set rSource = ActiveWorkbook.Worksheets(1).Range("A1:B3")
rSource.Value = "data"

Set rDest = ActiveWorkbook.Worksheets(2).Range("D4")
With rSource
Set rDest = rDest.Resize(.Rows.Count, .Columns.Count)
End With

' copy data directly
rDest.Value = rSource.Value

' or store in an array until for later use, say after source wb has
closed
arr = rSource.Value

Set rDest = ActiveWorkbook.Worksheets(2).Range("H4")
With rSource
Set rDest = rDest.Resize(.Rows.Count, .Columns.Count)
End With

rDest.Value = arr
ActiveWorkbook.Worksheets(2).Activate
End Sub

Probably best not to copy more than say 20-50k cells this way in one go,
with larger sizes do in multiple steps

Regards,
Peter T
wrote in message
...
Hello All,

I've got a routine which opens 40 workbooks - copies in some data -
copies out some data - then closes each workbook saving changes. Each
Save takes approx 40sec due to the size of each wkbk and many
calculations involved. The macro therefore takes 25mins to run.

Not worrying too much about the length of time, I went ahead and wrote
the macro, as I thought I'd just run it in a second open instance of
Excel and still be able to do other things in another instance.

Problem is that the Copying & Pasting means that even if I'm working
on SQL Server, whilst this macro is running, if I try to copy and
paste in other applications it seems like the routine takes over the
clipboard. Sometimes if I copy/paste in another app the routine will
crash - again as I think the clipboard is causing a problem.

Anyone ever come across this before and know a way around it.

Any help much appreciated
Jason

(Using Excel 2007)

On 17 Apr, 14:21, "Peter T" <peter_t@discussions wrote:
You can assign data from one range to another directly, or store it in an
array until needed

Sub test()
Dim rSource As Range, rDest1 As Range

* * Set rSource = ActiveWorkbook.Worksheets(1).Range("A1:B3")
* * rSource.Value = "data"

* * Set rDest = ActiveWorkbook.Worksheets(2).Range("D4")
* * With rSource
* * * * Set rDest = rDest.Resize(.Rows.Count, .Columns.Count)
* * End With

* * ' copy data directly
* * rDest.Value = rSource.Value

* * ' or store in an array until for later use, say after source wb has
closed
* * arr = rSource.Value

* * Set rDest = ActiveWorkbook.Worksheets(2).Range("H4")
* * With rSource
* * * * Set rDest = rDest.Resize(.Rows.Count, .Columns.Count)
* * End With

* * rDest.Value = arr
* * ActiveWorkbook.Worksheets(2).Activate
End Sub

Probably best not to copy more than say 20-50k cells this way in one go,
with larger sizes do in multiple steps

Regards,
Peter wrote in message

...



Hello All,

I've got a routine which opens 40 workbooks - copies in some data -
copies out some data - then closes each workbook saving changes. Each
Save takes approx 40sec due to the size of each wkbk and many
calculations involved. The macro therefore takes 25mins to run.

Not worrying too much about the length of time, I went ahead and wrote
the macro, as I thought I'd just run it in a second open instance of
Excel and still be able to do other things in another instance.

Problem is that the Copying & Pasting means that even if I'm working
on SQL Server, whilst this macro is running, if I try to copy and
paste in other applications it seems like the routine takes over the
clipboard. Sometimes if I copy/paste in another app the routine will
crash - again as I think the clipboard is causing a problem.

Anyone ever come across this before and know a way around it.

Any help much appreciated
Jason

(Using Excel 2007)- Hide quoted text -

- Show quoted text -


Alright Peter

Brilliant - plenty for me to work on: have you used both methods
previously? Which do you prefer? Which do you use in your current
work?
Also do you know if I could theoretically run some code from one
instance of Excel and then open a second hidden instance to do all the
work in, and then once all 40 wkbks have been updated the hidden
instance could be closed?

Any help much appreciated,
Jason.

Brilliant - plenty for me to work on: have you used both
methods previously? Which do you prefer? Which do you use in
your current work?

By "both methods" I assume you mean rDest.Value = rsource.Value and
rDest.Value = arr

If I know the source range and destination range will both be available at
the same time, there's no need to assign the values to the intermediate
array. Otherwise use the temporary array, eg open one wb, assign the values
to the array, close that wb then open the dest' wb. As I mentioned before,
probably best not to try and do a massive amount of data in one go.

Also do you know if I could theoretically run some code from one
instance of Excel and then open a second hidden instance to do all
the work in, and then once all 40 wkbks have been updated the hidden
instance could be closed?

Depends how you do it. If from Inst1 you create Inst2 but control all the
code from Inst1 you are not gaining anything. However you could open a code
file in created Inst2 that from its Open event starts a new routine called
by an OnTime macro to do all your work then close the itself and the
instance.

Small thing to keep in mind, when you start Inst2 make it and the opened wb
visible, then you can completely release all object references in Inst1, if
you don't make visible the created instance will simply quit as soon as you
destroy the last object pointers to it (or one of it's wb's).

Regards,
Peter T





wrote in message
...
On 17 Apr, 14:21, "Peter T" <peter_t@discussions wrote:
You can assign data from one range to another directly, or store it in an
array until needed

Sub test()
Dim rSource As Range, rDest1 As Range

Set rSource = ActiveWorkbook.Worksheets(1).Range("A1:B3")
rSource.Value = "data"

Set rDest = ActiveWorkbook.Worksheets(2).Range("D4")
With rSource
Set rDest = rDest.Resize(.Rows.Count, .Columns.Count)
End With

' copy data directly
rDest.Value = rSource.Value

' or store in an array until for later use, say after source wb has
closed
arr = rSource.Value

Set rDest = ActiveWorkbook.Worksheets(2).Range("H4")
With rSource
Set rDest = rDest.Resize(.Rows.Count, .Columns.Count)
End With

rDest.Value = arr
ActiveWorkbook.Worksheets(2).Activate
End Sub

Probably best not to copy more than say 20-50k cells this way in one go,
with larger sizes do in multiple steps

Regards,
Peter wrote in message

...



Hello All,

I've got a routine which opens 40 workbooks - copies in some data -
copies out some data - then closes each workbook saving changes. Each
Save takes approx 40sec due to the size of each wkbk and many
calculations involved. The macro therefore takes 25mins to run.

Not worrying too much about the length of time, I went ahead and wrote
the macro, as I thought I'd just run it in a second open instance of
Excel and still be able to do other things in another instance.

Problem is that the Copying & Pasting means that even if I'm working
on SQL Server, whilst this macro is running, if I try to copy and
paste in other applications it seems like the routine takes over the
clipboard. Sometimes if I copy/paste in another app the routine will
crash - again as I think the clipboard is causing a problem.

Anyone ever come across this before and know a way around it.

Any help much appreciated
Jason

(Using Excel 2007)- Hide quoted text -

- Show quoted text -


Alright Peter

Brilliant - plenty for me to work on: have you used both methods
previously? Which do you prefer? Which do you use in your current
work?
Also do you know if I could theoretically run some code from one
instance of Excel and then open a second hidden instance to do all the
work in, and then once all 40 wkbks have been updated the hidden
instance could be closed?

Any help much appreciated,
Jason.

On 17 Apr, 16:34, "Peter T" <peter_t@discussions wrote:
Brilliant - plenty for me to work on: have you used both
methods previously? Which do you prefer? Which do you use in
your current work?

By "both methods" I assume you mean rDest.Value = rsource.Value and
rDest.Value = arr

If I know the source range and destination range will both be available at
the same time, there's no need to assign the values to the intermediate
array. Otherwise use the temporary array, eg open one wb, assign the values
to the array, close that wb then open the dest' wb. As I mentioned before,
probably best not to try and do a massive amount of data in one go.

Also do you know if I could theoretically run some code from one
instance of Excel and then open a second hidden instance to do all
the work in, and then once all 40 wkbks have been updated the hidden
instance could be closed?

Depends how you do it. If from Inst1 you create Inst2 but control all the
code from Inst1 you are not gaining anything. However you could open a code
file in created Inst2 that from its Open event starts a new routine called
by an OnTime macro to do all your work then close the itself and the
instance.

Small thing to keep in mind, when you start Inst2 make it and the opened wb
visible, then you can completely release all object references in Inst1, if
you don't make visible the created instance will simply quit as soon as you
destroy the last object pointers to it (or one of it's wb's).

Regards,
Peter T

wrote in message

...
On 17 Apr, 14:21, "Peter T" <peter_t@discussions wrote:





You can assign data from one range to another directly, or store it in an
array until needed

Sub test()
Dim rSource As Range, rDest1 As Range

Set rSource = ActiveWorkbook.Worksheets(1).Range("A1:B3")
rSource.Value = "data"

Set rDest = ActiveWorkbook.Worksheets(2).Range("D4")
With rSource
Set rDest = rDest.Resize(.Rows.Count, .Columns.Count)
End With

' copy data directly
rDest.Value = rSource.Value

' or store in an array until for later use, say after source wb has
closed
arr = rSource.Value

Set rDest = ActiveWorkbook.Worksheets(2).Range("H4")
With rSource
Set rDest = rDest.Resize(.Rows.Count, .Columns.Count)
End With

rDest.Value = arr
ActiveWorkbook.Worksheets(2).Activate
End Sub

Probably best not to copy more than say 20-50k cells this way in one go,
with larger sizes do in multiple steps

Regards,
Peter wrote in message

...

Hello All,

I've got a routine which opens 40 workbooks - copies in some data -
copies out some data - then closes each workbook saving changes. Each
Save takes approx 40sec due to the size of each wkbk and many
calculations involved. The macro therefore takes 25mins to run.

Not worrying too much about the length of time, I went ahead and wrote
the macro, as I thought I'd just run it in a second open instance of
Excel and still be able to do other things in another instance.

Problem is that the Copying & Pasting means that even if I'm working
on SQL Server, whilst this macro is running, if I try to copy and
paste in other applications it seems like the routine takes over the
clipboard. Sometimes if I copy/paste in another app the routine will
crash - again as I think the clipboard is causing a problem.

Anyone ever come across this before and know a way around it.

Any help much appreciated
Jason

(Using Excel 2007)- Hide quoted text -

- Show quoted text -

Alright Peter

Brilliant - plenty for me to work on: have you used both methods
previously? Which do you prefer? Which do you use in your current
work?
Also do you know if I could theoretically run some code from one
instance of Excel and then open a second hidden instance to do all the
work in, and then once all 40 wkbks have been updated the hidden
instance could be closed?

Any help much appreciated,
Jason.- Hide quoted text -

- Show quoted text -

Thanks for all the help Peter - I'm working on this project.
For my purposes I don't need the second instance to be visible once
the routine has completed so should be ok leaving the visible property
as False (unless you know of other reasons I should make the app
visible). I'll add a small routine in at the end of the macro which
sends me an email so that I know all has completed.

Regards
Jason.

wrote in message

Thanks for all the help Peter - I'm working on this project.
For my purposes I don't need the second instance to be visible once
the routine has completed so should be ok leaving the visible property
as False (unless you know of other reasons I should make the app
visible). I'll add a small routine in at the end of the macro which
sends me an email so that I know all has completed.

I have never thought of having my app send me an email to tell me all is
done. Brilliant:-)

Regards,
Peter T