Data Transfer Via the Clipboard

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 EDIT|PASTE 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 EDIT|PASTE SPECIAL|PASTE 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