clipboard

[next]

Is there a way to put a var content onto clipboard without haveing to do ctrl+c?
Here is what i mean:
clipboard = myVar

[dominicb]

Good evening next

The clipboard is a Windows object, not an Office object,but there is provision within VBA to access it through the dataobject. To use this make sure you declare a reference to the Microsoft Forms 2.0 Object Library (from the VBE go to Tools > References, find it in the list and tick it). This reference will then be saved with your file, then use this code :

Sub test()
Dim MyClipObj As New DataObject
myvar = "next"
MyClipObj.SetText myvar
MyClipObj.PutInClipboard
End Sub

HTH

DominicB

[next]

Thanks!

[WardXmodem]

Hi, didn't want to start new thread...
but any time I do:
sub something()
then
dim somename as [new] DataObject
i.e. either with or without the "new",
I get an error about user-defined type not defined.

Many web sites start subroutines with def blah as dataobject
Yet in my Excel just making a sub with such a def, and pressing F8 to execute line by line, I get that error!

I must be missing something very fundamental!

I've written thousands of lines of VBA, but probably know only 2% of the language;

I want to put the current cell's row and column into the clipboard, so I can paste it into a macro elsewhere:
[vba]
Sub grab_rc()
' *****
Dim where As DataObject
Dim RC As String
RC = Selection.Row & "," & Selection.Column

where.SetText RC, 1
where.PutInClipboard
End Sub
[/vba]
I quickly die with the user defined variable problem.

I don't want to "hack" by putting it in a cell and copying it; I should be able to "put stuff in the clipboard from vba directly", but just am missing a major piece.

Thanks!

Ward Christensen - inventor of "Xmodem" and "BBSs"

[Bob Phillips]

Hello Ward,

It is a pleasure to have you visit!

Wen you refer to a specific datatype, you must have a reference to the appropriate type library where that datatype is defined. In most cases, this will be so because you will be using Excel datatypes when coding in Excel, and there is a default reference.

Many others, such as commandbars, will need a reference to the Office library, and again this is usually laoded by default (although I have seen instances of workboosk without it).

Yet others are in libraries that are not loaded by default, such as the scripting objects, which have to be specifically loaded if you want to refer to those datatypes explicitly.

In many instances, you can use late binding to bypass that reference, use a generic object type.

All of this is a long winded way of saying that you seem to be missing a refernce to the library that contains the DataObject datatype. This library is the Forms library, the library for all things userform.

To restore this library, in the VBIDE, goto Tools>references and scroll don to the item named Microsoft Forms .0 Object Library and check that, the code should work then.

[WardXmodem]

[blush] I see a previous post mentioning the forms library.

I did so, my code now gets past the DataObject.

Now, the 'where.SetText RC,1' fails with "Object variable or With block variable not set"

Going to try the other example (and read up on what 'Set' really does).

AHA! Turns out I have to say "new" DataObject

Thank you SO MUCH! I've only got about 99.5% of VBA to learn now!

But I have 2000 lines of code working! reading HTML and converting to spreadsheets.

[Bob Phillips]

Thank you SO MUCH! I've only got about 99.5% of VBA to learn now!

But I have 2000 lines of code working! reading HTML and converting to spreadsheets.

Well, you won't get a better site than this to help you along with VBA.