How to Copy Text to and from the Clipboard in Excel and Access Visual Basic

You can record a macro to copy from and paste to the Excel worksheet.  Easy enough.  But what if you have a value in Microsoft Excel VBA that you want to put on the clipboard for another program?  Or you want your code to get text from the clipboard after you copied it there from another program?  Then you want to access the clipboard directly.  And that is not straightforward in Excel code.

It is a strange omission that Excel Visual Basic does not give us direct access to the Clipboard.  Here are some ways around this problem.

1.        Simple Spreadsheet Copy-Paste

If you want to copy to or from the worksheet, that is a simple matter.  You can record a macro with a copy or a paste.  For example:

  ' Put item onto Clipboard (ordinary Excel Copy):
  Range("B1").Copy
  ' Paste item from Clipboard (ordinary Excel Paste):
  ActiveSheet.Paste     ' Or (text only):
  Selection.PasteSpecial Paste:=xlPasteValues

But what if you want to access the Clipboard directly from your Visual Basic code?  You may have a lot of text that you want to copy to the clipboard.  Or you may have copied a lot of text from another program and now want to process it in Excel.  Yes, you could put it into the spreadsheet first, for example with:

  ' Put item into a cell as text (prefix with single quote):
  Range("B1").Formula = "'" & Tex$

And then you could process the cell’s contents, in this case, B1.

But that is messy.  And you have to make sure you do not overwrite anything (perhaps by using a remote part of the spreadsheet).

2.        A Module Using a Data Object

Wouldn’t it be cleaner to get the data straight off the clipboard into a variable in your code?  Or put data from your code directly on the clipboard?

Here is a module with two routines, one to do each:

' Module Clipboard2 from www.ExcelForEngineers.com
Option Explicit

' The next statement needs:
' Tools > References: Microsoft Forms 2.0 Object Library (C:\Windows\System32\FM20.DLL)
Dim MyData As DataObject
' The DataObject supports only text formats.

Function Clipboard_GetText$()   ' Get Text from Clipboard:
  ' Code from Bank Megaplex51:
  Set MyData = New DataObject
  MyData.GetFromClipboard       ' Copy from Clipboard
  Clipboard_GetText$ = MyData.GetText(1)
End Function

Sub Clipboard_SetText(Tex$)     ' Put Text on Clipboard:
  Set MyData = New DataObject
  MyData.SetText Tex$
  MyData.PutInClipboard         ' Copy to Clipboard
End Sub

There is a drawback to the above module. For the DataObject declaration to work, you must go to the Tools menu, References dialog.  Add the Microsoft Forms 2.0 Object Library (or a more recent version).  If it is not in the list, browse for C:\Windows\System32\FM20.DLL.

What if you do not want to add to your references, or can’t find a version of the Microsoft Forms DLL?

3.        A Read/Write Clipboard Function

From this stackoverflow.com answer – the writer says, “’ I prefer this much simpler approach, which will work independently of the architecture. And I like the approach of a single function to either read/write the clipboard.”  We concur.

' Module Clipboard3 from www.ExcelForEngineers.com
Option Compare Text
Option Explicit

Function Clipboard(Optional StoreText As String) As String
' PURPOSE: Read/Write to Clipboard
' Source:  ExcelHero.com (Daniel Ferry)

  Dim x As Variant
  ' Store as variant for 64-bit VBA support:
  x = StoreText
  ' Create HTMLFile Object:
  With CreateObject("htmlfile")
    With .parentWindow.clipboardData
      If Len(StoreText) Then
        ' Write to the clipboard:
        .setData "text", x
      Else
        ' Read from the clipboard (no variable passed through):
        Clipboard = .GetData("text")
      End If
    End With
  End With
End Function

You can test both modules by putting the following in the Immediate Window:

In each case, the third line is the result you should get.

Both the Clipboard2 and Clipboard3 modules work in Microsoft Access too.