VBA Clear clipboard

[Guest]

Hi

How can I clear the clibboard from VBA code? I have an application which
copies a lot of data, which I want to remove once it's been used.

Many thanks

 

[Leith Ross]

Hello Nick,

Add a VBA module to your project and add this code to it.

CALLING THE MACRO:
ClearClipboard


Code:
--------------------

Declare Function OpenClipboard _
Lib "User32.dll" _
(ByVal hWndNewOwner As Long) As Long

Declare Function EmptyClipboard _
Lib "User32.dll" () As Long

Declare Function CloseClipboard _
Lib "User32.dll" () As Long

Public Sub ClearClipboard()

Dim Ret

Ret = OpenClipboard(0&)
If Ret <> 0 Then Ret = EmptyClipboard
CloseClipboard

End Sub

 

[Guest]

NickH:

Application.CutCopyMode = False

 

[Guest]

Thanks for such prompt response, Leith

I have tried this, but it doesn't appear to work. If I step through the
module, Ret always gets a value of 1, even on consecutive runs. With the
clibboard visible, the contents remain unchanged.

I don't know if it's relevant, but I'm using Office 2003 and I'm looking at
the "Office Cliboard" which indicates "1 of 24" entries as I type. The
source data comes from an Outlook message, which I copy manually (<ctrl>-C).

 

[Leith Ross]

Hello Nick,

I'm glad you mentioned youu are trying to clear the Office Clipboard.
The API code I posted is for the Windows Clipboard.

Here is some information about the Office Clipboard...
In versions of Excel earlier than Excel 2000, you can copy and paste an
item anywhere within a worksheet until you perform an action that clears
the clipboard. The Office Clipboard retains information about pasted
items so that you can perform other tasks without clearing the
clipboard and then paste items as you need them. The Office Clipboard
does not replace the Windows Clipboard, which can only store a single
item.

The data stored in the Office Clipboard is stored in HTML format.

The contents of the Office Clipboard are deleted when the current
session ends. If you have one Office program running, the contents of
the Office Clipboard are deleted when you close that program. If you
have multiple Office programs running, the contents of the Office
Clipboard are deleted after you close the last Office program.

There is no Visual Basic for Applications object model for the Office
Clipboard, so there is no way to programmatically manipulate it. You
can easily clear all the clipboards that are stored in the Office
Clipboard. To clear the Office Clipboard, click Clear Clipboard on the
Clipboard toolbar.

Sincerely,
Leith Ross

 

[Guest]

Hi Leith,

Thank you for your comprehensive reply. You have taught me something new
about the clipboard, which is useful to know.

I'm not sure what this means in terms of my using the applications, but I
will pay attention to what happens based on the information you've supplied.

Kind regards,
Nick