Clear clipboard in excel vba
-
- PlatinumLounger
- Posts: 4967
- Joined: 31 Aug 2016, 09:02
Clear clipboard in excel vba
Hello everyone
I have tried different solutions to clear the clipboard but I need to clear all that exists in clipboard section I need VBA code to do that task
I have tried different solutions to clear the clipboard but I need to clear all that exists in clipboard section I need VBA code to do that task
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 79526
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Clear clipboard in excel vba
Do you really need to clear the entire Office Clipboard?
Application.CutCopyMode = False
will clear Excel's clipboard, but not the multi-item Office Clipboard.
Application.CutCopyMode = False
will clear Excel's clipboard, but not the multi-item Office Clipboard.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4967
- Joined: 31 Aug 2016, 09:02
Re: Clear clipboard in excel vba
Yes I need to clear the entire Office Clipboard
-
- Administrator
- Posts: 79526
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Clear clipboard in excel vba
I tried several methods mentioned in a Google search (using the MSForms DataObject and involving Windows APIs). The only one that worked for me in Microsoft 365 is the following (source: Clearing the clipboard in Office 365):
Don't ask me to explain the code!
Code: Select all
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
Sub EvRClearOfficeClipBoard()
Dim cmnB, IsVis As Boolean, j As Long
Set cmnB = Application.CommandBars("Office Clipboard")
With Application
.DisplayClipboardWindow = True
End With
IsVis = cmnB.Visible
If Not IsVis Then
cmnB.Visible = True
DoEvents
End If
For j = 1 To 7
AccessibleChildren cmnB, Choose(j, 0, 3, 0, 3, 0, 3, 1), 1, cmnB, 1
Next
cmnB.accDoDefaultAction CLng(0)
Application.CommandBars("Office Clipboard").Visible = IsVis
With Application
.DisplayClipboardWindow = False
End With
End Sub
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4967
- Joined: 31 Aug 2016, 09:02
Re: Clear clipboard in excel vba
Amazing. It worked for me too. I already tried several solutions too but nothing worked. This only worked for me
Thank you very much
Thank you very much
-
- 5StarLounger
- Posts: 1154
- Joined: 21 Jan 2011, 16:51
- Location: Florida
Re: Clear clipboard in excel vba
YIKES!HansV wrote: ↑20 Jul 2024, 16:09I tried several methods mentioned in a Google search (using the MSForms DataObject and involving Windows APIs). The only one that worked for me in Microsoft 365 is the following (source: Clearing the clipboard in Office 365):
...
Don't ask me to explain the code!
PJ in (usually sunny) FL
-
- Administrator
- Posts: 79526
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 5StarLounger
- Posts: 631
- Joined: 27 Jun 2021, 10:46
Re: Clear clipboard in excel vba
The way it works is by using the fact that a CommandBar implements the Accessibility interface (iAccessible), and we can use that to navigate the GUI hierarchy; a bit like tabbing around a userform
The code navigates it's way down through the hierarchy starting at the root msoCommandBar (and down through to the msoWorkPane, I believe) to the 'Clear All' control And then presses that button (note that the code as written will error if there is nothing in the Office Clipboard)
Given the code uses magic numbers, the question is how did the programmer know what numbers to use? I'd guess either dogged experimentation or, more likely, using a tool like Microsoft's Accessibility Insights for Windows to walk the tree.
The code navigates it's way down through the hierarchy starting at the root msoCommandBar (and down through to the msoWorkPane, I believe) to the 'Clear All' control And then presses that button (note that the code as written will error if there is nothing in the Office Clipboard)
Given the code uses magic numbers, the question is how did the programmer know what numbers to use? I'd guess either dogged experimentation or, more likely, using a tool like Microsoft's Accessibility Insights for Windows to walk the tree.
-
- Administrator
- Posts: 79526
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 5StarLounger
- Posts: 665
- Joined: 18 Jan 2022, 15:59
- Location: Re-routing rivers, in Hof, Beautiful Bavaria
Review of a similar older Thread, which failed to solve the problem (Part 1 of 3)
Hi
I thought it might be of general subject interest and helpful for future reference and orientation to mention that we had this exact same question from Yasser in February, 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-Clipboa ... ersion.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……
_.____________
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……
I thought it might be of general subject interest and helpful for future reference and orientation to mention that we had this exact same question from Yasser in February, 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-Clipboa ... ersion.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……
_.____________
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……
Last edited by DocAElstein on 01 Nov 2024, 07:39, edited 3 times in total.
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(
-
- 5StarLounger
- Posts: 665
- Joined: 18 Jan 2022, 15:59
- Location: Re-routing rivers, in Hof, Beautiful Bavaria
Offices Clipboard Viewer Clear All Button VBA (Pasrt 2 of 3)
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/showthre ... #post17966
https://www.excelfox.com/forum/showthre ... #post17968
https://www.excelfox.com/forum/showthre ... #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)
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 (SpeakEasy'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. ….
_.. then based on that, I think I can make an initial attempt on a small coding version that may work from Office 2003 up to the newest Office versions….
….. in the next 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/showthre ... #post17966
https://www.excelfox.com/forum/showthre ... #post17968
https://www.excelfox.com/forum/showthre ... #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: Select all
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
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 (SpeakEasy'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. ….
_.. then based on that, I think I can make an initial attempt on a small coding version that may work from Office 2003 up to the newest Office versions….
….. in the next post
Last edited by DocAElstein on 29 Oct 2024, 07:31, edited 2 times in total.
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(
-
- 5StarLounger
- Posts: 665
- Joined: 18 Jan 2022, 15:59
- Location: Re-routing rivers, in Hof, Beautiful Bavaria
Offices Clipboard Viewer Clear All Button VBA ( Part 3 0f 3)
….. continued from the last two posts….. ( note: I messed up my earlier posts above, and so have deleted and re posted )
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 that will do the job from Office 2003 upwards. (It’s in the uploaded File also - Sub small_20202024_ClearOfficeClipBoard_() )
I tested it so far on quite a few computers with operating systems XP, Vista Windows 7 8.1 10 and 11 and Office versions 2003 2007 2010 2013. Unfortunately I only have one higher version Office, 2016. It works on that.
The important difference between mine and what Hans found is just this bitSo I have just a slightly changed section for Office 2013 and lower.
I would welcome
_1) any comments, 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.,( OS and Office). That would be particularly useful for newer versions, also 64 Bit versions if anyone has them.
_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 coding types 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
Some minor observations:
(_(i) This is perhaps obvious from the discussions so far, but just to make sure and clear: The coding Hans found does not work in Offices 2013 and lower : In the loop at J = 6 I get the run time error '424' Object needed )
_(ii)
_ (iii) I don’t see any obvious difference in how a typical Offices Clipboard viewer pain looks like across the Office versions 2003 2007 2010 2013 2016 that might explain why the coding needs to be different from Office 2016: They all look a bit different to my layman eye?
Alan
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 that will do the job from Office 2003 upwards. (It’s in the uploaded File also - Sub small_20202024_ClearOfficeClipBoard_() )
I tested it so far on quite a few computers with operating systems XP, Vista Windows 7 8.1 10 and 11 and Office versions 2003 2007 2010 2013. Unfortunately I only have one higher version Office, 2016. It works on that.
The important difference between mine and what Hans found is just this bit
Code: Select all
' 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 ' =======================================================================
I would welcome
_1) any comments, 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.,( OS and Office). That would be particularly useful for newer versions, also 64 Bit versions if anyone has them.
_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 coding types 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
Some minor observations:
(_(i) This is perhaps obvious from the discussions so far, but just to make sure and clear: The coding Hans found does not work in Offices 2013 and lower : In the loop at J = 6 I get the run time error '424' Object needed )
_(ii)
I can confirm that also for my coding on my Office 2016. However my coding does not error for Office versions 2013 and lower. Maybe that tells someone something, (of course for Office 2016 and higher my coding and the one Hans found are pretty well doing the same, but for Office 2013 and lower mine does it a bit differently, as seen in the snippet above)
_ (iii) I don’t see any obvious difference in how a typical Offices Clipboard viewer pain looks like across the Office versions 2003 2007 2010 2013 2016 that might explain why the coding needs to be different from Office 2016: They all look a bit different to my layman eye?
Alan
You do not have the required permissions to view the files attached to this post.
Last edited by DocAElstein on 29 Oct 2024, 14:57, edited 1 time in total.
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(
-
- NewLounger
- Posts: 12
- Joined: 06 Jun 2024, 12:36
Re: Clear clipboard in excel vba
Hi Doc. Testing Office 21 with 64 bit install, the following errors...
Big coding...
Little code...
Both versions pull up the clipboard dialog box with the ClearAll button before erroring. I'll trial on Office 19 with 32 bit install and report the outcome. HTH. Dave
Big coding...
Code: Select all
'*****object doesn't support this property or method
Call oIA.accDoDefaultAction(vKid) ' This does the clearing
Code: Select all
'**********object required error
AccessibleChildren avAcc, Choose(j, 0, 3, 0, 3, 0, 3, 1), 1, avAcc, 1
-
- 5StarLounger
- Posts: 665
- Joined: 18 Jan 2022, 15:59
- Location: Re-routing rivers, in Hof, Beautiful Bavaria
Re: Clear clipboard in excel vba
Thx Dave, that’s interesting and helpful
I was expecting the Big one not to work for Office 2016 and any higher Office versions.
I was not sure about Office 2021 and/ or the 64 Bit issue for the small one
Alan
I was expecting the Big one not to work for Office 2016 and any higher Office versions.
I was not sure about Office 2021 and/ or the 64 Bit issue for the small one
Alan
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(
-
- NewLounger
- Posts: 12
- Joined: 06 Jun 2024, 12:36
Re: Clear clipboard in excel vba
Hi Alan. I was mistaken. My Office 19 version is a 64 bit install but it did yield different results. The small code works BUT if there is nothing in the clipboard it becomes a recursive msgbox loop that requires the task manager to fix. The Big code did not work and errored on the same line as previously indicated. I forgot to mention in the previous post, to test the big code, you need to change the following line...
Also, I found it interesting that the clipboard pane display, along with the items copied, travelled with the file across pc's via my USB and were available for pasting when I opened the file on a different pc. I have earlier versions of XL going back to 03 and Windows versions going back to Vista but it seems you have the earlier editions covered. Anyways. HTH. Dave
Code: Select all
'*****************
'Application.OnTime Now + TimeValue("00:00:01"), "ClearOffPainBouton": Exit Sub
Application.OnTime Now + TimeValue("00:00:01"), "big_ClearOffPainBouton": Exit Sub
-
- 5StarLounger
- Posts: 665
- Joined: 18 Jan 2022, 15:59
- Location: Re-routing rivers, in Hof, Beautiful Bavaria
Re: Clear clipboard in excel vba
Thx for the correction. ( I forgot to do that change when I changed the macro name. I have corrected the file uploaded on the post . (On testing I probably missed that mistake as the Viewer Pain was probably open, meaning that the Application.OnTime … line was not done ) )
Thx for the other info.
I have the earlier versions from 2013 downwards well covered and have one 2016 version. But, they are all 34 Bit, so if anyone passing has an earlier 64 Bit Office it would be useful to know if the codings work or not.
I have not noticed yet anything copied travelling with the file on a USB, and I just took another quick check and I did not experience it. But I will keep my eye on that, - things Clipboardy can be a bit inconsistant.
Thx for the other info.
I have the earlier versions from 2013 downwards well covered and have one 2016 version. But, they are all 34 Bit, so if anyone passing has an earlier 64 Bit Office it would be useful to know if the codings work or not.
I have not noticed yet anything copied travelling with the file on a USB, and I just took another quick check and I did not experience it. But I will keep my eye on that, - things Clipboardy can be a bit inconsistant.
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(
-
- 5StarLounger
- Posts: 631
- Joined: 27 Jun 2021, 10:46
Re: Clear clipboard in excel vba
>Jaafar Tribak’s big coding and I was/am no the wiser how it works
It works by ensuring that the 'Clear All' button is displayed on the screen, then searching through the windows hierarchy find the window that represents the panel that contains the button, then obtains the Accessibilty COM interface for that button (by looking for what control with an Accessibility interface is at a specific point, first ensuring that nothing unexpected is covering that point), and then uses that Accessibility interface to invoke the (default) action the button(hopefully the 'Clear All' button). <phew>
It's just a different route to the same thing - the 'small' code walks through the relevant part of the application's accessibility hierarchy instead of having to search for relevant windows, and ends up at the same point - an Accessibility COM interface to the 'Clear All' button (well, depending on the version of Office ...)
>it could be useful to have the different coding available as an alternative
Not sure about that. It is even more of a hack than the 'small' version. And frankly they are indeed both hacks. In both cases we are trying to get around the fact that Microsoft, in their wisdom, wanted to keep the Office clipboard protected from normal automation. It would have been so much easier of they gave us a simple command ...
It works by ensuring that the 'Clear All' button is displayed on the screen, then searching through the windows hierarchy find the window that represents the panel that contains the button, then obtains the Accessibilty COM interface for that button (by looking for what control with an Accessibility interface is at a specific point, first ensuring that nothing unexpected is covering that point), and then uses that Accessibility interface to invoke the (default) action the button(hopefully the 'Clear All' button). <phew>
It's just a different route to the same thing - the 'small' code walks through the relevant part of the application's accessibility hierarchy instead of having to search for relevant windows, and ends up at the same point - an Accessibility COM interface to the 'Clear All' button (well, depending on the version of Office ...)
>it could be useful to have the different coding available as an alternative
Not sure about that. It is even more of a hack than the 'small' version. And frankly they are indeed both hacks. In both cases we are trying to get around the fact that Microsoft, in their wisdom, wanted to keep the Office clipboard protected from normal automation. It would have been so much easier of they gave us a simple command ...
-
- NewLounger
- Posts: 12
- Joined: 06 Jun 2024, 12:36
Re: Clear clipboard in excel vba
I messed around to get the small code working on both Office 19 & 21 64 bit versions. For Office 21, the clipboard pane has to be actually visible for the code to work so I had to revert to the big code's recursive Application.OnTime which causes a screen flicker when the clipboard pane is hidden. There are also some other changes in the code marked "NEW". Unfortunately, I wasn't able to come up with a reliable way to determine if the office clipboard is empty to prevent the error other than to code a copy before running the routine. Not sure if this advances the objective or not as I really don't like the screen flicker OR always running the routine even when the clipboard is empty. Anyways, HTH. Dave
To operate...
Code: Select all
Option Explicit
'*********NEW
Public RecursFlag As Boolean
'*********
#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_()
Dim avAcc, j As Long, bClipboard As Boolean
Dim MyPain As String, T As Double
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
'******NEW
RecursFlag = True
Application.OnTime Now + TimeValue("00:00:01"), "small_20202024_ClearOfficeClipBoard_": Exit Sub
'**********
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
'*******NEW
DoEvents
'*********
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 ' =======================================================================
'*****NEW
If RecursFlag Then
Let Application.CommandBars(MyPain).Visible = False 'Puts the viewer pain back as it was, open or closed
End If
'***********
End Sub
Code: Select all
RecursFlag = False
ActiveSheet.Range("A1").Copy
Application.CutCopyMode = False
Call small_20202024_ClearOfficeClipBoard_
-
- 5StarLounger
- Posts: 665
- Joined: 18 Jan 2022, 15:59
- Location: Re-routing rivers, in Hof, Beautiful Bavaria
Re: Clear clipboard in excel vba
Hi Dave
Thanks for the extra input.
I think all the codings , big and small , should only work if the offices viewer pane is visible, you possibly realise that.
The small coding should open the offices viewer pain, if it isn’t open. It should also put it back to the state it was in originally, ( that is what this bit does at the end
Let Application.CommandBars(MyPain).Visible = bClipboard
, - bClipboard being the Boolean set to the initial state of the offices clipboard viewer pain)
( You probably realise also that this is the code section that should open the offices viewer pain if it is initially closed,
It sounds like you are saying that in your Office 2021 the pane was not opening as it should. A problem like that is not such a strange thing to hear of in VBA. It’s a phenomena that occasionally seems to crop up, that phenomena of something not appearing as it should. The DoEvents: DoEvents is a trick that people have often found empirically, which to seems to cure that problem. I don’t think anyone understands yet either
_ why the problem occurs
or
_ why a double DoEvents sometimes solves the problem.
Perhaps some version of the Application.OnTime …… way works better sometimes
I once had a similar problem, where the final solution was a few DoEvents and some wait statements.
It’s useful to know what cures the problems on different versions. Perhaps then as time goes on , as other feedback perhaps comes in , we may get at a solution that works in all versions
(There is also the code line available of Let Application.DisplayClipboardWindow = True to experiment with.)
Alan
Thanks for the extra input.
I think all the codings , big and small , should only work if the offices viewer pane is visible, you possibly realise that.
The small coding should open the offices viewer pain, if it isn’t open. It should also put it back to the state it was in originally, ( that is what this bit does at the end
Let Application.CommandBars(MyPain).Visible = bClipboard
, - bClipboard being the Boolean set to the initial state of the offices clipboard viewer pain)
( You probably realise also that this is the code section that should open the offices viewer pain if it is initially closed,
Code: Select all
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
_ why the problem occurs
or
_ why a double DoEvents sometimes solves the problem.
Perhaps some version of the Application.OnTime …… way works better sometimes
I once had a similar problem, where the final solution was a few DoEvents and some wait statements.
It’s useful to know what cures the problems on different versions. Perhaps then as time goes on , as other feedback perhaps comes in , we may get at a solution that works in all versions
(There is also the code line available of Let Application.DisplayClipboardWindow = True to experiment with.)
Alan
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(
-
- NewLounger
- Posts: 12
- Joined: 06 Jun 2024, 12:36
Re: Clear clipboard in excel vba
The OnTime recursion always end up with the bClipboard = True hence the public RecursFlag. Interesting re. the double Do Events. I trialed many Do Events, Wait etc. with no success because I really don't like the recursive OnTime call but with no success. I'll repost if I have anything new to offer. Dave