VBA-Excel: Storing multiple data In the Windows Clipboard

In earlier sessions  you saw how to put text in the windows clipboard (Putting Text In The Windows Clipboard) with the help of PutInClipboard() and Data Object and how to get the text from the Clipboard using GetFromClipboard() and Data Object.

In this session you will learn how to store multiple data’s in Clipboard and retrieve them. For storing multiple data’s, you need to store values as pair, Keys and Values or you can say format identifier and while retrieving the data from the Clipboard you will use these format identifiers to get the data. This data can be String, Long, Integer.

For storing multiple data in Clipboard, Follow the below steps.

Steps:

•    Initialize the Data Object, the type of MSForms.DataObject
•    Initialize a String and an Integer.
•    Set the String into Data Object using SetText() method and provide format identifier
•    Put the data in ClipBoard using PutInClipboard
•    Fetch the data from ClipBoard using GetFromClipboard(“format identifier”)

Initialize the Data Object, the type of MSForms.DataObject
Dim objData As New MSForms.DataObject
Initialize a String and an Integer.
strText = "I am the First One"
intData = 5
Set the String into Data Object using SetText() method and provide format identifier
objData.SetText strText, "StringOne"

Put the data in ClipBoard using PutInClipboard
objData.PutInClipboard

Fetch the data from ClipBoard using GetFromClipboard(“format identifier”)
objData.GetText("StringOne")

Complete Code:

Function FnStoreMultipleDataInClipBoard()

   Dim objData As New MSForms.DataObject

   Dim strText

   Dim intData

   strText = "I am the First One"

   intData = 5

   objData.SetText strText, "StringOne"

   objData.PutInClipboard

   objData.SetText intData, "IntegerOne"

   objData.PutInClipboard

   MsgBox "Data in ClipBoard : '" & objData.GetText("StringOne") & "' And '" &   objData.GetText("IntegerOne") & "'"

End Function 

  12.00

Note:  For work­ing with Win­dows Clip­board you need DataOb­ject, the object in MSForms library. It pro­vides sup­port for text-string.
For that you must add the ref­er­ence “Microsoft Forms 2.0 Object Library”
How to add “Microsoft Forms 2.0 Object Library”