View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
GS[_6_] GS[_6_] is offline
external usenet poster
 
Posts: 1,182
Default Getting a variable's contents onto the clipboard

Terry,
Here's what I've been using since day1 programming; - it's a great tutorial
IMO!

Working With The Windows Clipboard


This page describes various methods in Visual Basic For Applications (VBA) for
copying data to and retrieving data from the Windows clipboard. In VBA, you are
restricted to setting and retrieving only text data.

To copy data directly from a worksheet cell to the Windows clipboard, you can
use the COPY method of the Range object, e.g., Range("A1").Copy. However,
copying other data to the clipboard, such as variable, cell comments, sheet
names, etc, is not as simple as it might be.

VBA does not give you generic PutOnClipboard or GetOffClipboard procedures, so
we'll create them here. Along the way, we'll look at how VBA does interact with
the Windows clipboard.

Because these procedures use the DataObject variable type, you must have a
reference set in your VBA project to the Microsoft Forms 2.0 object library
(FM20.DLL).


[Copying To The Clipboard]

To access the Windows Clipboard from VBA, you must go through an intermediate
object of the DataObject type. If your VBA procedure will be working with the
clipboard, declare a NEW DataObject object with the following statement.

Dim MyDataObj As New DataObject

The SetText method of the DataObject variable is used to store a text string
or numeric value in the variable For example:

MyDataObj.SetText "This Is A Text String" Or
MyDataObj.SetText 123.456

This sets the contents of MyDataObj to a value. To copy the contents of the
variable MyDataObj to the Windows clipboard, use the PutInClipboard method .

MyDataObj.PutInClipboard


[Pasting From The Clipboard]

To retrieve the contents of the clipboard, use the following statement:

MyDataObj.GetFromClipboard

This sets the contents of MyDataObj to the contents of the Windows clipboard.

The counterpart to the SetText method is the GetText method. This method
returns the contents of DataObject to another variable. For example,

Dim MyVar As Variant
MyVar = MyDataObj.GetText


Using this knowledge, we can create the following VBA procedures:

Public Sub PutOnClipboard(Obj As Variant)
Dim MyDataObj As New DataObject
MyDataObj.SetText Format(Obj)
MyDataObj.PutInClipboard
End Sub


Public Function GetOffClipboard() As Variant
Dim MyDataObj As New DataObject
MyDataObj.GetFromClipboard
GetOffClipboard = MyDataObj.GetText()
End Function


Public Sub ClearClipboard()
Dim MyDataObj As New DataObject
MyDataObj.SetText ""
MyDataObj.PutInClipboard
End Sub

I use these formulas quite often to place the formula of the active cell on to
the clipboard, to allow cut and paste operations without Excel changing any
cell references. You may find it useful to link them to command items on your
right click menu.

Sub CopyFormula()
Dim x As New DataObject
x.SetText ActiveCell.Formula
x.PutInClipboard
End Sub

Sub PasteFormula()
On Error Resume Next
Dim x As New DataObject
x.GetFromClipboard
ActiveCell.Formula = x.GetText
End Sub

Clearing The Clipboard

To completely clear the clipboard, you need to use a few API calls:

Declare Function CloseClipboard Lib "user32" () As Long
Declare Function EmptyClipboard Lib "user32" () As Long
Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long

Sub ClearClipboard()
OpenClipboard 0&
EmptyClipboard
CloseClipboard
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion