Re: Routine Interfering with Clipboard

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

<whyt...@xxxxxxxxx> wrote in message

news:31c6bda2-59a8-4bcc-a39c-18fb981689f1@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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 T<whyt...@xxxxxxxxx> wrote in message

news:922dada5-dc56-4b69-a878-6ab52845d93d@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

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

Relevant Pages

  • Re: Routine Interfering with Clipboard
    ... Otherwise use the temporary array, eg open one wb, assign the values ... Set rDest = ActiveWorkbook.Worksheets.Range ... The macro therefore takes 25mins to run. ... instance of Excel and then open a second hidden instance to do all the ...
    (microsoft.public.excel.programming)
  • Array and Struct Constants in MASM
    ... compile-time array and structure constants. ... Syntax (used as a macro function): ... This macro creates a MASM struct declaration and also creates ...
    (alt.lang.asm)
  • Re: Help end the testing tedium please
    ... The new macro is crashing at: ... The filename and sheet name look fine. ... two arguments needed for "Consolidate". ... the file really were test1 thru test60, would the array statement need ...
    (microsoft.public.excel.programming)
  • Re: Is it possible to run a macro in tandem with, or right after,
    ... macro, or by having one macro call several others that each perform one piece ... 'sub procedure can access the rowHgts array from this ... Dim rowRng As Range ... Dim outputRow As Long ...
    (microsoft.public.excel.programming)
  • Speed versus memory
    ... My initial impulse was to use vector processes everywhere. ... an absurd amount of memory. ... Such a routine only has two input arrays ... and one output array but goes through ten intermediate arrays all of ...
    (comp.lang.fortran)