Solved

Clear Access clipboard

Posted on 2015-01-14
23
304 Views
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.

Thanks!
0
Comment
Question by:mickferrari
  • 8
  • 8
  • 6
  • +1
23 Comments
 
LVL 10

Expert Comment

by:Anthony Berenguel
ID: 40550369
try
Application.CommandBars("Clipboard").Controls(4).Execute

Open in new window

0
 
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.

mx
0
 
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&)
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
 
LVL 26

Expert Comment

by:Nick67
ID: 40551412
@mx
Does that clear the Office clipboard (which I think is the question) or the Windows clipboard?
0
 
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 ?
0
 
LVL 26

Expert Comment

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

mx
0
 

Author Comment

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

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

Expert Comment

by:Nick67
ID: 40552586
@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
 

Author Comment

by:mickferrari
ID: 40552590
Ah, I see.

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

Expert Comment

by:Nick67
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.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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.

mx
0
 

Author Comment

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

Another office limitation?
0
 
LVL 26

Expert Comment

by:Nick67
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.
0
 

Author Comment

by:mickferrari
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.
0
 
LVL 26

Accepted Solution

by:
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
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
 
LVL 75
ID: 40552677
I'm sure there is an API call(s) somewhere, but so far I have not found it.

mx
0
 
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).

--
Enjoy,

Tony Jollans
Microsoft Word MVP"
0
 

Author Comment

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

Expert Comment

by:Nick67
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.
0
 

Author Closing Comment

by:mickferrari
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.
0
 
LVL 26

Expert Comment

by:Nick67
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.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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 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…

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now