PDA

View Full Version : Solved: Copy string to clipboard in VBA



manderson
08-11-2009, 09:45 AM
Is it possible to copy a string directly to the clipboard in an Excel VBA macro?
For example, I can copy the contents of a cell using a statement like:
Cells([Row],[Col]).Copy
but I have generated a string by concatenating the contents of several cells:

myString = ""
For Each myCell In mySelection
myString = myString + CStr(myCell.Value) + ","
Next
and I want to copy that directly to the clipboard without inserting it in a cell first. I want to be able to past the result direcly into a text editor without going back into Excel, and the source spreadsheet should remain unmodified.

Any help would be much appreciated.

hardlife
08-11-2009, 10:23 AM
Hi, try this:


Sub ToTheClipboard()
Dim MyDataObj As New DataObject
MyDataObj.SetText "This Is A Text String"
'MyDataObj.SetText 123.456
MyDataObj.PutInClipboard
End Sub

Sub PastingFromTheClipboard()
Dim MyDataObj As New DataObject
MyDataObj.GetFromClipboard

Dim MyVar As Variant
MyVar = MyDataObj.GetText
MsgBox MyVar
End Sub


HTH

Pavel Humenuk

Marcster
08-11-2009, 11:25 AM
You must set a reference to the 'Microsoft Forms 2.0 Object Library' in the vbe in order for this to work. Or create a new userform. Or you'll get the error: User-defined type not defined.

manderson
08-12-2009, 12:16 AM
Thanks. The DataObject object is exactly what I have been looking for. Much appreciated.