Excel VBA: Copy and Paste text to/from the Clipboard

Microsoft ExcelLeave a Comment on Excel VBA: Copy and Paste text to/from the Clipboard

Excel VBA: Copy and Paste text to/from the Clipboard

There is no built-in functionality in Excel to use the clipboard. We could have implemented the clipboard functionality from scratch using COM DataObject and Win32 API. To save time we will instead show you how to use the Microsoft Forms DataObject class.

There are two different ways to use the Microsoft Forms DataObject:

  1. Add a reference to the COM library FM20.dll (Microsoft Forms 2.0), or
  2. Use late binding by calling CreateObject

In most cases you don’t want to add a reference to the Microsoft Forms 2.0 COM library. It is messy to maintain for other developers, and if you re-use your code in another Excel Workbook you would have to remember to add the reference too. In this article we will show you have to use late binding which is enabled through the COM IDispatch interface.

Late binding

The correct way in VBA to create a new instance of a COM Object is to use the CreateObject method. In a perfect world we would have written a statement like

' Instance
Dim objDataObject As Object

' Allocate instance (DOES NOT WORK)
Set objDataObject = CreateObject("Microsoft.Forms.DataObject")

Unfortunately the above statement doesn’t work. This is because when we run Regsvr32 to install the FM20.dll library no named entries are registered in the registry. Instead we need to use the Class ID (CLSID) to find the object definition in the registry. The class ID (CLSID) for the Microsoft Forms 2.0 DataObject is “1C3B4210-F441-11CE-B9EA-00AA006B1A69”. To create a new instance of the DataObject we need to do the following

' Instance
Dim objDataObject As Object

' Allocate new instance of Microsoft Forms 2.0 DataObject
Set objDataObject = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

The object definition of the Microsoft Forms DataObject can be found in the registry (regedit.exe). You can see that the CLSID refers to the FM20.dll.

Microsoft Form 2.0 DataObject in the registry
Microsoft Form 2.0 DataObject in the registry

To Copy any text to the clipboard we need to use the methods SetText and PutInClipboard. Copying text data to the clipboard requires two steps. The first step is to put the text in a DataObject variable, and then copy the entire DataObject to the clipboard. A DataObject can contain many different clipboard formats. Internally the text format is known as CF_TEXT and has the numeric value 1. If you would like to copy a Bitmap or a Metafile to the clipboard it is much more complicated, and it will not be covered in this article.

To Paste existing text from the clipboard, we need to call GetFromClipboard and GetText. First we need to retrieve a reference to the DataObject, and then we need to get the text. When pasting data from the clipboard, the Microsoft Forms DataObject works as a wrapper object. It is not the actual object stored on the clipboard. Instead it calls the methods of the OLE IDataObject interface of the clipboard object.

Code

The complete code for Copy and Paste in Excel can be found below:

' Copy Text To Clipboard
Public Sub SetClipboardText(ByVal strText As String)
    ' Instance
    Dim objDataObject As Object

    ' Allocate instance of Microsoft Forms DataObject
    Set objDataObject = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

    ' Set the Properties
    With objDataObject
        ' Add text to the DataObject
        .SetText strText
        ' Copy all data in the DataObject to the Clipboard (internally calling OleSetClipboard)
        .PutInClipboard
    End With

    ' Free memory
    Set objDataObject = Nothing
End Sub

' Paste From Clipboard
Public Sub GetClipboardText() As String
    ' Instance
    Dim objDataObject As Object

    ' Allocate instance of Microsoft Forms DataObject
    Set objDataObject = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

    ' Set the Properties
    With objDataObject
        ' Get the DataObject from the clipboard (internally calling OleGetClipboard)
        .GetFromClipboard
        ' Get the text and set the return value of the function
        GetClipboardText = .GetText
    End With

    ' Free memory
    Set objDataObject = Nothing
End Sub
    

Usage

The Copy and Paste methods can be called the following way:

' Example text
Dim strText As String

' Set the text
strText = "This is a test"

' Copy the text to the clipboard
Call SetClipboardText(strText)

' Get the text from the clipboard
strText = GetClipboardText()

Related

Ulf Emsoy has long working experience in project management, software development and supply chain management.

Leave a Reply

Your email address will not be published. Required fields are marked *

Back To Top