Runtime Error Msg: "Cannot empty the Clipboard"

[Guest]

Hello,

In an attempt to find a way of breaking a chart reference to worksheet cells
in pre-Excel2003 by using the chart's Series formula/Series named array and
avoiding the "Unable to set the XValues property of the Series class" error
message when the formual/named array is very large and , i've tried copying
the chart to the clipboard, then pasting back using the following code:

aWorksheet.ChartObjects.Item(1).Chart.CopyPicture Appearance:=xlScreen,
Size:=xlScreen, Format:=xlBitmap
aWorkbook.Sheets(1).Paste

The code above works but problem is if i have a user that opens many
INSTANCES of EXCEL.EXE each running an excel template that contain the above
code and executes the above code simultaneously, i get the following runtime
error message: "Cannot empty the Clipboard"

Based on the following KB article from MS:
http://msdn.microsoft.com/library/default.asp?url=/library/en-
us/vbenlr98/html/vamsgclip2cantempty.asp

it appears one of the instances of excel is not releasing the clipboard. Is
this true (??).

Other than trapping the error and trying to copy/paste a 2nd time in the
trapping subroutine, is there another solution to overcoming this "Cannot
empty the Clipboard" error message, or is there another reason for this
errror message?

Thanks

 

[Robin Hammond]

BW,

I have been running into this for years. The multiple instances might be
having an effect, but are not a pre-requisite. I've seen it on most versions
of Excel (but particularly 97 and 2000 - it seems to be better in XP),
regardless of the machine config. It's worse than you think too, because
sometimes error handling doesn't work and VBA just stops on the call causing
the clipboard error.

I've tried api calls, all the tricks this group can suggest, and have never
found a foolproof solution. Without any proof other than API calls having no
effect, I've come to believe that Excel uses a private clipboard that is
nothing to do with the windows clipboard, and that this sometimes fails when
under load copying pictures.

If you switch into the VBE you can typically step through the failing call
and resume your routine, but you won't want most of your users doing that.

No help, I'm afraid, but it's not just you.

Robin Hammond
www.enhanceddatasystems.com

 

[NickHK]

BW,
What if you disable the Office clipboard, forcing Excel to use the Windows
clipboard instead.
http://techsupt.winbatch.com/TS/T000001071F4.html

NickHK

 

[NickHK]

Robin,
This is probably not applicable to this case but if you RUN "clipbrd", you
get a Windows app called Clipbook Viewer where you can see the contents of
clipboard and also save them to your own clip board for future use. You can
see the clipboard on remote computers also. Uses NetDDE. See:

http://www.microsoft.com/technet/archive/wfw/5_ch10.mspx

Not sure if you could program this for any use, but may be interesting.

NickHK

 

[Guest]

Thanks for you input gentlement.

It looks like when i use "aWorksheet.ChartObjects.Item(1).Chart.CopyPicture
" not only is the image copied to the Office Clipboard (i can see it there),
but also copied over to the Windows clipboard (i assume this is the windows
clipboard i am viewing when i run "clipbrd" from the Windows Start->Run box.

So i dont know which clipboard causing the "Can't empty clipboard" message.