Clear Access clipboard

Hopefully an easy one.

Is it possible to clear the Access clipboard using VBA? if so, how is it done?

I've had a bit of a search elsewhere but I can't get a straight answer.

Thanks!
mickferrariICT TechnicianAsked:
Who is Participating?
 
Nick67Commented:
We're stymied.
There's a clipboard CommandBar
Set sCmdBar = Application.CommandBars("clipboard")
The count of the number of controls on it in Access and Excel 2003
Zero.

Sorry man.
I hate the word impossible...
I've got no idea how it would get done, even with SendKeys -- which with Access & Win 7 is a no-no because it toggles NumLock.



Function ListCmdBars()
'On Error GoTo Error_Handler
    Dim i As Long
    Dim sCmdBar As Object
    Dim ctrl As Object
 
    'Debug.Print "Number", "Name", "Visible", "Built-in"
    'For i = 1 To Application.CommandBars.Count
        Set sCmdBar = Application.CommandBars("clipboard")
        MsgBox i & "," & sCmdBar.Name & "," & sCmdBar.Visible & "," & sCmdBar.BuiltIn
        MsgBox sCmdBar.Controls.Count
        'For Each ctrl In sCmdBar.Controls
            'MsgBox ctrl.Item
       ' Next ctrl
    'Next i
Error_Handler_Exit:
    On Error Resume Next
    Set sCmdBar = Nothing
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: ListCmdBars" & vbCrLf & _
           "Error Description: " & Err.Description, vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

Open in new window

0
 
Anthony BerenguelCommented:
try
Application.CommandBars("Clipboard").Controls(4).Execute

Open in new window

0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Just keep in mind using that approach the following:

Whereas in A2013 the CommandBar object still exists, there is zero guarantee it will in the next version of Access, since CommandBars were essentially deprecated in A2007
Also .. there is no guarantee that Controls(4) might not be Controls (SomeOtherNumber) if a future release.

mx
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
This code of from the legendary Steven Lebans (retried now):
This code will always work ... and I just tested it.

You have to:
Open the Clipboard
Clear the Clipboard
Close the Clipboard


' Place these API declarations at the top of your Form (or a Module) in the General Declarations area.

Private Declare Function OpenClipboard Lib "user32" (ByVal hWnd As Long)
As Long
Private Declare Function CloseClipboard Lib "user32" () As Long
Private Declare Function EmptyClipboard Lib "user32" () As Long

The create this Sub (or Function) ... this uses a Command Button to execute the code.

Private Sub cmdClip_Click()
On Error GoTo Err_cmdClip_Click

' Open, Empty and Close Clipboard
' No Clipboard API error handling
Call OpenClipboard(0&)
EmptyClipboard
CloseClipboard
MsgBox "ClipBoard Cleared!"


Exit_cmdClip_Click:
Exit Sub

Err_cmdClip_Click:
MsgBox Err.Description
Resume Exit_cmdClip_Click

End Sub

From http://www.lebans.com
0
 
Nick67Commented:
@mx
Does that clear the Office clipboard (which I think is the question) or the Windows clipboard?
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Windows ...
What is the difference ?

In fact ... what is 'Access Clipboard' ?

Control C
Control V
Control X

Is there something else ?
0
 
Nick67Commented:
Yes.
There's the Office Clipboard.
Been around since O2000.
You can turn it on or off.
Any copy done from an active Office application goes to the Office Clipboard & the Windows clipboard.
The Office one can hold more than one item
Capabilities have spun up with each version
O2000 was 12 items
O2003 was 24 items
O2013 is still 24 items
Items can be more than just text.

Very handy if you have multiple cuts-copies-and-pastes to accomplish.
Fire up the help.
'Office Clipboard'
Configuration is on the Home tab of the Ribbon.  Very, small dropdown, near the bottom right of the first default grouping
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Well ...  ok.
I assume OP is referring to ... Windows Clipboard ... but then again 'Access Clipboard' ?

mx
0
 
mickferrariICT TechnicianAuthor Commented:
Hi everyone, good to see so many suggestions.

I am referring to the Access Clipboard not the Windows Clipboard.
0
 
Nick67Commented:
@mickferrari
Neither me nor @mx have heard tell of an 'Access Clipboard'
There's the Office Clipboard, that's common to all Office applications
clipboardAnd there's the memory location accessed by Windows when you cut-copy-and-paste by whatever means suit you.

Is it one, the other, or something else?
0
 
mickferrariICT TechnicianAuthor Commented:
Ah, I see.

Then it's the office clipboard that I am trying to clear.
0
 
Nick67Commented:
Did you give the original suggestion a whirl?
Basically my research suggested that short of a commandbars call or sendkeys() there's no good way to get it done.
It's another one of these objects, like the WIA dialog box, that lives halfway between the completely inaccessible and the exposed.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Well ... since you use the same Control C to copy to both, I wonder if the code will clear both. Probably not, but worth a shot.

mx
0
 
mickferrariICT TechnicianAuthor Commented:
I did give the original suggestion a go but it didn't appear to work.

Another office limitation?
0
 
Nick67Commented:
Most of the web code is from the perspective of getting not-Access apps to clear it.
Primarily Excel & Word
And therefore the menubars and commandbars are not the same.

And things are necessarily the same between A2003- and A2007+
What version are you targeting.
There's no point for me to try an suss out a commandbars statement that'll work for A2003 if you're targeting A2013.
And, as @mx pointed out, CommandBars -- if not the CommandBars VBA objects -- are heading for deprecation.
0
 
mickferrariICT TechnicianAuthor Commented:
I am using Access 2010.

Depreciation isn't a huge problem at the moment as I'll be using 2010 for a while yet.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
I'm sure there is an API call(s) somewhere, but so far I have not found it.

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Here is some additional insight I just found:

"Tony Jollans replied on 24-Apr-07 05:17 PM
If you are using Office 2000 you do have some indirect access to the Office
Clipboard from VBA - but you do not have it in any later versions.

In Office 2000 the Office Clipboard is presented as a CommandBar and so you
can programmatically manipulate the Controls, for example ...

CommandBars("Clipboard").Controls("Clear Clipboard").Execute

In Office XP and later, the Office Clipboard is presented as a Task Pane and
the 'back door' has been shut. AFAIK, VBA access to it post-2000 is
impossible - I don't think you can even do it with SendKeys (but don't quote
me on that).

--
Enjoy,

Tony Jollans
Microsoft Word MVP"
0
 
mickferrariICT TechnicianAuthor Commented:
Thanks for all your digging Nick and Joe. I appreciate all the help you have given.

Doesn't look like there is a clear cut way to do what i want. I'll leave this question up for a bit longer and see if another expert can help out.

Otherwise we might have to admit defeat on this one.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Pretty sure there MUST ... be a way ...
0
 
Nick67Commented:
I hate 'impossible' but I don't think that short of writing some C++ add-on that its going to happen.  There's just nothing there for VBA / Win API to get.  I don't think even PowerShell can do it.  We'd've seen footprints.
0
 
mickferrariICT TechnicianAuthor Commented:
For anyone that reads this thread. A solution was not found. The Answers accepted in this thread are to give you an idea of why a solution was not found.

Thanks again for the help of Nick and Joe.
0
 
Nick67Commented:
You're welcome.
I did post that complete lack of VBA control of the clipboard as something MS should address in @jim Dettman's discussion of improvements to Access.  Joe's an MVP, too, and can maybe bring that to the tale with MS.  Hopefully, MS will get around to making what's needful, possible, in the next release.

I'm not counting on it, though.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.