Append to Clipboard using MS Access VBA

Doug 25 Reputation points
2026-05-19T13:01:34.2066667+00:00

I have a delimited string, ConstStr = "1356, 1360, 1366, 1368", and would like to add each value in the string to the clipboard so that, when executing a "Paste", all the values would fill individual cells, e.g:

1356

1360

1366

1368

The idea is expressed in the LoadClipboard() function below, where clipboard is the actual clipboard used by Microsoft applications.

ConstStr = "1356, 1360, 1366, 1368"

Function LoadClipboard(ConstStr As String) As String

Dim I As Long

Dim A() As String

Dim Clipboard As String

Clipboard = ""

A() = Split(ConstStr, ",")

For I = 0 To UBound(A())

    A(I) = Trim(A(I))

    Clipboard = Clipboard & A(I) & vbCrLf

Next

LoadClipboard = Clipboard

'Paste clipboard into another program

End Function

My question are:

  1. How do you actually activate the clipboard and load it with the clipboard variable above?
  2. Can the clipboard be appended to several times without pasting and only paste the final result?
  3. Does the clipboard only accept one input (i.e. all future inputs to the clipboard overwrite the clipboard contents)?

Thanks for in advance for your help

Microsoft 365 and Office | Access | Development
0 comments

3 answers

Sort by: Oldest
Most helpful Newest Oldest

  1. AI answer

    2026-05-19T13:01:50.92+00:00

    Use a single text string with row delimiters and then put that string on the Windows/Office Clipboard; Excel (and other apps) will split it into rows when pasted.

    For the specific function shown, build the string as already done, then move it to the Clipboard using either:

    1. Access RunCommand (for a control’s contents), or
    2. A DataObject / Clipboard API (for arbitrary text).

    1. Loading the Clipboard from Access VBA

    If the text is in a control (for example, a text box on a form):

    Private Sub cmdCopy_Click()
    Me!txtNotes = LoadClipboard("1356, 1360, 1366, 1368")
    Me!txtNotes.SetFocus
    DoCmd.RunCommand acCmdCopy
End Sub

    DoCmd.RunCommand acCmdCopy copies the active control’s contents to the Clipboard.

    Reference: Send information to the Clipboard

    If the text is not in a control, use a DataObject and PutInClipboard to place the string directly on the Clipboard:

    Dim MyData As DataObject

Sub CopyListToClipboard()
    Dim s As String
    s = LoadClipboard("1356, 1360, 1366, 1368")   ' builds CRLF-delimited string

    Set MyData = New DataObject
    MyData.SetText s
    MyData.PutInClipboard
End Sub

    PutInClipboard transfers the data from the DataObject to the Clipboard. Excel will interpret vbCrLf as row breaks when pasting.

    Reference: Paste, PutInClipboard, SetText methods example

    2. Appending to the Clipboard

    The standard Clipboard itself does not support “append” in the sense of keeping multiple separate text items and concatenating them later. Each time text is placed on the Clipboard in the same format, the previous text in that format is replaced.

    To simulate appending:

    1. Maintain an accumulator string in code (for example, ClipboardText = ClipboardText & NewChunk & vbCrLf).
    2. When finished, call SetText / PutInClipboard once with the final accumulated string.

    This matches the pattern already used in LoadClipboard.

    3. Overwrite behavior

    For text formats, the Clipboard holds one value per format. When new text is placed on the Clipboard in that format, the previous text is discarded.

    From the Clipboard/DataObject guidance:

    • “If you store data with a format that is already in use, the new data is saved and the old data is discarded.”

    Reference: What is the difference between the DataObject and the Clipboard?

    So:

    1. Load the Clipboard by copying from a control (acCmdCopy) or by using a DataObject with SetText + PutInClipboard.
    2. To “append,” build the full string in VBA and write it to the Clipboard once.
    3. The Clipboard accepts one value per format; new text in the same format overwrites the previous text.

    References:

    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

  2. Doug 25 Reputation points
    2026-05-19T13:19:32.7466667+00:00

    Dim MyData As DataObject produces this error:

    User's image

    Is there a Reference to a dll required?

    Was this answer helpful?

    0 comments
  3. Anonymous
    2026-05-19T13:37:48.2233333+00:00

    Hi @Doug
    Thank you for sharing the additional insight. You can try the options below:

    Option 1: Add the Library Reference
    The DataObject belongs to the Microsoft Forms 2.0 Object Library. To enable it:

    • Open the VBA window, then go to Tools > References
    • Scroll through the list and locate Microsoft Forms 2.0 Object Library.
    • Select the checkbox and click OK.

    If it's missing, click the Browse... button in the same window, navigate to C:\Windows\System32\ (or C:\Windows\SysWOW64), locate a file named FM20.DLL, select it, and click Open.

    Option 2: Use the “no-reference” approach (late binding)

    If you prefer not to depend on library references, especially when sharing the database with others, late binding can help avoid compatibility issues. This approach allows Access to resolve the DataObject at runtime without requiring any manual setup.

    You can adjust your code as follows:

    Sub CopyListToClipboard()
    Dim s As String
    s = LoadClipboard("1356, 1360, 1366, 1368")
    ' Change "As DataObject" to "As Object"
    Dim MyData As Object 
    ' Use CreateObject instead of "New"
    Set MyData = CreateObject("MSForms.DataObject")
    MyData.SetText s
    MyData.PutInClipboard
End Sub

    If you have any updates, please feel free to share.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.