Using a custom format on the clipboard

[Fred]

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

 

[Peter T]

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]

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]

Needing to set & get clipboard with "format" between different app's casts a
different light on your original question.

I'll stand corrected but it does not appear possible to retrieve data with a
custom format from a different reference to the DataObject. If so, maybe
something like this might work for you:

'' in the ThisWorkbook module of "MyBook.xls"

Public myDataObj As New DataObject

Public Property Let MyData(sFormat As String, sCopy As String)
myDataObj.SetText sCopy, sFormat
myDataObj.PutInClipboard
End Property

Public Property Get MyData(sFormat As String) As String
On Error GoTo errH
myDataObj.GetFromClipboard
MyData = myDataObj.GetText(sFormat)
Exit Property
errH:
MyData = "failed"
End Property

''in a normal module in "MyBook.xls"

Sub LetData()
Dim sFormat As String, myStr As String
myStr = "some test text"
sFormat = "myformat"

ThisWorkbook.MyData(sFormat) = myStr

End Sub

Sub GetData()
Dim sFormat As String, myStr As String
sFormat = "myformat"
myStr = ThisWorkbook.MyData(sFormat)
MsgBox myStr
End Sub

' in a normal module in a Word

Sub Geter()
Dim myStr As String, sFormat As String
Dim sXLbook As String
Dim oXL As Object
Dim oWB As Object

sXLbook = "MyBook.xls" ' change
sFormat = "myformat"

Set oXL = GetObject(, "Excel.Application")
Set oWB = oXL.workbooks(sXLbook)

myStr = oWB.mydata(sFormat)
MsgBox myStr

Set oWB = Nothing
Set oXL = Nothing

End Sub

I guess there's also an API method.

Regards,
Peter T

 

[DM Unseen]

Maybe check this out.

It is for VB but also should work in VBA

http://www.vbaccelerator.com/tips/vba0013.htm

DM Unseen