Routine Interfering with Clipboard

[whytheq]

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)

 

[Peter T]

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

 

[whytheq]

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,











- 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.

 

[Peter T]

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

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,











- 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.

 

[whytheq]

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.


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














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.

 

[Peter T]

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

 

[whytheq]

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

Regards,
Peter T

Hello Peter,

Think an easier method than trying to send an email is just to make
the application visible once all the code has run.

One thing I've just encountered is that my program also uses the
method PasteSpecial xlValues over some quite large ranges - is there
an easy way to avoid the clipboard in these instances?

Regards
Jason.

 

[Peter T]

I thought the whole idea was to avoide the clipboard, following just for
ideas

Sub MakeSampledata()
Dim rng As Range, rCol As Range
Set rng = ActiveSheet.Range("B2:J1000")
For i = 1 To rng.Columns.Count
rng.Columns(i).Value = i
Next

Call Test
End Sub

Sub Test()
Dim nCol As Long
Dim rSource As Range, rDest As Range, rCol As Range
Dim wb As Workbook
Set rSource = ActiveSheet.Range("B2").CurrentRegion

Workbooks.Add
Set rDest = ActiveSheet.Range("C3")
With rSource
Set rDest = rDest.Resize(.Rows.Count, .Columns.Count)
End With

For Each rCol In rSource.Columns
nCol = nCol + 1
rDest.Columns(nCol).Value = rCol.Value
Next

End Sub

Regards,
Peter T

 

[whytheq]

I thought the whole idea was to avoide the clipboard, following just for
ideas

Sub MakeSampledata()
Dim rng As Range, rCol As Range
Set rng = ActiveSheet.Range("B2:J1000")
For i = 1 To rng.Columns.Count
rng.Columns(i).Value = i
Next

Call Test
End Sub

Sub Test()
Dim nCol As Long
Dim rSource As Range, rDest As Range, rCol As Range
Dim wb As Workbook
    Set rSource = ActiveSheet.Range("B2").CurrentRegion

    Workbooks.Add
    Set rDest = ActiveSheet.Range("C3")
    With rSource
        Set rDest = rDest.Resize(.Rows.Count, .Columns.Count)
    End With

    For Each rCol In rSource.Columns
        nCol = nCol + 1
        rDest.Columns(nCol).Value = rCol.Value
    Next

End Sub

Regards,
Peter T










- Show quoted text -

thanks Peter - my confusion.
I'd tested your initial code and it worked perfectly in replacing
rng1.copy/rng2.paste. For some reason I thought I'd need something
more from you to replace rng1.copy/rng2.pastespecial xlvalues .....but
discovered last night that your initial code does this aswell.

thanks again for all the help
jason

 

[whytheq]

thanks Peter - my confusion.
I'd tested your initial code and it worked perfectly in replacing
rng1.copy/rng2.paste. For some reason I thought I'd need something
more from you to replace rng1.copy/rng2.pastespecial xlvalues .....but
discovered last night that your initial code does this aswell.

thanks again for all the help
jason- Hide quoted text -

- Show quoted text -

Ended up with this in App1:

'==================
Dim apXL As Excel.Application
Dim wbXL As Excel.Workbook

Dim rSource As Range
Dim rDest1 As Range

Sub OpenEXLAndWB()

Set apXL = New Excel.Application
apXL.Visible = True
apXL.Workbooks.Open "C:\Excel Experiment 0.1\Data Feed.xlsm"
Set apXL = Nothing

End Sub
'==========================

and then in the workbook Data Feed I've got this event proc:
'==========================
Private Sub Workbook_Open()

Dim ActionTime As Date

ActionTime = Now + TimeValue("00:00:10")
Application.OnTime ActionTime, "CopyData"

End Sub
'==========================

the CopyData macro looks like this:
'==========================
Private wbDestination As Workbook

Private rSource As Range
Private rDest As Range


Sub CopyData()

Application.ScreenUpdating = False

