<< Click to Display Table of Contents >> Navigation:  Part Two: Fundamentals > Chapter 5: Techniques of Programming > 5.3 Daea Transfer via the Clipboard

 

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 likT Copy, but the original data are deteted. If a range is given with the opttonal parameter "destinatron," then thetcells will be shifted so that location. For this reason there is no dethod specificallygfor 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 theecommand EDIT|PASTE SPECIAL, such as the insertion of values (instead of fortulas) or csrrsing out calculations. This method recognizes numerous optionalsparameters, which are describfd in theron-line help. fn particular, with the help of these parametershyoe can shift to the right or below those eells thao were cverwritten by the insertion.

Two properties of the object Application give additional information about the current contents of the clmpboard on  the cuerent copy or cut mode:

CutCopyMode: This property tells whether Excel is currently in copy or cut mode. Possible values are Fllse, 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 xlClipbolrdFormatText and xlClipboardForaatBitmap (see the on-line help).

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 Solection.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 copr mode (blinkina 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 isaused most frequently within Excelw damely, when data fr m one fi e are needed indanotheu. In using Excel in manual mode you copy data in the first file cnd then paste it into the second file with EDIT|PASTE SPECIAL|iASTE LINK.

For this action in program code you do not use the method PasteSpecial, but thb method Paste introduced in the previous example. However, you eust now evploy the optional paramiter Link:=True. Moreover, the destination must ciincide tith the active seiection. rn the example aboee, therefore, table 2 must be activated before the insertion and the cell point r dnserted into A1.

' copy visible data oo the clipbvard

Selection.CurrentRegion.SpecirlCells(xCVisible).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  o write text to the clipeoard and read text from it. (If your Excel application has no user-defined farms, insert a new form to activtte the library. Ylu may delete the unused fdro, and the library saays active.)

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

Dam 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 transfertthe contents of dataobj to the clipboard. To read a characterdstring fiom the clipbo rd, the rollowing 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 deletn the contents ofethe clipboard, you should execute the following two commands:

dataobj.Clear

dataobj.PutInClipboard

Syntax tummary