Clearing the clipboard in Office 365

user_2923267 0
Viewed 2276

I am using Office 365 under Windows 10, 64 bit. I am trying to clear the clipboard. The macro recorder produces an empty sub.

The following attempts are mostly collected from How to Clear Office Clipboard with VBA :

Option Explicit
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 ClearClipboardA()
    Application.CutCopyMode = False
End Sub

Public Sub ClearClipBoardB()
    ' Source: http://www.vbaexpress.com/kb/getarticle.php?kb_id=462
    Dim oData As New DataObject
    oData.SetText Text:=Empty ' Clear
    oData.PutInClipboard ' Putting empty text into the clipboard to empty it
End Sub

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

Version A:

Method or data member not found

Version B: Runs without clearing the clipboard. A small yellow status window appears shortly:

"7 of 24 - Clipboard | Element not fetched"

(translated into English)

Version C: Nothing seems to happen.

In the above reference user iamstrained writes: “... if anyone is hunting for how to do this in Office 365 under 64-Bit, you now need to use the modifications for backwards compatibility to make this work: Private Declare PtrSafe and LongPtr as your two changes to these values will resolve issues and allow it to still work.”

I found a reference to a Microsoft page, where this has perhaps been done:

https://docs.microsoft.com/da-dk/office/vba/access/Concepts/Windows-API/send-information-to-the-clipboard

Using subs shown here I can insert text into the clipboard and extract from it, but not clear it.

office365 ms-word clipboard vba
4 Answers

to clear the office Clipboard (from Excel):

#If VBA7 Then
    Private 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
    Public Const myVBA7 As Long = 1
#Else
    Private 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
    Public Const myVBA7 As Long = 0
#End If

Public Sub EvRClearOfficeClipBoard()
    Dim cmnB, IsVis As Boolean, j As Long, Arr As Variant
    Arr = Array(4, 7, 2, 0)                      '4 and 2 for 32 bit, 7 and 0 for 64 bit
    Set cmnB = Application.CommandBars("Office Clipboard")
    IsVis = cmnB.Visible
    If Not IsVis Then
        cmnB.Visible = True
        DoEvents
    End If

    For j = 1 To Arr(0 + myVBA7)
        AccessibleChildren cmnB, Choose(j, 0, 3, 0, 3, 0, 3, 1), 1, cmnB, 1
    Next
        
    cmnB.accDoDefaultAction CLng(Arr(2 + myVBA7))

    Application.CommandBars("Office Clipboard").Visible = IsVis

End Sub
user_85378070

I can confirm that the code below clears the Windows clipboard

#If Win64 Then
Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As LongPtr) As Long
Declare PtrSafe Function CloseClipboard Lib "user32" () As Long
Declare PtrSafe Function EmptyClipboard Lib "user32" () As Long
#Else
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
#End If

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

You can download the pointer safe declarations from https://www.microsoft.com/en-us/download/confirmation.aspx?id=9970

user_52117520

I've used this above code snippet and it worked well until recent software updates that prevented me to clear the office clipboard without opening clipboard window. My solution it's very simple - add just this to the code:

#If VBA7 Then
    Private 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
    Public Const myVBA7 As Long = 1
#Else
    Private 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
    Public Const myVBA7 As Long = 0
#End If

Public Sub EvRClearOfficeClipBoard()
    Dim cmnB, IsVis As Boolean, j As Long, Arr As Variant
    Arr = Array(4, 7, 2, 0)                      '4 and 2 for 32 bit, 7 and 0 for 64 bit
    Set cmnB = Application.CommandBars("Office Clipboard")
   
'Just add here...First
'---------------------    
    With Application
       .DisplayClipboardWindow = True
    End With

    IsVis = cmnB.Visible 

    If Not IsVis Then
        cmnB.Visible = True
        DoEvents
    End If

    For j = 1 To Arr(0 + myVBA7)
        AccessibleChildren cmnB, Choose(j, 0, 3, 0, 3, 0, 3, 1), 1, cmnB, 1
    Next
        
    cmnB.accDoDefaultAction CLng(Arr(2 + myVBA7))

    Application.CommandBars("Office Clipboard").Visible = IsVis

'And finish with this
'--------------------
    With Application
       .DisplayClipboardWindow = False
    End With

End Sub
user_167446580

EvR's macro to clear the Office Clipboard is very clever. (See above.) It works in Excel with my 64-bit Microsoft (Office) 365 and Windows 10.

VBA7 was introduced with Office 2010; everyone should have it by now. The vba7 directive does not distinguish 64-bit; the win64 directive does that, if it was needed. So here is my version of EvR's macro:

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
Sub ClearOfficeClipboard()
    Dim A() As Variant, CB As Variant, n As Variant, i As Integer
    With Application
        If Not .DisplayClipboardWindow Then
            ClearClipboard
            .DisplayClipboardWindow = True
            ClearOfficeClipboard ' recurse
            Exit Sub
        End If
        On Error GoTo ErrHandler
        Set CB = .CommandBars("Office Clipboard")
        A = Array(0, 3, 0, 3, 0, 3, 1)
        For i = 0 To UBound(A)
            AccessibleChildren CB, A(i), 1, CB, n
        Next i
        CB.accDoDefaultAction CLng(0)
    End With
ErrHandler:: Set CB = Nothing
End Sub

The ClearClipboard macro is from Chip Pearson, http://www.cpearson.com/Excel/Clipboard.aspx. (See Timothy Rylatt above, but substitute vba7 in place of win64.)

I don't understand the For loop that calls AccessibleChildren. Can someone explain how it works? Note that CB must be Variant, not CommandBar or Object.

user_101724330
Related