Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Clear Access clipboard

Posted on 2015-01-14
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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 500 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 - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 500 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

715 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