How to Clear Office Clipboard with VBA

Question

How would you clear the Microsoft Office Clipboard using VBA, specifically Word VBA?

I am copying a lot of data at time into the clipboard and don't want excessive data kept in the Clipboard.

Answer 1

Most of the answers here are clearing the windows clipboard. One is just removing Excel things copied.

However, I suspect what is/ was wanted here is a VBA coding to clear the Offices clipboard viewer. The way we manually do that is by clicking this button:

https://www.excelfox.com/forum/showthread.php/2824-Tests-Copying-pasting-Cliipboard-issues?p=18037#post18037

See my answer here:

https://stackoverflow.com/a/79137208/4031841

    Option Explicit
    #If VBA7 Then
     Declare PtrSafe Function AccessibleChildren Lib "oleacc" (ByVal paccContainer As Office.IAccessible, ByVal iChildStart As Long, ByVal cChildren As Long, ByRef rgvarChildren As Any, ByRef pcObtained As Long) As Long
    #Else
     Declare Function AccessibleChildren Lib "oleacc" (ByVal paccContainer As Office.IAccessible, ByVal iChildStart As Long, ByVal cChildren As Long, ByRef rgvarChildren As Any, ByRef pcObtained As Long) As Long
    #End If
Sub small_20202024_ClearOfficeClipBoard_()  ' https://www.excelfox.com/forum/showthread.php/2824-Tests-Copying-pasting-Cliipboard-issues?p=24879&viewfull=1#post24879    
Dim avAcc, bClipboard As Boolean, j As Long
Dim MyPain As String
    If CLng(Val(Application.Version)) <= 11 Then  '                   Case 11: "Excel 2003" Windows    "Excel 2004" mac
     Let MyPain = "Task Pane"
    Else
     Let MyPain = "Office Clipboard"
    End If
Set avAcc = Application.CommandBars(MyPain)   '
Let bClipboard = avAcc.Visible      '   bClipboard will be false if the viewer pain is not open
    If Not bClipboard Then
     avAcc.Visible = True           '   This opens the Viewer pain. The coding won't work if it is not open
     DoEvents: DoEvents
    Else
    End If
'   coding change for Office versions at  --  Office 2016  ==
    If CLng(Val(Application.Version)) < 16 Then
' --For Office versions 2003 2007 2010 2013 ----------------------------------------
        For j = 1 To 4         '      J =    1  2  3  4
         AccessibleChildren avAcc, Choose(j, 0, 3, 0, 3), 1, avAcc, 1
        Next
     avAcc.accDoDefaultAction 2&  '           This seems to do the clearing   It will NOT error if viewer pain is already  Cleared                  1& for paste
' ----------------------------------------------------------------------------------
    Else
' ==For Office versions 2016 and higher ==============================================
        For j = 1 To 7      '           J =  1  2  3  4  5  6  7
         AccessibleChildren avAcc, Choose(j, 0, 3, 0, 3, 0, 3, 1), 1, avAcc, 1
        Next
     avAcc.accDoDefaultAction 0& '            This seems to do the clearing   It WILL error if viewer pain is already  Cleared
    End If ' =======================================================================
 Let Application.CommandBars(MyPain).Visible = bClipboard      '   Puts the viewer pain back as it was, open or closed
End Sub

Alan

Answer 2

If any one is like me and just grabs code and bashes it with the programing hammer until it fits your needs: The above solution updated for a modern era with the ptrsafe and longptr already added. In case, much like myself, you have no idea what they mean and it would take far too long to figure out where to put them.

Option Explicit
Public Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As LongPtr
Public Declare PtrSafe Function EmptyClipboard Lib "user32" () As LongPtr
Public Declare PtrSafe Function CloseClipboard Lib "user32" () As LongPtr

Public Function ClearClipboard()
    OpenClipboard (0&)
    EmptyClipboard
    CloseClipboard
End Function

Sub ccc()
    Call ClearClipboard
End Sub

Answer 3

If you are getting the error while closing the workbook, Use code something like this

wbk.close False
Application.CutCopyMode = False 

Answer 4

A quick and easy solution I've just used is

MyDoc.Range.Characters(1).Copy

Where "MyDoc" is the name of the document you are using.

It doesn't technically empty the clipboard, it just makes it one character long (assuming your document starts with characters), which stops Word from popping up with annoying questions.

The purists may not like it, but heyho, it's dead easy :)

Answer 5

Here's a solution that worked for me. This is based on a post by by Zack Barresse on VBAexpress.com:

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

Public Sub ClearClipboard()
    OpenClipboard (0&)
    EmptyClipboard
    CloseClipboard
End Sub

After copying this function to your VBA project, use ClearClipboard to clear it.

Answer 6

Would a simple

Application.CutCopyMode = False

work for your situation, or is this option not viable?

Answer 7

Saw this on another post, and I have tested it with Word VBA.

'Clearing the Office Clipboard

    Dim oData   As New DataObject 'object to use the clipboard

    oData.SetText text:=Empty 'Clear
    oData.PutInClipboard 'take in the clipboard to empty it

Just copy and paste into your code where ever you need to clear the Clipboard.

Another thing I noticed is that when I .Quit a program, say Excel, it keeps asking me if I want to keep the data is the Clipboard. A work around is to clear the clipboard using the above stated code. See below:

'Clearing the Office Clipboard

    Dim oData   As New DataObject 'object to use the clipboard

    oData.SetText text:=Empty 'Clear
    oData.PutInClipboard 'take in the clipboard to empty it


'You can also just remove the Alert Messages from the Excel Program while    
'the code is running
'Remove alert Messages from the Excel Program when closing
ExcelProgram.DisplayAlerts = False   

'Quiting the Excel Application
ExcelProgram.Quit

I used the above example in a VBA code to import data from an Excel File. See here

Answer 8

This functionality is held within the library "Microsoft Forms 2.0 Object Library". To link to that library go to the VBA editor, then Tools, References and pick it out from the list if it's not already ticked.

You can do more funky stuff with a bunch of WinAPI calls, but I generally prefer avoiding those unless absolutely necessary.

Also, don't forget about the DisplayAlerts property, which will suppress dialog boxes - although I'm not sure if it would always produce the desired result.