42

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?

hawbsl
  • 15,313
  • 25
  • 73
  • 114
  • 4
    Resistance to API code in favor of adding an external reference is really foolish. – David-W-Fenton Apr 06 '11 at 20:52
  • 1
    I almost didn't click the MS link because I read "gazillion", just FYI to others, it is 5 API calls, and they put it in a nice function for you even. Heck, I'd almost like to copy/paste it and post here because we all know how MS is about removing links. – eselk Jun 22 '12 at 16:08
  • Problem with API's is when you have to code for use in both 32 and 64 bit environments, it gets a bit complicated. Been there, done that, got the straightjacket. I feel it is definitely worth the effort to add a reference to the Forms library. – Steve Levine Aug 02 '20 at 04:08

6 Answers6

64

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
Cody Gray - on strike
  • 239,200
  • 50
  • 490
  • 574
  • 5
    +1 for sticking with the API. In terms of working with the API, it's really not *that* much extra code. Especially since you just copy and paste the code once from MSDN, then call a simple function `ClipBoard_SetData()` when you need it. I agree it is a curious oversight for VBA, but the API route is really not that bad. – mwolfe02 Apr 05 '11 at 13:19
  • +1 four lines plus a reference to Forms dll. perfectly answered our requirement. – hawbsl Apr 05 '11 at 13:38
  • @mwolfe02 take your point, would certainly do that for a future project – hawbsl Apr 05 '11 at 13:38
  • 2
    The API is the only way to go here. Adding a reference for something that can be done with an API is really foolish. – David-W-Fenton Apr 06 '11 at 20:53
  • 1
    @David: Agreed, I even mentioned that in my answer. One should only use the Forms library to do this if they were already using it for other reasons. The question did ask, however, if those were the only two options. This is a third, even if I don't recommend it. – Cody Gray - on strike Apr 07 '11 at 06:43
  • 11
    Find the `Microsoft Forms 2.0 Object Library` at `C:\windows\system32\fm20.dll` (XP 32) – Tino Jan 18 '12 at 08:41
  • I used to use Forms Library for Excel VBA clipboard manipulation, then when I upgraded to Windows 8 x64, it wouldn't work anymore. So I had to go the [API route](http://msdn.microsoft.com/en-us/library/office/ff192913(v=office.14).aspx) – Baodad Jun 16 '13 at 03:14
  • GlobalLock API doesn't work in 64-bit Office, I think because it needs to be declared as PtrSafe... – Baodad Aug 21 '13 at 20:25
  • The problem with the links to the API code is that they all fail if you try to copy a string with containing Unicode code points beyond extended ASCII. The API functions are ANSI string only and the way the API calls are declared means that your UTF-16 VBA strings will be passed as ANSI strings anyway. What a shame, we keep copying the same old stale code... :-( – Renaud Bompuis Sep 30 '13 at 14:07
  • 1
    @Renaud It's not simply "old stale code" being copied and pasted. It's the fact that old-school VB has some real limitations regarding dealing with Unicode strings. The built-in String type is ANSI. It is possible to work around them, but far beyond the scope of a simple answer to a SO question. One could write a whole book on that (and then never get it published because the technology is "obsolete"). Anyway, if you're supporting Unicode everywhere else, calling the Unicode versions of the WinAPI functions is relatively straightforward: declare the string as LONG, and then use StrPtr(). – Cody Gray - on strike Jun 20 '16 at 04:47
  • _Adding a reference to this library only to use the clipboard seems a bit silly._ - Why? Is there is any other alternative? Isn't it like adding `using` or `import` in other frameworks? – t3chb0t Jul 09 '20 at 17:04
  • Dragging in dependencies that you do not really require, @t3chb0t. – Cody Gray - on strike Jul 10 '20 at 06:03
9

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

Julio Nobre
  • 4,196
  • 3
  • 46
  • 49
Jim Tonti
  • 91
  • 1
  • 3
7

I couldn't figure out how to use the API using the first Google results. Fortunately a thread somewhere pointed me to this link: http://access.mvps.org/access/api/api0049.htm

Which works nicely. :)

Mallow
  • 844
  • 1
  • 13
  • 37
1

Easy TWO line code:

It's not so complicated, I don't understand why all solutions found in the net are so complicated.

Sub StoreData()

  Set objCP = CreateObject("HtmlFile")
  objCP.ParentWindow.ClipboardData.SetData "text", "Some text for clipboard"

End Sub
DavidTaubmann
  • 3,223
  • 2
  • 34
  • 43
0

There are many examples listed here but none seem to cover the direct way of using the API that also works with Access and Excel 32 bit and 64 bit.

I don't want to steal anyone else's work so I'm pointing to an article that has a solution.

https://stackoverflow.com/a/35512118/1898524

Ben
  • 1,168
  • 13
  • 45
0

Following up on David's idea, if you want to pass in an argument, it has to be double-quoted.

Public Sub SetClipboardText(ByVal Text As String)
    Dim QuotedText As String
    QuotedText = """" & Text & """"
    Set HtmlFileObject = CreateObject("HtmlFile")
    HtmlFileObject.ParentWindow.ClipboardData.SetData "text", Eval(QuotedText)
End Sub
CalvinDale
  • 9,005
  • 5
  • 29
  • 38