VBA – Save String To Clipboard, Get String From Clipboard

Considering some recent work I’ve done and seeing questions in forums, I thought I’d quickly illustrate how easy it is to save and retrieve strings (text) from the clipboard.

There are several possible approaches for working with the Clipboard and I will explore 3 in this post:

DoCmd
APIs
Microsoft Forms Data Object (FDO)

DoCmd

If you are programming within Microsoft Access, using the DoCmd approach is most certainly the most straightforward and easiest method to employ! The drawback here being it is limited to only being available within Access.

Save Content to the Clipboard

DoCmd.RunCommand acCmdCopy

just remember to set the focus to the control you which to copy the data of before executing the DoCmd. So something you would need to do something like:

Me.FirstName.SetFocus
DoCmd.RunCommand acCmdCopy

Retrieve Content from the Clipboard

DoCmd.RunCommand acCmdPaste

Similarly, be sure to set the focus to where you want the data pasted to. So, you’d normally do something like:

Me.FirstName.SetFocus
DoCmd.RunCommand acCmdPaste

APIs

Now, if you want an approach that works beyond Microsoft Access, say Excel, Word, and beyond then many people will turn towards using APIs and thus can use code like:

#If VBA7 Then
    Private Declare PtrSafe Function CloseClipboard Lib "user32" () As Long
    Private Declare PtrSafe Function EmptyClipboard Lib "user32" () As Long
    Private Declare PtrSafe Function GetClipboardData Lib "user32" (ByVal wFormat As Long) As LongPtr
    Private Declare PtrSafe Function IsClipboardFormatAvailable Lib "user32" (ByVal wFormat As Long) As Long
    Private Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As LongPtr) As Long
    Private Declare PtrSafe Function SetClipboardData Lib "user32" (ByVal wFormat As Long, ByVal hMem As LongPtr) As LongPtr

    Private Declare PtrSafe Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, ByVal dwBytes As LongPtr) As LongPtr
    Private Declare PtrSafe Function GlobalLock Lib "kernel32" (ByVal hMem As LongPtr) As LongPtr
    Private Declare PtrSafe Function GlobalSize Lib "kernel32" (ByVal hMem As LongPtr) As Long
    Private Declare PtrSafe Function GlobalUnlock Lib "kernel32" (ByVal hMem As LongPtr) As Long

    Private Declare PtrSafe Function lstrcpy Lib "kernel32" Alias "lstrcpyW" (ByVal lpString1 As LongPtr, ByVal lpString2 As LongPtr) As LongPtr
#Else
    Private Declare Function CloseClipboard Lib "user32" () As Long
    Private Declare Function EmptyClipboard Lib "user32" () As Long
    Private Declare Function GetClipboardData Lib "user32" (ByVal wFormat As Long) As Long
    Private Declare Function IsClipboardFormatAvailable Lib "user32" (ByVal wFormat As Long) As Long
    Private Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
    Private Declare Function SetClipboardData Lib "user32" (ByVal wFormat As Long, ByVal hMem As Long) As Long

    Private Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, ByVal dwBytes As Long) As Long
    Private Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As Long
    Private Declare Function GlobalSize Lib "kernel32" (ByVal hMem As Long) As Long
    Private Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) As Long

    Private Declare Function lstrcpy Lib "kernel32" Alias "lstrcpyW" (ByVal lpString1 As Long, ByVal lpString2 As Long) As Long 'bug in Microsoft File!
#End If
Const CF_UNICODETEXT As Long = 13&

#If VBA7 Then
    Public Sub SetClipboard(sUniText As String)
        Dim iStrPtr         As LongPtr
        Dim iLen            As LongPtr
        Dim iLock           As LongPtr
        Dim iUnlock         As LongPtr
        Const GMEM_MOVEABLE As Long = &H2
        Const GMEM_ZEROINIT As Long = &H40
        'Const CF_UNICODETEXT As Long = &HD

        iLen = LenB(sUniText) + 2&
        iStrPtr = GlobalAlloc(GMEM_MOVEABLE Or GMEM_ZEROINIT, iLen)
        iLock = GlobalLock(iStrPtr)
        lstrcpy iLock, StrPtr(sUniText)
        GlobalUnlock iStrPtr

        OpenClipboard 0&
        EmptyClipboard
        SetClipboardData CF_UNICODETEXT, iStrPtr
        CloseClipboard
    End Sub

    Public Function GetClipboard() As String
        Dim iStrPtr         As LongPtr
        Dim iLen            As Long
        Dim iLock           As LongPtr
        Dim sUniText        As String
        'Const CF_UNICODETEXT As Long = 13&

        OpenClipboard 0&
        If IsClipboardFormatAvailable(CF_UNICODETEXT) Then
            iStrPtr = GetClipboardData(CF_UNICODETEXT)
            If iStrPtr Then
                iLock = GlobalLock(iStrPtr)
                iLen = GlobalSize(iStrPtr)
                sUniText = String$(iLen \ 2& - 1&, vbNullChar)
                lstrcpy StrPtr(sUniText), iLock
                GlobalUnlock iStrPtr
            End If
            GetClipboard = sUniText
        End If
        CloseClipboard
    End Function
