How to copy to clipboard using Access/VBA?

Question

Using VBA inside Access2003/2007.

How to copy the contents of a string variable to the clipboard?

This site recommends a creating a zero length TextBox, copying the string to the TextBox, then running DoCmd.RunCommand acCmdCopy. Ugh. I mean, we may go down the route. But still. Ugh.

While the MS knowledgebase article shows us how to do it but it involves a number of Windows API calls. Yuk.

Are those the only two options?

Answer 1

VB 6 provides a Clipboard object that makes all of this extremely simple and convenient, but unfortunately that's not available from VBA.

If it were me, I'd go the API route. There's no reason to be scared of calling native APIs; the language provides you with the ability to do that for a reason.

However, a simpler alternative is to use the DataObject class, which is part of the Forms library. I would only recommend going this route if you are already using functionality from the Forms library in your app. Adding a reference to this library only to use the clipboard seems a bit silly.

For example, to place some text on the clipboard, you could use the following code:

Dim clipboard As MSForms.DataObject Set clipboard = New MSForms.DataObject clipboard.SetText "A string value" clipboard.PutInClipboard 

Or, to copy text from the clipboard into a string variable:

Dim clipboard As MSForms.DataObject Dim strContents As String  Set clipboard = New MSForms.DataObject clipboard.GetFromClipboard strContents = clipboard.GetText 

Answer 2

User Leigh Webber on the social.msdn.microsoft.com site posted VBA code implementing an easy-to-use clipboard interface that uses the Windows API:

http://social.msdn.microsoft.com/Forums/en/worddev/thread/ee9e0d28-0f1e-467f-8d1d-1a86b2db2878

You can get Leigh Webber's source code here

If this link doesn't go through, search for "A clipboard object for VBA" in the Office Dev Center > Microsoft Office for Developers Forums > Word for Developers section.

I created the two classes, ran his test cases, and it worked perfectly inside Outlook 2007 SP3 32-bit VBA under Windows 7 64-bit. It will most likely work for Access. Tip: To rename classes, select the class in the VBA 'Project' window, then click 'View' on the menu bar and click 'Properties Window' (or just hit F4).

With his classes, this is what it takes to copy to/from the clipboard:

Dim myClipboard As New vbaClipboard  ' Create clipboard  ' Copy text to clipboard as ClipboardFormat TEXT (CF_TEXT)     myClipboard.SetClipboardText "Text to put in clipboard", "CF_TEXT"      ' Retrieve clipboard text in CF_TEXT format (CF_TEXT = 1) mytxt = myClipboard.GetClipboardText(1) 

He also provides other functions for manipulating the clipboard.

It also overcomes 32KB MSForms_DataObject.SetText limitation - the main reason why SetText often fails. However, bear in mind that, unfortunatelly, I haven't found a reference on Microsoft recognizing this limitation.

-Jim