I have a VB app that creates an instance of Excel, and then intensively gets
Excel to copy and paste data between worksheets. The problem that I have is
that whilst this is going on (for about 5 minutes), if the user copies data
in another application, then that data is pasted into Excel instead of the
data that was intended.

How can Excel 'lock' the clipboard between the Copy and Paste commands so as
to ensure that this does not happen?

John Austin

John

I generally copy cells when I need more than just the value (e.g.
formatting, data validation). If I just need the value, I set the Value
property of the destination cell equal to the Value property of the source
cell. This avoids the clipboard and will probably be faster.

Five minutes of copying is quite a bit, unless you're running a 486 machine.
I would concentrate on getting that five minutes down to under 30 seconds.
If you describe what you're doing, maybe someone can help you do that.

I don't know of any way to lock the clipboard without locking out the whole
computer.


--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com


John Austin wrote:
I have a VB app that creates an instance of Excel, and then
intensively gets Excel to copy and paste data between worksheets. The
problem that I have is that whilst this is going on (for about 5
minutes), if the user copies data in another application, then that
data is pasted into Excel instead of the data that was intended.

How can Excel 'lock' the clipboard between the Copy and Paste
commands so as to ensure that this does not happen?

John Austin

Hello Dick,

The 4 minutes is actually mainly data insertion - it takes a long time
because the workbook is big. Up to 20 sheets each 250 rows by 90 colums. The
only things that are actually copied are formatting and formulae. If the
machine were locked for the duration of the copy / paste at least it would
avoid corruption of the formulae.

As an aside, I did try making the VB6 data insertion routine into an
active-x dll so that it could be called from within the Excel application
in-process. This resulted in startling performance improvements but I got
some weird errors that were impossible to debug.
--
John Austin


"Dick Kusleika" wrote:

John

I generally copy cells when I need more than just the value (e.g.
formatting, data validation). If I just need the value, I set the Value
property of the destination cell equal to the Value property of the source
cell. This avoids the clipboard and will probably be faster.

Five minutes of copying is quite a bit, unless you're running a 486 machine.
I would concentrate on getting that five minutes down to under 30 seconds.
If you describe what you're doing, maybe someone can help you do that.

I don't know of any way to lock the clipboard without locking out the whole
computer.


--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com


John Austin wrote:
I have a VB app that creates an instance of Excel, and then
intensively gets Excel to copy and paste data between worksheets. The
problem that I have is that whilst this is going on (for about 5
minutes), if the user copies data in another application, then that
data is pasted into Excel instead of the data that was intended.

How can Excel 'lock' the clipboard between the Copy and Paste
commands so as to ensure that this does not happen?

John Austin

John Austin wrote:

The 4 minutes is actually mainly data insertion - it takes a long time
because the workbook is big. Up to 20 sheets each 250 rows by 90
colums. The only things that are actually copied are formatting and
formulae. If the machine were locked for the duration of the copy /
paste at least it would avoid corruption of the formulae.

It still seems like a long time. I can populate that many cells in 88
seconds on my dinosaur of a computer from VBA in Excel. Maybe the
automation is creating the overhead.

I don't know VB6 well enough to know if there's a better solution, but you
could find some APIs that disable the keyboard and the mouse. That would
prevent the user from doing anything until your program is done.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Hi,

Excel seems to be able to copy and paste in an instance without using
the clipboard. how about something like this?

Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
Declare Function CloseClipboard Lib "user32" () As Long
Declare Function EmptyClipboard Lib "user32" () As Long


If OpenClipboard(0) = 0 Then
MsgBox "cannot open clipboard."
ElseIf EmptyClipboard() = 0 Then
MsgBox "cannot clear clipboard."
Else

'code to copy and paste

CloseClipboard
End If

--
HTH,

okaizawa


John Austin wrote:
I have a VB app that creates an instance of Excel, and then intensively gets
Excel to copy and paste data between worksheets. The problem that I have is
that whilst this is going on (for about 5 minutes), if the user copies data
in another application, then that data is pasted into Excel instead of the
data that was intended.

How can Excel 'lock' the clipboard between the Copy and Paste commands so as
to ensure that this does not happen?

John Austin

This looks very promising - thanks, I will try it out!
--
John Austin


"okaizawa" wrote:

Hi,

Excel seems to be able to copy and paste in an instance without using
the clipboard. how about something like this?

Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
Declare Function CloseClipboard Lib "user32" () As Long
Declare Function EmptyClipboard Lib "user32" () As Long


If OpenClipboard(0) = 0 Then
MsgBox "cannot open clipboard."
ElseIf EmptyClipboard() = 0 Then
MsgBox "cannot clear clipboard."
Else

'code to copy and paste

CloseClipboard
End If

--
HTH,

okaizawa


John Austin wrote:
I have a VB app that creates an instance of Excel, and then intensively gets
Excel to copy and paste data between worksheets. The problem that I have is
that whilst this is going on (for about 5 minutes), if the user copies data
in another application, then that data is pasted into Excel instead of the
data that was intended.

How can Excel 'lock' the clipboard between the Copy and Paste commands so as
to ensure that this does not happen?

John Austin