5.3 Data Transfer via the Clipboard

Copying, Cutting, and Pasting Ranges of Cells

If you wish to relocate or copy a range of cells, your best bet is to use the clipboard, just as in using Excel manually. The following methods are designed for data transfer to and from the clipboard.

Copy : This method copies the range of cells specified as object to the clipboard. If the method is used with an optional parameter, then the data are not copied to the clipboard, but directly to the given range.

Cut : This method functions like Copy , but the original data are deleted. If a range is given with the optional parameter "destination," then the cells will be shifted to that location. For this reason there is no method specifically for shifting cells.

Paste : This method inserts data from the clipboard. A worksheet must be given as object. If the destination is not given in an optional parameter, then the current selection in the worksheet will be the destination.

PasteSpecial : This method enables more complex editing with the command EDITPASTE SPECIAL, such as the insertion of values (instead of formulas) or carrying out calculations. This method recognizes numerous optional parameters, which are described in the on-line help. In particular, with the help of these parameters you can shift to the right or below those cells that were overwritten by the insertion.

Two properties of the object Application give additional information about the current contents of the clipboard and the current copy or cut mode:

CutCopyMode : This property tells whether Excel is currently in copy or cut mode. Possible values are False , xlCut , and xlCopy . With a specification of False an operation of cutting or copying that has already begun can be interrupted . With this the blinking frame around the copied or cut data disappears.

ClipboardFormats : This enumeration property tells which formats are exhibited by the data in the clipboard. This property is organized as a field, since the clipboard can contain data in several formats simultaneously . Possible formats are xlClipboardFormatText and xlClipboardFormatBitmap (see the on-line help).

Tip

Starting with Office 2000, Excel, Word, and the like possess not merely one clipboard, but twelve. In other words, the last twelve cut or copied data are in temporary storage and can be restored as needed. For this you need to make the toolbar "Clipboard" visible.

However, this new feature is not accessible to VBA programmers. The commands described in this section are valid only for the last piece of data added to the clipboard. The up to eleven remaining clipboard items cannot be accessed by code.

Copying a Range of Cells into Another Sheet

The following instructions copy the data of the current region in which the cell pointer is located from table 1 to table 2. With SpecialCells(xlVisible) only visible data are copied. This restriction makes sense, for example, in database applications in which only the filtered data are to be transferred. If you simply wish to transfer the selected data, then the instruction Selection.Copy suffices.

Note that when Paste is invoked, although the active sheet is specified as object, the data beginning with cell A1 are copied into table 2.

 ' copy visible data to the clipboard Selection.CurrentRegion.SpecialCells(xlVisible).Copy ' insert data beginning with A1 into table 2 ActiveSheet.Paste Range("Table2!A1") ' cancel copy mode (blinking border) Application.CutCopyMode = False

Linking and Inserting Data

Depending on the origin of the data, it is possible in inserting data from the clipboard to create a link to the program from which the data originate. Then when the original program is changed, the data will also be updated in Excel.

Data linking is used most frequently within Excel, namely, when data from one file are needed in another. In using Excel in manual mode you copy data in the first file and then paste it into the second file with EDITPASTE SPECIALPASTE LINK.

For this action in program code you do not use the method PasteSpecial , but the method Paste introduced in the previous example. However, you must now employ the optional parameter Link:=True . Moreover, the destination must coincide with the active selection. In the example above, therefore, table 2 must be activated before the insertion and the cell pointer inserted into A1.

 ' copy visible data to the clipboard Selection.CurrentRegion.SpecialCells(xlVisible).Copy ' insert and link data beginning with A1 into table 2 Worksheets("table2").Select Range("A1").Select ActiveSheet.Paste Link:=True Worksheets("table1").Select ' cancel copy mode (blinking border) Application.CutCopyMode = False

Access to the Clipboard with the DataObject

The MS Forms library offers a DataObject that can be used to write text to the clipboard and read text from it. (If your Excel application has no user -defined forms, insert a new form to activate the library. You may delete the unused form, and the library stays active.)

The DataObject is an object independent of the clipboard, which can be declared in program code as follows :

 Dim dataobj As New DataObject

You can then copy the contents of the clipboard into this object with the method GetFromClipboard . Conversely, you can use PutInClipboard to transfer the contents of dataobj to the clipboard. To read a character string from the clipboard, the following two commands are necessary:

 Dim cliptext$ dataobj.GetFromClipboard cliptext = dataobj.GetText()

The other direction, that is, copying a text to the clipboard, goes as follows:

 dataobj.SetText "abc" dataobj.PutInClipboard

If you wish to delete the contents of the clipboard, you should execute the following two commands:

 dataobj.Clear dataobj.PutInClipboard

Note	As an example of programming an ActiveX library that can be used from within Excel, a program will be introduced in Chapter 14 that makes the Clipboard object of the programming language Visual Basic usable in Excel as well.

Syntax Summary

COPYING/CUTTING/INSERTING RANGES OF CELLS
range.Copy	copy a range to the clipboard
range1.Copy range2	copy data from range1 to range2
range.Cut	as with copy, but range is deleted
range1.Cut range2	shift data from range1 to range2
wsheet.Paste	inserts data into a worksheet
wsheet.Paste Link:=True	as above, but with a link
wsheet.Paste range	inserts data into the given range
wsheet.PasteSpecial format	inserts data in the specified format
Application.CutCopyMode	gives the current mode
Application.ClipboardFormats(n)	contains information about data in the clipboard

MSFORMS.DATAOBJECT ”METHODS
Clear	deletes content of object
GetFromClipboard	reads content of object from the clipboard
PutInClipboard	transfer the contents of the object to the clipboard
GetFormat	determines data format (like ClipboardFormats )
GetText	read text from object
SetText	store text in object