error trying to use GetFromClipboard

[John Keith]

I want to use the followiong method to take text data from the
clipboard and insert it into a cell. I found the following code on
this website (authored by cpearson):

http://www.officekb.com/Uwe/Forum.aspx/excel-prog/153040/clipboard-excel

' Declarations -- above and outside of any procedure
Public Declare Function EnumClipboardFormats Lib "user32" ( _
ByVal wFormat As Long) As Long
Public Declare Function OpenClipboard Lib "user32" ( _
ByVal hwnd As Long) As Long
Public Declare Function CloseClipboard Lib "user32" () As Long
Public Declare Function GetClipboardFormatName Lib "user32" _
Alias "GetClipboardFormatNameA" ( _
ByVal wFormat As Long, ByVal lpString As String, _
ByVal nMaxCount As Long) As Long
Public Declare Function IsClipboardFormatAvailable Lib "user32" ( _
ByVal wFormat As Long) As Long
Public Declare Function EmptyClipboard Lib "user32" () As Long

Sub TestForText()
Dim R As Range
Dim DataObj As MSForms.DataObject
Dim L As Long
Const CF_TEXT As Long = 1&
Set R = Range("A1")
Set DataObj = New MSForms.DataObject
L = IsClipboardFormatAvailable(CF_TEXT)
If L <> 0 Then
' text is available
DataObj.GetFromClipboard
R.Value = DataObj.GetText
Else
Debug.Print "text not available"
End If
End Sub

I pasted the above code into a module but when I run it I get this
error message:

User-defined type not defined on the Dim DataObj As MSForms.DataObject
statement

I'm not sure I handled the declarations correct, "above and outside of
any procedure", the code posted hee is exactly what I copied from the
module.

Any suggestions on how I can get the text pulled from the clipboard?

Thanks


John Keith
(e-mail address removed)

 

[Dave Peterson]

Take a look at the notes on Chip Pearson's site:
http://www.cpearson.com/Excel/clipboard.aspx

Especially, the first paragraph where he describes how to set a reference inside
the VBE.

 

[John Keith]

Take a look at the notes on Chip Pearson's site:
http://www.cpearson.com/Excel/clipboard.aspx

Dave,

Thank you for the pointer, but I'm not quite there yet.

I went to the tools menu on the VB editor, selected references and
found a multitude of items that could be selected but no MS Forms 2.0
Object Library. I'll try a internet search but if you have some
suggestions please pass them on.


John Keith
(e-mail address removed)

 

[Peter T]

It should be there, are you looking for
"Microsoft Forms 2.0 Object Library"
it might be near the top of the list rather than in alphabetical order

Simple way to add the reference is to add a userform, which you can remove
immediately.

Keep in mind the DataObject only returns Text, if any, in the clipboard. If
copying from cells and depending on the numberformat that might be different
to the Value property.

FWIW, instead of the clipboard APIs. to check if text is available you can
do simply something like this

Sub test2()
Dim DataObj As MSForms.DataObject

Set DataObj = New MSForms.DataObject
DataObj.GetFromClipboard

If DataObj.GetFormat(1&) Then
Debug.Print DataObj.GetText
Else
Debug.Print "text not available"
End If

End Sub

Regards,
Peter T

 

[Akihito Yamashiro]

To use MSFORMS.DATAOBJECT,
You should insert a user-form , once you have inserted a user-form,
you may remove it.
Creating a user-form causes making a referrence to MSFORMS Library.

Or you can use MSFORMS.DATAOBJECT without refferencing the library.
See my site : http://akihitoyamashiro.com/en/VBA/LateBindingDataObject.htm
about that.