Thanks Peter but the idea is to use the clipboard so I can retreive the data
from another application like Word. Your method doesn't use the clipboard at
all.


"Peter T" <peter_t@discussions wrote in message
...
Hello Fred,

If you want to use what you put into the clipboard in different routines,
you need to declare myDataObj at module or public level, not destroy it in
myCopy() and don't declare again at proc level in myPaste():

Public myDataObj As DataObject
Sub myCopy()

Set myDataObj = New DataObject
myDataObj.SetText Selection.Cells(1).Value, "myFormat"
myDataObj.PutInClipboard

End Sub

Sub myPaste()
Dim myStr As String

If Not myDataObj Is Nothing Then

myDataObj.GetFromClipboard
On Error Resume Next
myStr = myDataObj.GetText("myFormat")
If Err.Number Then
myStr = "probably someone's used the clipboard "
End If
On Error GoTo 0
MsgBox myStr
Set myDataObj = Nothing ' not needed again ?
Else
MsgBox "myDataObj is nothing"
End If


At some stage you should destroy myDataObj, even if it's only in your
closing routine

Regards,
Peter T

"Fred" <leavemealone@home wrote in message
...
I want to put some data on the clipboard and retreive it but using a
custom
format.
I tried the code below. I run myCopy and it works fine but myPaste fails
when trying to retreive the data (myStr = myDataObj.GetText("myFormat"))
with the error message:
Dataobject: Gettext invalid FORMATETC structure.

Can someone help me make this work. I want to use a custom format because
I
also want to keep some standard text data in the clipboard.

Thanks
Fred


Sub myCopy()
Dim myDataObj As DataObject

Set myDataObj = New DataObject
myDataObj.SetText Selection.Cells(1).Value, "myFormat"
myDataObj.PutInClipboard
Set myDataObj = Nothing
End Sub

Sub myPaste()
Dim myStr As String
Dim myDataObj As DataObject

Set myDataObj = New DataObject
myDataObj.GetFromClipboard
myStr = myDataObj.GetText("myFormat")
MsgBox myStr
Set myDataObj = Nothing
End Sub