Set wbDestination = Workbooks.Open("C:\Excel Experiment
0.1\Destination.xlsx")
' copy data directly
Set rSource = ThisWorkbook.Worksheets(1).Range("B35")
Set rDest = wbDestination.Worksheets(1).Range("B3")
With rSource
Set rDest = rDest.Resize(.Rows.Count, .Columns.Count)
End With
rDest.Value = rSource.Value
Set rSource = Nothing
Set rDest = Nothing

wbDestination.Close True
Set wbDestination = Nothing

Application.ScreenUpdating = True

End Sub
'=========================

the above is a model really for a much bigger routine: what do you
think? any major problems / improvements?

any help much appreciated
Jason

 

[Peter T]

That looks fine. I might change a few things but only as a matter of style
and organization, on that basis some brief comments -

If the objective is to batch process (as you said in the OP) I might arrange
something like this

Sub A()
set wbS = ThisWorkbook
get an array or collection of wb names to open here
disable screenpdating
on error resume next
for i = 1 to ...
set wbD = nothing
set wbD = workbooks(arr(i))
if not wb is nothing then
If CopyData(wbS, wbD) = False then
write log failed to copy to wbD
else
' report failed to open the wb
end if
next

re-enable screenupdating

clean up advise all done
End sub

Function CopyData(wbSource as workbook, wbDest as Workbook) as boolean
On error goto errH

copy stuff
CopyData = True
Exit Function
errH:
End sub

It's not necessary to explicity destroy object variables declared at
procedure level as you are doing unless you need to for other reasons (like
in my example above, but no harm to do so.


You said something about doing a lot of calculations in each wb, depending
on what it might be worth disbaling calculation and doing a recalc just
before the save (but reset in the clean up).

Regards,
Peter T




Ended up with this in App1:

'==================
Dim apXL As Excel.Application
Dim wbXL As Excel.Workbook

Dim rSource As Range
Dim rDest1 As Range

Sub OpenEXLAndWB()

Set apXL = New Excel.Application
apXL.Visible = True
apXL.Workbooks.Open "C:\Excel Experiment 0.1\Data Feed.xlsm"
Set apXL = Nothing

End Sub
'==========================

and then in the workbook Data Feed I've got this event proc:
'==========================
Private Sub Workbook_Open()

Dim ActionTime As Date

ActionTime = Now + TimeValue("00:00:10")
Application.OnTime ActionTime, "CopyData"

End Sub
'==========================

the CopyData macro looks like this:
'==========================
Private wbDestination As Workbook

Private rSource As Range
Private rDest As Range


Sub CopyData()

Application.ScreenUpdating = False

Set wbDestination = Workbooks.Open("C:\Excel Experiment
0.1\Destination.xlsx")
' copy data directly
Set rSource = ThisWorkbook.Worksheets(1).Range("B35")
Set rDest = wbDestination.Worksheets(1).Range("B3")
With rSource
Set rDest = rDest.Resize(.Rows.Count, .Columns.Count)
End With
rDest.Value = rSource.Value
Set rSource = Nothing
Set rDest = Nothing

wbDestination.Close True
Set wbDestination = Nothing

Application.ScreenUpdating = True

End Sub
'=========================

the above is a model really for a much bigger routine: what do you
think? any major problems / improvements?

any help much appreciated
Jason

 

[whytheq]

That looks fine. I might change a few things but only as a matter of style
and organization, on that basis some brief comments -

If the objective is to batch process (as you said in the OP) I might arrange
something like this

Sub A()
set wbS = ThisWorkbook
get an array or collection of wb names to open here
disable screenpdating
on error resume next
for i = 1 to ...
set wbD = nothing
set wbD = workbooks(arr(i))
if not wb is nothing then
If CopyData(wbS, wbD) = False then
   write log failed to copy to wbD
else
' report failed to open the wb
end if
next

re-enable screenupdating

clean up advise all done
End sub

Function CopyData(wbSource as workbook, wbDest as Workbook) as boolean
On error goto errH

copy stuff
CopyData = True
Exit Function
errH:
End sub

It's not necessary to explicity destroy object variables declared at
procedure level as you are doing unless you need to for other reasons (like
in my example above, but no harm to do so.

You said something about doing a lot of calculations in each wb, depending
on what it might be worth disbaling calculation and doing a recalc just
before the save (but reset in the clean up).

Regards,
Peter T



Ended up with this in App1:

'==================
Dim apXL As Excel.Application
Dim wbXL As Excel.Workbook

Dim rSource As Range
Dim rDest1 As Range

Sub OpenEXLAndWB()

    Set apXL = New Excel.Application
    apXL.Visible = True
    apXL.Workbooks.Open "C:\Excel Experiment 0.1\Data Feed.xlsm"
    Set apXL = Nothing

End Sub
'==========================

and then in the workbook Data Feed I've got this event proc:
'==========================
Private Sub Workbook_Open()

Dim ActionTime As Date

ActionTime = Now + TimeValue("00:00:10")
Application.OnTime ActionTime, "CopyData"

End Sub
'==========================

the CopyData macro looks like this:
'==========================
Private wbDestination As Workbook

Private rSource As Range
Private rDest As Range

Sub CopyData()

Application.ScreenUpdating = False

    Set wbDestination = Workbooks.Open("C:\Excel Experiment
0.1\Destination.xlsx")
        ' copy data directly
        Set rSource = ThisWorkbook.Worksheets(1).Range("B35")
        Set rDest = wbDestination.Worksheets(1).Range("B3")
            With rSource
                Set rDest = rDest.Resize(.Rows.Count, .Columns.Count)
            End With
            rDest.Value = rSource.Value
        Set rSource = Nothing
        Set rDest = Nothing

        wbDestination.Close True
    Set wbDestination = Nothing

Application.ScreenUpdating = True

End Sub
'=========================

the above is a model really for a much bigger routine: what do you
think? any major problems / improvements?

any help much appreciated
Jason

Thanks for all the advice Peter - much appreciated; I'll be in touch
in terms of whether it finally succeeds or hits problems

J

 

[whytheq]

Thanks for all the advice Peter - much appreciated; I'll be in touch
in terms of whether it finally succeeds or hits problems

J- Hide quoted text -

- Show quoted text -

So far the results are amazing - when I was using Copy/paste in a
single instnace of Excel the running time was 30mins - now running it
without the clipboard, in a second instance of excel the running time
is around 5mins ! ....not sure where the saving has been made, but
thanks again.

J

 

[Peter T]

Sounds like a worthwhile gain and thanks for the feedback. I suspect though
this has more to do with re-working the copy/paste method so as not use the
clipboard (and perhaps in chunks?) rather than doing the work in a separate
instance.

Could you give some details of the real life data and size you are dealing
with. Also what version, is any recalc involved and if so is that
(dis)enabled throughout. If Excel 2007 did you previously do similar in an
earlier version and if so any significant differences (with the original
method).

Regards,
Peter T



So far the results are amazing - when I was using Copy/paste in a
single instnace of Excel the running time was 30mins - now running it
without the clipboard, in a second instance of excel the running time
is around 5mins ! ....not sure where the saving has been made, but
thanks again.

J