CODEWITHSUNDEEP
vbams-wordoffice365clipboard
Holger Nielsen
Holger Nielsen

Reputation: 63

Clearing the clipboard in Office 365

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://learn.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.

Upvotes: 5

Views: 7147

Answers (6)

J. Woolley
J. Woolley

Reputation: 88

Previous answers address clearing the legacy (single-item) clipboard and the expanded (multi-item) Office Clipboard. Here is what I use to clear the expanded (multi-item) Windows Clipboard History. See How to Clear Windows 10 Clipboard History.

Public Sub ClearClipboardHistory()
    Dim msg As String
    
    Const myName As String = "ClearClipboardHistory"
    Const NL As String = vbNewLine
    Const BL As String = NL & NL
    Const W As String = "wmic service where ""name like 'cbdhsvc[_]%'"" call "
    Const C As String = "/c " & W & "stopservice" & " & " & W & "startservice"

    msg = "This macro will present a User Account Control dialog" & NL _
        & "authorizing an administrative command line to clear" & NL _
        & "Windows Clipboard History." & BL _
        & "After clearing Windows Clipboard History, press" & NL _
        & "Win+V, Esc, Win+V to view its status."
    If MsgBox(msg, (vbOKCancel + vbInformation), myName) <> vbOK Then Exit Sub
    CreateObject("Shell.Application").ShellExecute "cmd.exe", C, , "runas", 0
    
End Sub

Upvotes: 0

Alan Elston
Alan Elston

Reputation: 99

Guten Tag

I am not too clear on the discussions here regarding things to do with different Office version, and the whole VBA7 and Bit stuff. There appears to be some different explanations in the comments.

But based on some recent reviewing of codings here and elsewhere on the subject, I am thinking that what we are doing here is Clearing an Office’s Clipboard Viewer. Specifically we are doing with VBA what we do manually when we click that (in English Office) Clear All Button.

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

(In my German Office versions the button is Alle löschen ). The codings discussed so far here, are, I think, doing some sort of GUI hierarchy navigating using some COM Wrapper Interface Thing which allows us to get at things which can be loosely described as Active Accessibles.

That is the limit of my Layman understanding of it.

The Active bit probably explains why the Viewer Pain needs to be open for the codings to work. The hierarchy of the accessible buttons may have changed at about the Office 2016 time, so possibly the main thing needed to consider regarding code changes for different Office versions is the Office version. (One other extra small thing to consider is the Literal string used in the early code section that checks that the Viewer Pain is open, because I think that changed from Office 2007 upwards)

I think this coding version below here may do the job from Office 2003 upwards. I have tested it thoroughly on a few computers with Office versions 2003 2007 2010 2013. I only have one higher version, an Office 2016, and it works on that. I know that a couple of other people found that it worked in their Office 365. Perhaps if anyone passing finds it works or not in their office, then they could drop a comment and let us know, and please give their Office version as well, Thanks.

A current discussion of this and other solutions is going on from about here

https://eileenslounge.com/viewtopic.php?p=321817#p321817

https://www.eileenslounge.com/viewtopic.php?p=321822#p321822

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://eileenslounge.com/viewtopic.php?p=319159&sid=a5636ddee2213f0629c9f46423c324c5#p319159
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

Upvotes: 0

J. Woolley
J. Woolley

Reputation: 88

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.

Upvotes: 0

Noetico
Noetico

Reputation: 11

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

Upvotes: 0

EvR
EvR

Reputation: 3498

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

Upvotes: 4

Timothy Rylatt
Timothy Rylatt

Reputation: 7860

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

Upvotes: 0

Related Questions