Get Clipboard data from VBA

by Philipp Stiefel, originally published September 3rd, 2019, last updated September 3rd, 2019

I recently encountered a question on a forum where someone copied a text file from his email inbox onto the clipboard and needed to retrieve this file or its content in Access VBA.

Well, there are couple of samples available to get Text from the clipboard using the Windows API. However, none of those samples work when you don’t have the text directly in the clipboard but rather a file containing text. Despite being busy otherwise I couldn’t resist to hack a small sample class together, which would retrieve the file content of a file from the clipboard using the GetClipboardData function from the Windows API.

The Windows Clipboard API proved to be way more complex than I originally anticipated. Nevertheless, I managed to create a basic VBA Clipboard class that nicely wraps this complexity under its hood.

Capabilities

Due to time constraints, this sample is current very limited only. It can retrieve plain text from the clipboard, ANSI and Unicode (untested!), and the file content of any file on the clipboard (tested only with text). – It cannot write any data to the clipboard yet. I guess, I will add that whenever I find the time.

This download not limited to Microsoft Access. It is a VBA class module that can be used in any VBA enabled application.

How to use

To integrate this class into your application, download the Clipboard-ZIP-Archive and unpack it. Then open the VBA-Editor with your application, Use the “File” -> “Import File” menu to import the Clipboard.cls file into your application.

The class module Clipboard hat its Attribute VB_PredeclaredId set to True. This means you do not need to explicitly create an instance of that class but can just reference this class as Clipboard in your code.

If you just want to retrieve text (CF_TEXT) from the clipboard, a single line of code is enough.

Please note, the Clipboard.GetData method returns a variant. When retrieving just plain text it is just a wrapped string that can be converted straight to a string by simple assignment, as shown above.

However, if you retrieve file content, Clipboard.GetData returns a byte array wrapped by that variant. If that is the content of a text file you want to process in VBA you need to use the StrConv-Function, to explicitly convert that byte array to a String.

Limitations

While it is not tested yet, it should also work to retrieve the content of a binary file from the clipboard and then process the raw byte array. But be aware, the actual file content is not always available even when the clipboard reports data in the FileContents (CFSTR_FILECONTENTS) format present. It reliably works when copying a file from an email in Outlook, but it does not when you just select a file in Windows Explorer and “Copy” it from the context menu.

Download

Finally, here is the link to the download of the VBA-Clipboard-Class.

Share this article: