• Privacywetgeving
    Het is bij Helpmij.nl niet toegestaan om persoonsgegevens in een voorbeeld te plaatsen. Alle voorbeelden die persoonsgegevens bevatten zullen zonder opgaaf van reden verwijderd worden. In de vraag zal specifiek vermeld moeten worden dat het om fictieve namen gaat.

Clear the Office Clipboard ! "E v R" excellent code.

Status
Niet open voor verdere reacties.

RAFAAJ200

Gebruiker
Lid geworden
15 mrt 2018
Berichten
11
Hi everybody,

Sorry, I do not speak dutch but I used Google Translation to understand the thread containg the following post by the user member : E v R

Code:
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

 Sub ClearOfficeClipBoard()
 Dim Acc As Office.IAccessible
   
   With Application
        .CommandBars("Office Clipboard").Visible = True
   DoEvents
        Set Acc = .CommandBars("Office Clipboard").accChild(1)
        Set Acc = zetAcc(Acc, 3)
        Set Acc = zetAcc(Acc, 0)
        Set Acc = zetAcc(Acc, 3)
        Acc.accDoDefaultAction 2&
        .CommandBars("Office Clipboard").Visible = False
   End With
   
 End Sub
 Private Function zetAcc(myAcc As Office.IAccessible, myChildIndex As Long) As Office.IAccessible
 Dim ReturnAcc As Office.IAccessible
 Dim Count As Long, List() As Variant
 
    Count = myAcc.accChildCount
    ReDim List(Count - 1&)
    If AccessibleChildren(myAcc, 0&, ByVal Count, List(0), Count) = 0& Then Set zetAcc = List(myChildIndex)
 
 End Function

This is the post I am enquiring about.. I couldn't add this question there because the thread is now locked

http://www.helpmij.nl/forum/showthread.php/893233-Klembord-overvol-melding-bij-afsluiten/page4

As shwon in the picture below taken with inspect.exe , the highlighted "effacer tout" button which is the french for Clear All button is not the 3rd child of the clipboard parent .. I am confused with EvR's code.

So my question is : Where are the child indexes 1,3,0,3 located in the screenshot picture below ? How did he find the correct child indexes ? The child indexes seem to contradict their hyerarchical locations in the picture below !


 
Laatst bewerkt:
Why in English ;)?
 
Parce qu'il parle seulement le Francais
 
