Test Post for an EileenLounge post PART 4 (In older post)
Quote:
Originally Posted by rory post_id=246738 time=1550498216 user_id=83
IIRC for Office 2013 or later, you need to use CommandBars("Office Clipboard") rather than CommandBars("Task Pane")
Hi, Mr Rory.
Since this Thread in 2019, I have used various variations of codings discussed here, across Office (Windows) versions 2003 2007 2010 2013 and 2016, and also over the last day or two I have done a few more experiments and reviews in the light of a more recent similar Thread: https://eileenslounge.com/viewtopic.php?f=27&t=41223.
I am finding consistently that it appears that "Task Pane" is needed for codings to work in Office 2003, whereas so far in all cases the same codings need "Office Clipboard" for Offices 2007 2010 2013 and 2016. I strongly suspect that "Office Clipboard" is needed for newer Office versions also based on the codings that appear to work for others with the newer versions. Is it therefore a reasonable assumption that for 2003 and lower "Task Pane" is needed, whereas "Office Clipboard" for all above?
( Have you noticed that some much smaller codings have appeared in the meantime
https://stackoverflow.com/questions/...46949#64946949
* https://www.mrexcel.com/board/thread.../#post-5228633
( https://www.excelfox.com/forum/showt...ll=1#post17966
https://www.excelfox.com/forum/showt...ll=1#post24879 ) I am not sure where they originated? Possibly Jaafar Tribak * ?
I tried to figure out what these where doing and had not a clue but I remember it causing me to have a strange dream of being in the dark in solitary confinement and trying to play a form of Squash / whack-a-mole with a piece of dried excrement, and a more recent enlightenment suggest I may not have been so far off with my thinking. )
_.______________________________-
By the way, in all the codings kicking around, this literal name issue plays a fairly minor role. Its needed to make sure the Offices Clipboard Viewer Pane thing is open/ showing . As an alternative we can use Let Application.DisplayClipboardWindow = True. I am not sure what the relative merits are, although I would have a tendency to go with earlier more fundamental stuff. But I don’t know what is the earlier more fundamental of the two ways here. (I noticed in 2003 sometimes the "Task Pane" brought something else up, so I also did the Let Application.DisplayClipboardWindow = True to get over that problem. It does not seem to error if the Offices Clipboard Viewer Pane thing is already open, so maybe it does no harm to include it also as a belt and braces approach)
I suppose perhaps needing the thing open for the meat of the codings to work means the COM wrapper of accessiblies interface we are using is only for active accessiblies
_._______________________________________-
* @Yasser
Hello Yasser,
I think it could be a good and nice idea for you to do a follow up post over at mrexcel in your February 18, 2019 thread , to tell your Arab friend , Jaafar Tribak, about your more recent post here at eileenslounge .
Jaafar Tribak tried to get you a solution back then, but did not manage it. You seem to have that solution now, ( the one Hans found for you )
It is interesting that one of Jaafar Tribak's attempts for you, was close *
Perhaps Jaafar Tribak might have some interesting comments.
( I note that back in February 2019, Jaafar said he only had Office 2013 so for that reason could not help you further, but it appears now that he has Office 2016
_.________________________________
( One other very minor thing, a small oddity, while I am here, just for completeness. In the review I done of the two similar Threads over the past couple of days, I came across a strange thing. In this thread, (the one we are in now), there was a very monstrous big code offering , ( given from Yasser in a uploaded text file , and I reproduced it here with my usual modifications which usually allow _(i) the thing to work also in Office 2003, as well as _(ii) to work in German Office.)
I can only get that to work in the only English Office versions I have, ( Office 2007). I suspect that coding would also work in at least Office 2003, but my usual modification of changing a literal text in the coding of "Clear All" to "Alle löschen" does not seem to help for that particular monster very big coding. This modification gets all the more typical earlier big Jaafar Tribak codings to work in my German Office, at least for all the Versions that they work in in English Office. )
Alan
Test for a EileenLounge post 1 (Review of a similar older Thread, which failed to solve the problem)
Review of a similar older Thread, which failed to solve the problem.
Hi
I thought it might be of general subject interest and helpful for future reference to mention that we had this exact same question from Yasser in 2019 ,
(although it took us most of that Thread to figure out what he wanted, which was to get VBA to do something like when we manually click that Clear All Button .
In the meantime we got a bit more insight into what The Windows Clipboard is really all about: It seems we are not strictly playing with a clipboard here, since there is only one, and that thing often referred to as the Office Clipboard aint really part of the The Windows Clipboard : What we are doing here is messing with something that makes its own limited copies of things last set to go on The Windows Clipboard , (in many cases, especially in Excel, those things never get there, ( there in the real clipboard that is) until a Paste. )
We are messing with something that could also be described as a specific viewer of the phenomena that is The Windows Clipboard
Also if you have multiple versions of Office open, they will all make their own independent copy of what is sent or set to be sent to the windows clipboard, but all our coding attempts so far will just clear stuff in the version its running in. That last bit is perhaps an important point that might be worth saying again a bit differently: Say I have Excel open from Office 2003, and I have word open from Office 2007. I then go somewhere, (anywhere, does not have to be anywhere in particular other than in Windows ), and I copy a word, Alan. That now appears in two list, one in each of the open Office versions: https://i.postimg.cc/nrhXLb56/A-Clip...ce-version.jpg
But if I use just one of the two Clear All Buttons, then it only is cleared from that list. So that is telling us again that each of the two things is monitoring and making its own copy of what is going or set to be going to The Windows Clipboard
(Some quirky contradiction like things do exist however. For example after using either Clear All button in one of the Offices, you will then no longer be able to do a simple windows Paste operation, even if things were copied from , and are still showing in the list in the other Office. So somehow either button does have the side effect of clearing the windows clipboard)
So there is no single Office Clipboard in the way that there is a single windows clipboard.
I am not trying to be annoyingly pedantic, - I just think it helps to understand things a bit better, which might lead to better and more interesting and clear solutions . Maybe calling it an Office’s Clipboard monitor might be better.
A confusing quirk is that messing with it might effect the thing its monitoring. Maybe a bit like a old analogue pressure meter measuring some very high pressure large gas tank, and because of some badly designed leaking spaghetti dependency chains, if you mess with the monitor you might cause the tank to explode!
Anyways, manipulating any Office’s Clipboard monitor thing in any direct way with VBA I have yet to see done.
Smarter people have told me that third party software that tries to claim to be some clipboard monitor or other are a bit iffy.
I think throughout we are concentrating here on just getting that button clicked, at some higher accessible interface level.
_._________
Back in 2019 we had a few of the mysterious API codings offered, big ones, most of which I think seemed to come from an Arab guy, who is an Author of a lot of these API thingies, Jaafar Tribak. I fiddled around, admittedly blindly, and got a version to work across Office versions 2003 2007 2010, (which was all I had at the time). It’s still was mostly Jaafar Tribak’s big coding and I was/am no the wiser how it works.
In the light of this Thread, and also just re reading all the previous stuff, I did some minor changes/ updates, and this is probably "my" latest attempt – its still mostly from the mysterious big versions from Jaafar Tribak, which seemed to have been the standard used for many years. )
We hit a brick wall back then, in 2019, as we could not get it working in Office 2016. So Yasser went off to mrexcel.com and got some interesting info from Jaafar Tribak , who tried but failed still to get it working in Office 2016. (Back then he only had Office versions 2007 2010 and 2013)
(That mrexcel thread has got a bit messed up by a forum software update, so I tried to make a repaired summarized copy of it pulling out the important bits here )
Back in 2019, the persistent error ( In Office 2016 ) with "my" coding and a few of the bigger codings offered by Jaafar Tribak at mrexcel was "Object doesn't support this property or method 'Error 438' " at the line
Call oIA.accDoDefaultAction(vKid).
For all previous Office version, that code line did not error, and that is the code line in the previous big codings that does the clearing, that is to say does the same as manually clicking that Clear All button in the Office’s Clipboard viewer, ( which incidentally must be open for any of the codings so far to work. I noticed on my recent review that it does not always get opened as it should by the previous codings, and I added a small mod that seems to overcome that problem)
The only reason for this Office 2016 problem at the time that Jaafar Tribak could think of was that the hierarchy of the accessible buttons in the office clipboard had changed for Office 2016. My thinking was that something quirky was going on as we weren’t getting at any kid from the relevant interface out of the COM wrapper that should have that interface of the active accessibles…… :innocent:
_.____________
In the meantime I have got some 2013 Office versions and one 2016 versions, so I thought I would
_ a) take a re look at the previous Threads
and
_b) a look at this recent one( the one I am writing in now) …..
….. maybe in the next post……
Test Post for an EileenLounge post PART 2
Continued from last post…..
_a) I confirmed all the previous findings, which was that the codings from 2019 were OK for Office up to and including 2013 and then the mentioned problem was also what I got in my Office 2016
https://www.excelfox.com/forum/showt...ge11#post17966
https://www.excelfox.com/forum/showt...ge11#post17968
https://www.excelfox.com/forum/showt...ge11#post17969
_._________________________________
Now _b ), some comments and interesting findings as a result of this recent Thread, the one I am in now
So back in early 2019 we were all stuck, even that Arab guru, Jaafar Tribak
Now, this is the interesting thing, consider from back then in 2019, one of Jaafar Tribak’s / ("my" version of his) failed ( failed in Office 2016 ) attempts, ( and I will call this and similar looking ones from now on the more recent appearing small coding to distinguish it from the big ones that we had all back then been looking at and which had become the standard one used by many people for a long time)
( Note also, that back then, we all seem to have all missed the fact that this small one does actually work in Offices 2003, 2007, 2010, 2013. So we had an alternative coding to the big ones most people had been using, but we all missed that, because at the time, we were concentrating on getting something to work in Office 2016 )
This is that smaller coding that we missed, and it would have been / (would be still for office 2013 and lower) an alternative to the previously widely used bigger type of codings. (The version here has a few simple modifications from me, just to help in the following comparisons and discussions)
Code:
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_2019_ClearOfficeClipBoard() ' Slightly modified attempt of Jaafar Tribak from 2019 to do the Clear All button https://www.mrexcel.com/board/threads/reset-clear-clipboard.1087948/#post-5228633 https://www.excelfox.com/forum/showthread.php/2824-Tests-Copying-pasting-Cliipboard-issues/page11#post17966
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"
Let MyPain = "Task Pane"
Else
Let MyPain = "Office Clipboard"
End If
Set avAcc = Application.CommandBars(MyPain) '
Let bClipboard = avAcc.Visible
If Not bClipboard Then
Let avAcc.Visible = True
DoEvents
End If
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 1& for paste
Let Application.CommandBars(MyPain).Visible = bClipboard '
End Sub
You can see straight away that it has strong similarity to what Hans found at stack overflow.
Having read through that stack overflow Thread a few times, I am not totally sure if the people providing the seemingly working answers totally understood what was going on as there seems to be some inconstancy and confusion in their explanations to things such as VBA7 , Win32, Win64 issues.
I certainly don’t claim to know better, but consider these two things:
Mike’s enlightenment ,
and
what Jaafar Tribak said back in 2019 when he couldn’t get that small 2019 code ( like the one I gave above) to work in Office 2016 ….. I guess the reason for the code not working in office 2016 is that the hierarchy of the accessible buttons in the office clipboard has changed. ….
I am going to take a Layman guess for now that we can forget 32Bit / 64Bit issues, and that this attempt from me could be a new small coding version from me that will do the job from Office 2003 upwards.
I would welcome
_1) any comment s, generally
as well as
_2) if anyone passing could try that coding and tell me if it worked or not and at the same time tell me their version info.
( If it's any help here, the macro http://<span style="font-family: Cou...()</span> here, will give some info of your versions in the Immediate window, (although note:
___ Application.OperatingSystem can give quirky answers in windows 11 , so the operating system result may be wrong for if you have Windows 11
___ I don’t know if that macro gets it correct in Office versions 2016, 2019,2021, 2024 or 365, since I don’t have them versions to check. My guess is that it might be a bit iffy for 2016 2019,2024 or 365. )
_3) Based on what we have seen and learnt with these more recent small codings, I wonder if anyone has any ideas on how to get the previous big codings to work on Office 2016 upwards. I ask this because the big codings seem to be doing things a bit differently, and it could be useful to have the different coding available as an alternative, for example to try if for some reason the small ones one day did not work
