Clear Access clipboard

Posted on 2015-01-14
Last Modified: 2015-01-21
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.

Question by:mickferrari
  • 8
  • 8
  • 6
  • +1
LVL 10

Expert Comment

by:Anthony Berenguel
ID: 40550369

Open in new window

LVL 75
ID: 40550567
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.

LVL 75
ID: 40550572
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&)
MsgBox "ClipBoard Cleared!"

Exit Sub

MsgBox Err.Description
Resume Exit_cmdClip_Click

End Sub

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

LVL 26

Expert Comment

ID: 40551412
Does that clear the Office clipboard (which I think is the question) or the Windows clipboard?
LVL 75
ID: 40552185
Windows ...
What is the difference ?

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

Control C
Control V
Control X

Is there something else ?
LVL 26

Expert Comment

ID: 40552327
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
LVL 75
ID: 40552430
Well ...  ok.
I assume OP is referring to ... Windows Clipboard ... but then again 'Access Clipboard' ?


Author Comment

ID: 40552557
Hi everyone, good to see so many suggestions.

I am referring to the Access Clipboard not the Windows Clipboard.
LVL 26

Expert Comment

ID: 40552586
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?

Author Comment

ID: 40552590
Ah, I see.

Then it's the office clipboard that I am trying to clear.
LVL 26

Expert Comment

ID: 40552605
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.
LVL 75
ID: 40552608
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.


Author Comment

ID: 40552611
I did give the original suggestion a go but it didn't appear to work.

Another office limitation?
LVL 26

Expert Comment

ID: 40552632
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.

Author Comment

ID: 40552638
I am using Access 2010.

Depreciation isn't a huge problem at the moment as I'll be using 2010 for a while yet.
LVL 26

Accepted Solution

Nick67 earned 250 total points
ID: 40552662
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

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
    On Error Resume Next
    Set sCmdBar = Nothing
    Exit Function
    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

LVL 75
ID: 40552677
I'm sure there is an API call(s) somewhere, but so far I have not found it.

LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 250 total points
ID: 40552684
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).


Tony Jollans
Microsoft Word MVP"

Author Comment

ID: 40552699
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.
LVL 75
ID: 40552702
Pretty sure there MUST ... be a way ...
LVL 26

Expert Comment

ID: 40552761
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.

Author Closing Comment

ID: 40563385
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.
LVL 26

Expert Comment

ID: 40563499
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.

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question