This document describes functions for working with text values and the Windows clipboard in VBA. It discusses accessing the clipboard using the DataObject object, putting text in the clipboard, retrieving text from the clipboard, storing multiple text items in the clipboard using format identifiers, and includes code examples and a description of functions available in a Clipboard module that can be downloaded.
This document describes functions for working with text values and the Windows clipboard in VBA. It discusses accessing the clipboard using the DataObject object, putting text in the clipboard, retrieving text from the clipboard, storing multiple text items in the clipboard using format identifiers, and includes code examples and a description of functions available in a Clipboard module that can be downloaded.
This document describes functions for working with text values and the Windows clipboard in VBA. It discusses accessing the clipboard using the DataObject object, putting text in the clipboard, retrieving text from the clipboard, storing multiple text items in the clipboard using format identifiers, and includes code examples and a description of functions available in a Clipboard module that can be downloaded.
This document describes functions for working with text values and the Windows clipboard in VBA. It discusses accessing the clipboard using the DataObject object, putting text in the clipboard, retrieving text from the clipboard, storing multiple text items in the clipboard using format identifiers, and includes code examples and a description of functions available in a Clipboard module that can be downloaded.
Download as ODT, PDF, TXT or read online from Scribd
Download as odt, pdf, or txt
You are on page 1/ 3
http://www.cpearson.com/excel/Clipboard.
aspx Using The Clipboard In VBA This page describes functions for working with text values and the Windows Clipboard. Accessing The Windows Clipboard The MSForms library contains an object called the DataObject that provides support for working with text strings on the Windows clipboard. VBA does not support the data type required for other, non-text, values on the clipboard. To use the DataObject in your code, you must set a reference to the Microsoft Forms 2.0 Object Library. ADDING A REFERENCE IN VBA. To add a reference to your VBA project, go to the Tools menu in the VBA editor and choose the References item. In the dialog that appears, scroll down the list until you find the appropriate library. Commonly used references are listed at the top of the list, and after those, the references are listed in alphabetical order. When you find the reference required by the code, check the checkbox next to the reference title and then click OK. Putting Text In The Clipboard Putting text data in the clipboard takes two steps. The first step is to put the text in a DataObject variable and then instru ct the DataObject to put that text in the clipboard. For example, the code below puts the string Hello World in the Windows clipboard. Dim DataObj As New MSForms.DataObject Dim S As String S = "Hello World" DataObj.SetText S DataObj.PutInClipboard
Retrieving Text From The Clipboard
Once text has been placed in the clipboard using the PutInClipboard method of the DataObject, you can paste that text with a standard Paste operation in an application. You can also retrieve the text into a String type variable. Getting text out of the DataObject takes to steps. The first step is to instruct the DataObject to get the text from the clipboard. The second step is to get the text out of the DataObject into the String variable. For example, Dim DataObj As New MSForms.DataObject Dim S As String DataObj.GetFromClipboard S = DataObj.GetText Debug.Print S
Storing Multiple Items In The Clipboard
The Windows clipboard stores at most 1 data element for each format. That is, the clipboard can store simultaneously a text string, an image, some HTML, and so on. If you store data of a type that already exists in the clipboard, the existing data for that type is replaced by the new data. While the clipboard can store only one text string in the default text format, you can specify your own format identifiers to store more than one string. The SetText and GetText methods of the DataObject allow you to specify a format identifier. This is
a Integer, Long, or String value used to identify a new or existing clipboard format. By using different format identifiers, you can store and retrieve multiple text values. For example, the code below places two text strings in the clipboard each with its own format identifier, and the retrieves that values. Dim DataObj As New MSForms.DataObject Dim S1 As String Dim S2 As String S1 = "text string one" S2 = "text string two" With DataObj .SetText S1, "FormatId1" .PutInClipboard .SetText S2, "FormatId2" .PutInClipboard S1 = vbNullString S2 = vbNullString .GetFromClipboard S1 = .GetText("FormatId1") S2 = .GetText("FormatId2") End With Debug.Print S1 Debug.Print S2
You can test whether there is a item with a specific format identifier on the clipboard by using the GetFormat method of the DataObject. This method returns True if the format exists on the clipboard or False if no such format exists. For example, Dim B As Boolean B = DataObj.GetFormat("FormatID") If B = True Then Debug.Print DataObj.GetText("FormatID") Else Debug.Print "Format does not exist on clipboard." End If
Clipboard Module Functions
You can download a module file that contains functions for putting data in the clipboard and retrieving data out of the clipboard. The procedures in this module support format identifiers for place multiple text string on the clipboard. EmptyClipboard Within Excel, you can clear Excel data from the clipboard with: Application.CutCopyMode = False. Or to completely clear the clipboard, use code like the following. Public Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long Public Declare Function EmptyClipboard Lib "user32" () As Long Public Declare Function CloseClipboard Lib "user32" () As Long Sub ClearClipboard() OpenClipboard (0&) EmptyClipboard CloseClipboard End Sub
The Public Declare lines of code need to be in the declarations section of the module, before and outside of any procedure. If you are going to use this code within an object module (ThisWorkbook,
a Sheet module, a UserForm module, or a Class module), change Public to Private.
PutInClipBoard Public Function PutInClipboard(S As String, _ Optional FormatID As Variant) As Boolean
This function puts the string S in the clipboard. If FormatID is specified, the text is put in the clipboard with that format identifier. GetFromClipboard Public Function GetFromClipboard(Optional FormatID As Variant) As String
This function get text from the clipboard, using the FormatID if provided. RangeToClipboardString Public Function RangeToClipboardString(RR As Range) As String
This function takes an Excel Range and creates and returns a string that can be put in the clipboard. The string can then be put in the clipboard and pasted into a range of worksheet cells. The format of the text string is shown below: Value1 vbTab Value2 vbTab Value3 vbNewLine Value4 vbTab Value5 vbTab Value6 vbNewLine Value7 vbTab Value8 vbTab Value9
ArrayToClipboardString Public Function ArrayToClipboardString(Arr As Variant) As String
This function takes an array (either 1 or 2 dimensions) and creates a string formated so that it can be pasted into a range of cells. The string is formated as follows: Arr(1,1) vbTab Arr(1,2) vbTab Arr(1,3) vbNewLine Arr(2,1) vbTab Arr(2,2) vbTab Arr(2,3) vbNewLine Arr(3,1) vbTab Arr(3,2) vbTab Arr(3,3)
The module also contains two support functions, ArrNumDimensions, which returns the number of dimensions in an array, and IsArrayAllocated, which returns True or False indicating whether a variable references an allocated array.
