Using the clipboard

Can someone explin how to use the windows clipboard in VBA.

I've seen mention of various methods in this discussion group but I seem to be missing a reference to some Object library, as the simplest program falls over immediately?

I'd like to be able to select objects, do some changes on them, and then copy them to the clipboard, from which they could then be inserted into multiple seperate drawings using Paste or some other method.

 

haven't looked into this but heres something i saved awhile back in case i
ever needed it
hope it helps
Mark

'quote
Pearson Software Consulting, LLC
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.

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.

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








Created By Chip Pearson and Pearson Software Consulting, LLC
This Page: http://www.cpearson.com/excel/clipboar.htm
Updated: January 27, 2003
MAIN PAGE About This Site Consulting Downloads
Page Index Search Topic Index What's New Links

© Copyright 1997-2003 Charles H. Pearson

to be missing a reference to some Object library, as the simplest program
falls over immediately?

copy them to the clipboard, from which they could then be inserted into
multiple seperate drawings using Paste or some other method.

 

Thanks for the information, but unfortunately I don't have Excel on my system to reference the Forms 2.0 library.

 

The DataObject belongs to the MS Forms library. But it only handles text.

--
----
Ed
----

system to reference the Forms 2.0 library.

 

Use Windows API command

Copy the below code to a mod. You can then use ClipBoard_SetText() and
ClipBoard_GetText() anywhere in the project.

'********** BEGIN WINDOWS CLIPBOARD MODULE **********
'********** BEGIN WINDOWS CLIPBOARD DECLARATIONS **********
Const CF_TEXT = 1
Const GMEM_MOVEABLE = &H2
Const GMEM_ZEROINIT = &H40
Const GHND = &H42

Private Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long,
ByVal dwBytes As Long) As Long
Private Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As
Long
Private Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) As
Long
Private Declare Function GlobalSize Lib "kernel32" (ByVal hMem As Long) As
Long
Private Declare Function OpenClipboard Lib "USER32" (ByVal hWnd As Long) As
Long
Private Declare Function CloseClipboard Lib "USER32" () As Long
Private Declare Function EmptyClipboard Lib "USER32" () As Long
Private Declare Function SetClipboardData Lib "USER32" (ByVal wFormat As
Long, ByVal hMem As Long) As Long
Private Declare Function GetClipboardData Lib "USER32" (ByVal wFormat As
Long) As Long
Private Declare Sub CopyMemoryLS Lib "Kernel32.dll" Alias "RtlMoveMemory"
(ByVal dest As Any, ByVal src As Any, ByVal n As Long)

'********** END WINDOWS CLIPBOARD DECLARATIONS **********


Public Function ClipBoard_SetText(strCopyString As String) As Boolean
'used for ClipBoard button
Dim hGlobalMemory As Long, lpGlobalMemory As Long, hClipMemory As Long

' Allocate moveable global memory
hGlobalMemory = GlobalAlloc(GHND, Len(strCopyString) + 1)
' Lock block to get far pointer to this memory
lpGlobalMemory = GlobalLock(hGlobalMemory)
' Copy the string to global memory
CopyMemoryLS lpGlobalMemory, strCopyString, Len(strCopyString)
' Unlock memory then copy to clipboard
If GlobalUnlock(hGlobalMemory) = 0 Then
If OpenClipboard(0&) <> 0 Then
'If OpenClipboard(Screen.ActiveForm.Hwnd) <> 0 Then 'can't use when
debugging
Call EmptyClipboard
hClipMemory = SetClipboardData(CF_TEXT, hGlobalMemory)
ClipBoard_SetText = CBool(CloseClipboard)
End If
End If
End Function

Public Function ClipBoard_GetText() As String
Dim hClipMemory As Long, lpClipMemory As Long, strCBText As String
Dim RetVal As Long, lngSize As Long

If OpenClipboard(0&) <> 0 Then
'If OpenClipboard(Screen.ActiveForm.Hwnd) <> 0 Then 'can't use when
debugging
'Get handle to global memory block that is referencing the text
hClipMemory = GetClipboardData(CF_TEXT)
If hClipMemory <> 0 Then
'Lock Clipboard memory so we can reference the actual data string
lpClipMemory = GlobalLock(hClipMemory)
If lpClipMemory <> 0 Then
lngSize = GlobalSize(lpClipMemory) 'size of string
in ClipBoard
strCBText = Space$(lngSize) 'make VBA string
to hold ClipBoard data
CopyMemoryLS strCBText, lpClipMemory, lngSize 'copy from
clipboard to our string
RetVal = GlobalUnlock(hClipMemory) 'unlock the
memory
'Remove the null terminating character
strCBText = Left(strCBText, InStr(1, strCBText, Chr$(0), 0) - 1)
Else
MsgBox "Could not lock memory to copy string from."
End If
End If
Call CloseClipboard 'close the clipboard
End If
ClipBoard_GetText = strCBText
End Function
'********** END WINDOWS CLIPBOARD MODULE **********

 

The Clipboard was not intended to be used by
programmers for transferring data. When you
develop solutions that need to transfer data,
you should use your own 'Private' medum and
not the clipboard.

Why? In a nutshell, the clipboard belongs to
the user. When they copy something to it,
they should be able to depend on it being there
when they go to paste.

For what you describe, it should be fairly easy
to save the objects you want to copy to the
clipboard, to a .DWG file (using ObjectDBX),
and then use ObjectDBX again, to copy them
into other drawings.

After all, what do you think AutoCAD does
when you copy objects to the clipboard (it
wblocks them to a .DWG file in the temp
folder)?

 

Tony, I've used ObjectDBX to bring objects into one drawing from another, but not to create what you are suggesting, a temp file in the manner of creating a WBlock.

Could you give an example of this?

 

You use the CopyObjects method to copy the objects
you want to 'wblock' to a new, AxDbDocument, then
just Save it. There's already lots of examples of using
CopyObjects around, and that should show you how to
go about it.

 

Tony, thanks for the advice.

 

Just wanted to thank you. I also don't have the MS Forms 2.0 library and was unable to use .PutInClibboard. I'm a beginner VBA programmer and want to send an outlook email from within MS Access - way over my head! I was able to cut and paste your code into my project, and now I can put the body of the text into the clipboard - ready to paste into an email. Now all I have to do is understand it ;-)

 

Acess has built-in commands for emailing database objects (File>Send) using
the default email client. Here's a quickie function I wrote.

Public Function SendEmail(Optional sTo As String, _
Optional sCC As String, Optional sBCC As String, _
Optional sSubject As String, Optional sMessage As String)
DoCmd.SendObject acSendNoObject, , , sTo, sCC, sBCC, sSubject, sMessage,
vbTrue
End Function

--
----
Ed
----

was unable to use .PutInClibboard. I'm a beginner VBA programmer and want to
send an outlook email from within MS Access - way over my head! I was able
to cut and paste your code into my project, and now I can put the body of
the text into the clipboard - ready to paste into an email. Now all I have
to do is understand it ;-)