Clear the clipboard (Excel 2000)

[Lynden]

I use VBA code to copy a pivot table and then past it using the paste special 'values' option. Even although I use Application.CutCopyMode = False,
the clipboard is not cleared. Because there are 20 pivot tables that must be copied and pasted, the clipboard becomes overloaded.
Is there any other way to clear the Windows clipboard totally from within the macros?
Can anyone out there help me, please.

[pieterse]

What makes you think the clipboard becomes overloaded?

[Lynden]

I get an error that tells me 'You cannot paste any more items to the clipboard' or some such text. Also when I open the Clipboard Toolbar, it shows me all these items that have been pasted. I want them cleared!

[HansV]

Unfortunately, the Office clipboard is not exposed in the Excel or Office object model (still isn't in Excel 2002, don't know about 2003), so you can't manipulate it in VBA directly. I found something in the MS newsgroups that may work for Excel 2000; it doesn't work in Excel 2002:

Sub ClearClipboard()
On Error Resume Next
Application.CommandBars("Clipboard").Controls("Cle ar All").Execute
End Sub

This attempts to press the Clear All button on the Clipboard task pane. (But in Excel 2002, the buttons on this task pane don't count as command bar controls)

[Lynden]

Part of the problem is resolved - I don't get the error during the run time anymore. Thanks for this.
When I try to copy another piece of information to the clipboard, I get the warning message that the clipboard can only contain a max of 12 items and asks me if I want to continue and throw away the first item, with Yes and No buttons. It only appears once and then you can carry on copying and pasting.
So in fact the 'execute' command does not actually remove everything from the clipboard in 2000 either.

[unkamunka]

There is a registry setting that will disable the Office Clipboard altogether in both Office 2000 and Office XP. This is mentioned in both of Woody's books and is also available here. If you need to set this up on other machines as well, you will need to use the Scripting Runtime. HTH

[Lynden]

This could work if the file was only used on one machine, or where you can control who is to get and use the file.
Unfortunately I do not have control over who will use the file.
Pity, I was hoping there would be some way of manipulating the clipboard from within Excel or Word!
Thanks all.

[unkamunka]

Something that MIGHT work is using:

Application.DisplayAlerts = False 'turns off any warning

You will have to reset this to True at the end of your code. Otherwise, all warnings will remain turned off.

I have not tested this, and the Office Clipboard interface may not actually operate at the "Excel" level - making this irrelevant. HTH

[jscher2000]

I was going to suggest temporarily disabling the Clipboard CommandBar, but now I seem to have permanently disabled mine, so perhaps it's not such a good idea for wide deployment. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

<pre>?commandbars("clipboard").Enabled
False

commandbars("clipboard").Enabled=True

?commandbars("clipboard").Enabled
False</pre>

(Display puzzled expression here.)

[pieterse]

Strange. Works fine for me (Xl2000).

[jscher2000]

I was unable to revive it in either Excel 2002 or Word 2002. Seems I've killed it in a very serious way. Not that I'm complaining. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

[Lynden]

Sorry you have lost your clippy - although you don't seem too sad!!!!
I have run the macro with the clipboard code at the top and bottom, but the copy data of the pivot table seems to be so stubbon. It still appears on the clipboard after the macro has run!!
It is not really a train smash, although I am rather surprised that it does not work as it does on your computer.
I rest my case. Thanks for all the assistance.

[charlotte]

WinAPI to the rescue.

Declare Function EmptyClipboard Lib "user32" Alias "EmptyClipboard" () As Long

[Lynden]

I popped this function right at the top of the module and ...... IT WORKED.
Thank you a thousand times.
The clipboard is clear - no more error message - and I can copy somethings immediately after running the macro and it does not give me an error either.

[charlotte]

The functionality within Office for stuff like this is just a wrapper over the API calls. Sometimes we try too hard to avoid the API when that's really the *easy* way to do it. Not in every case, of course, but sometimes ... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>