The code of EVR uses indexes of the commandbar, which are called with Office.IAccessible
Office.IAccessible connects directly to the commandbar from excel2007 and newer.
The code of me (alphamax,message #84) in the same thread uses the indexes of the windows which should resemble your inspect.exe indexes.

p.s. i used windows spy++ instead of inspect.exe

@all
Sometimes code is so unique on the internet that it can only be found in one place.
So, if someone want's to know more, it's possible they express themselfs in a language which is not the mother-language of that forum.
Please have some understanding, it's all about sharing knowledge.

Soms is code zo uniek dat deze alleen op een plaats op het internet is te vinden.
Dus als iemand meer wil weten, dan kan het zijn dat deze zich uitdrukt in een taal die niet overeenkomt met de voertaal van het betreffende forum.
Graag begrip hiervoor, het gaat tenslotte over het delen van kennis.
 
Laatst bewerkt:
RAFAAJ200 are you jaafar tribak (who knows a lot of api's in excel)?
 
RAFAAJ200 are you jaafar tribak (who knows a lot of api's in excel)?

Yes that's me - You guessed right :) but the Windows API is not the propper solution for this because the office clipboard buttons don't have an HWND .

I did manage to clear the clipboard using API calls but it is not an elegant solution.
 
My solution in message#84 uses windows api's and hwnd's for the button.
I got the hwnd of the buttons by building a hwnd-tree.
It's not elegant but ugly either.
What non-elegant solution did you have.
 
Laatst bewerkt:
My solution in message#84 uses windows api's and hwnd's for the button.
I got the hwnd of the buttons by building a hwnd-tree.
It's not elegant but ugly either.
What non-elegant solution did you have.

Hi alphamax,

Yes - Your code works fine just as did mine ... My API code used more or less the same technique ...Your code as well as mine both basically retrieve the hwnd of the "bosa_sdm_XL9" window, adjust the clipboard pane screen position and then post a mouse button click over the screen area where the "Clear All" button is located.

What I meant by not having hwnds is that "bosa_sdm_XL9" doesn't correspond to the actual "Clear All" button .. It corresponds to its container window... The "Clear All" or "Paste All" buttons do not have a hwnd or Dialog ID .

Using Mcrosoft Active Accessibily or UI Automation for this ,is in my opinion, a cleaner and more correct approach than using the classic WinAPI PLUS, to be honest, the real reason I want to understand E v R's code is because I want to learn from it so I can apply similar technique to other projects.

Anyway, thanks alphamax for your interest and I hope Mr E v R sees this thread and responds to it as I couldn't Private Message him because this forum doesn't seem to allow new users to PM other members.
 
Laatst bewerkt:
Hi Jaafar,

Thanks! And thank you for a lot of nice code out there on the www :thumb:

The code you've posted (post #79) is the latest version without recursive looping through the IAccesible-children which wasn't needed because these children are 'always' on the same 'location'

These locations I found by watching my Locals-window together with F8 :D
The thread is allmost 2 years old, so I have to look into it as well

If you like you can contact me at my gmail account which starts with
x16101970
 
I tried to reduce the code somewhat:

Code:
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

Sub ClearOfficeClipBoard()
   redim sn(3)

   With Application.CommandBars("Office Clipboard")
        .Visible = True
        DoEvents
        Set Acc = .accChild(1)
 
        For j = 1 To 3
            If AccessibleChildren(Acc, 0, ByVal Acc.accChildCount, sn(0), Acc.accChildCount) = 0 Then Set Acc = sn(Choose(j, 3, 0, 3))
        Next
        
        Acc.accDoDefaultAction 2&
        .Visible = False
   End With
   Set Acc = Nothing
End Sub
 
Laatst bewerkt:
@snb, ben vaak voorstander van versimpeling/reducering van code, nu ook, hoewel mijn Excel crasht wanneer de code gedraaid wordt met een leeg clipboard. (kan uiteraard aan mijn systeem liggen, maar crasht niet met de wat langere code)
 
Als ik het test met een leeg klembord blijkt de DoEvents na Visible doorslaggevend.
Die heb ik nu toegevoegd aan de code in #12. + opschoonregel.
 
Laatst bewerkt:
Without loop, only retrieving the children needed, look for the indexes for a familiar pattern.
Code:
Option Explicit

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 Sub ClearOfficeClipBoard2()    'am_2018
    Dim avAcc(0) As Variant
    Dim bClipboard As Boolean
    Dim lCount As Long
    With Application.CommandBars("Office Clipboard")
        bClipboard = .Visible
        If Not bClipboard Then
            .Visible = True
            DoEvents
        End If
        AccessibleChildren .accChild(1), 3, 1, avAcc(0), lCount
        AccessibleChildren avAcc(0), 0, 1, avAcc(0), lCount
        AccessibleChildren avAcc(0), 3, 1, avAcc(0), lCount
        avAcc(0).accDoDefaultAction 2&
        .Visible = bClipboard
    End With
End Sub

p.s. interesting blog https://blogs.msdn.microsoft.com/gu...e-office-task-pane-with-active-accessibility/ and thread http://www.eileenslounge.com/viewtopic.php?f=26&t=15415
 
Laatst bewerkt:
Crasht nog steeds bij mij, maar als ik redim sn(3) wijzig naar redim sn(6) (=grootste "list" in dit 'pad') dan gaat het wel goed
 
Hi Jaafar,

Thanks! And thank you for a lot of nice code out there on the www :thumb:

The code you've posted (post #79) is the latest version without recursive looping through the IAccesible-children which wasn't needed because these children are 'always' on the same 'location'

These locations I found by watching my Locals-window together with F8 :D
The thread is allmost 2 years old, so I have to look into it as well

If you like you can contact me at my gmail account which starts with
x16101970

Thanks E v R for responding.

Yes, stepping through code (F8) does indeed reveal the actual locations but what I find unexplicable is that those location numbers ie : 3-0-3 do not correspond to the locations I see in the tree produced by Inspect.exe unless I am missing something.

Anyway, I'll keep exploring the inspect spy tool and see if I can get to the bottom of this. (BTW, I am new to inspect spy tool and I think it is an excellent helping tool)

Thanks again for your excellent idea to simplify the classic long iterating code that is used for clearing the office clipboard.
 
Without loop, only retrieving the children needed, look for the indexes for a familiar pattern.
Code:
Option Explicit

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 Sub ClearOfficeClipBoard2()    'am_2018
    Dim avAcc(0) As Variant
    Dim bClipboard As Boolean
    Dim lCount As Long
    With Application.CommandBars("Office Clipboard")
        bClipboard = .Visible
        If Not bClipboard Then
            .Visible = True
            DoEvents
        End If
        AccessibleChildren .accChild(1), 3, 1, avAcc(0), lCount
        AccessibleChildren avAcc(0), 0, 1, avAcc(0), lCount
        AccessibleChildren avAcc(0), 3, 1, avAcc(0), lCount
        avAcc(0).accDoDefaultAction 2&
        .Visible = bClipboard
    End With
End Sub

p.s. interesting blog https://blogs.msdn.microsoft.com/gu...e-office-task-pane-with-active-accessibility/ and thread http://www.eileenslounge.com/viewtopic.php?f=26&t=15415

Nice addition alphamax !
 
@Alpha,

Die lcount vind ik wel spannend....


Code:
Public Sub ClearOfficeClipBoard2()    'am_2018
    Dim avAcc(0)
    
    With Application.CommandBars("Office Clipboard")
        .Visible = -1
        Do
           DoEvents
        Loop Until .Visible
        
        AccessibleChildren .accChild(1), 3, 1, avAcc(0), 0
        AccessibleChildren avAcc(0), 0, 1, avAcc(0), 0
        AccessibleChildren avAcc(0), 3, 1, avAcc(0), 0
        avAcc(0).accDoDefaultAction 2&
        .Visible = 0
    End With
End Sub
 
Was ook al weer wat verder gegaan, combi van bovenstaande codes:

Code:
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 Sub ClearOfficeClipBoard3()
    Dim avAcc, bClipboard As Boolean, j As Long
    
    With Application.CommandBars("Office Clipboard")
        bClipboard = .Visible
        If Not bClipboard Then
            .Visible = True
            DoEvents
        End If
      Set avAcc = .accChild(1)
          For j = 1 To 3
            AccessibleChildren avAcc, Choose(j, 3, 0, 3), 1, avAcc, 1
        Next
        
        avAcc.accDoDefaultAction 2& '1& for paste
        .Visible = bClipboard
    End With
End Sub
 
Status
Niet open voor verdere reacties.
Terug
Bovenaan Onderaan