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.
(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