#Else
    Public Sub SetClipboard(sUniText As String)
        Dim iStrPtr As Long
        Dim iLen As Long
        Dim iLock As Long
        Const GMEM_MOVEABLE As Long = &H2
        Const GMEM_ZEROINIT As Long = &H40
        'Const CF_UNICODETEXT As Long = &HD

        iLen = LenB(sUniText) + 2&
        iStrPtr = GlobalAlloc(GMEM_MOVEABLE Or GMEM_ZEROINIT, iLen)
        iLock = GlobalLock(iStrPtr)
        lstrcpy iLock, StrPtr(sUniText)
        GlobalUnlock iStrPtr

        OpenClipboard 0&
        EmptyClipboard
        SetClipboardData CF_UNICODETEXT, iStrPtr
        CloseClipboard
    End Sub

    Public Function GetClipboard() As String
        Dim iStrPtr As Long
        Dim iLen As Long
        Dim iLock As Long
        Dim sUniText As String
        'Const CF_UNICODETEXT As Long = 13&

        OpenClipboard 0&
        If IsClipboardFormatAvailable(CF_UNICODETEXT) Then
            iStrPtr = GetClipboardData(CF_UNICODETEXT)
            If iStrPtr Then
                iLock = GlobalLock(iStrPtr)
                iLen = GlobalSize(iStrPtr)
                sUniText = String$(iLen \ 2& - 1&, vbNullChar)
                lstrcpy StrPtr(sUniText), iLock
                GlobalUnlock iStrPtr
            End If
            GetClipboard = sUniText
        End If
        CloseClipboard
    End Function
#End If

Save Content to the Clipboard

Now to store a value in the Clipboard one would simply do:

Call SetClipboard(Me.txt_FirstName)

Retrieve Content from the Clipboard

To retrieve a value from the clipboard you need only do:

Me.txt_FirstName = GetClipboard()

The Downside to this approach is that it is bitness dependent and thus requires proper declarations depending on whether you’re running 32 or 64-bit installations, or the use of Conditional Compiler Directives.

Microsoft Forms Data Object (FDO)

If you want a way to avoid complex API solutions then let me introduce you to the Microsoft Forms Data Object (FDO).

This approach does not require any APIs, nor does it require any VBA References to be set as it uses Late Binding techniques. So it is pure and simple VBA code that should work in any VBA application regardless of bitness.

The following function can be called to retrieve the current clipboard content/string.

Save Content to the Clipboard

The following procedure can be called to set the clipboard’s content to a specified string.

'---------------------------------------------------------------------------------------
' Procedure : Clipboard_SetText
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Push a string value to the Clipboard
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Late Binding  -> none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sInput : The string to push to the clipboard
'
' Usage:
' ~~~~~~
' Call Clipboard_SetText("Hello World")
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2020-11-25              Initial Public Release
'---------------------------------------------------------------------------------------
Public Sub Clipboard_SetText(sInput As String)
    On Error GoTo Error_Handler

    With CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        .SetText sInput
        .PutInClipboard
    End With

Error_Handler_Exit:
    On Error Resume Next
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: Clipboard_SetText" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

To use it, you simply do:

Call Clipboard_SetText(Me.FirstName)

Retrieve Content from the Clipboard

'---------------------------------------------------------------------------------------
' Procedure : Clipboard_GetText
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Retrieve the clipboard value
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Late Binding  -> none required
'
' Usage:
' ~~~~~~
' Debug.Print Clipboard_GetText
' sClipboardValue = Clipboard_GetText
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2020-11-25              Initial Public Release
'---------------------------------------------------------------------------------------
Public Function Clipboard_GetText() As String
    On Error GoTo Error_Handler

    With CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        .GetFromClipboard
        Clipboard_GetText = .GetText
    End With

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: Clipboard_GetText" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

To use it, you simply do:

Me.FirstName = Clipboard_GetText()

As you can see, the code is actually very simple and comprised of only a few lines of code, uses Late Binding thus no reference libraries are required to be defined and doesn’t use any application specific code so it is completely portable so it can be used in any VBA application (Access, Excel, Outlook, PowerPoint, Word, …).

This could also be a great place to use Self-healing Object Variables (SHOVs) to improve global performance!

Resources on the Subject

Microsoft Forms reference

Office VBA reference topic

VBA - Early Binding and Late Binding

Early Binding and Late Binding When coding using VBA, as a developer, you have a choice of either using Early Binding or Late Binding. Below I will attempt to explain in plain English what each technique consists of and go over their Pros and Cons. Please keep in mind the advice provided below is all…

Continue reading →

VBA – Early Binding and Late Binding – Part 2

In my first post on the subject, VBA – Early Binding and Late Binding, I went over the basics, but I thought I’d push forward a little more to help demonstrate how to implement “The Best of Both World”. As I explained, in my first article, the best of both worlds consists of developing using…

Continue reading →

Page History

Date	Summary of Changes
2020-11-25	Initial Release
2023-09-05	Complete page revamp & addition of DoCmd and API approaches.

5 responses on “VBA – Save String To Clipboard, Get String From Clipboard”

Blair Baker May 27, 2021 at 7:39 pm

Clipboard_GetText works for me but “SetText” sub does not.
1. Daniel Pineault Post authorMay 28, 2021 at 11:29 am
  
  I’m not sure what to say. I just quickly tested on my system and it worked for me. Does it report any particular error?
Keith Swerling September 21, 2021 at 12:08 pm

“SetText” sub works when you use “GetText”.
So for example when you Set from a range, and Get to a range, works good.
But when I use “SetText” and paste outside of Excel, I get 2 black diamonds with question marks in them. ->
J Stribling February 24, 2022 at 12:14 pm

Daniel,
Thank you for a couple of excellent subs to accomplish this task. They work great and I really appreciate your design for maximum portability. Nice work
HABlet June 30, 2022 at 11:42 am

Thank you for teaching me about this.
If MSForms 2.0 Object Library (FM20.dll) is available, you can make the code slightly cleaner.
Public Function Clipboard_GetText() As String
Dim data_object as MSForms.DataObject
Set data_object = New MSForms.DataObject
data_object.GetFromClipboard
Clipboard_GetText = data_object.GetText
etc

Is there any way to retrieve in one go the output of a sequence of commands (e.g a file containing multiple PowerShell commands) ? I have not been able to acquire anything more than the output of the just the last command.