Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1095
  • Last Modified:

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!
0
mickferrari
Asked:
mickferrari
  • 8
  • 8
  • 6
  • +1
2 Solutions
 
Anthony BerenguelCommented:
try
Application.CommandBars("Clipboard").Controls(4).Execute

Open in new window

0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Nick67Commented:
@mx
Does that clear the Office clipboard (which I think is the question) or the Windows clipboard?
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
Well ...  ok.
I assume OP is referring to ... Windows Clipboard ... but then again 'Access Clipboard' ?

mx
0
 
mickferrariAuthor 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
 
mickferrariAuthor 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 MVP, Access and Data Platform)Commented:
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
 
mickferrariAuthor 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
 
mickferrariAuthor 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
 
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
I'm sure there is an API call(s) somewhere, but so far I have not found it.

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
mickferrariAuthor 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 MVP, Access and Data Platform)Commented:
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
 
mickferrariAuthor 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

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 8
  • 8
  • 6
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now