copy text to clipboard

[Mitch Powell]

I need to copy some text to the clipboard using vba so that is will be
available in another application. For various reasons, I cannot use the
native Excel Copy function in this case. I can find no way to do this.
Surely I am missing something obvious.

 

[Chip Pearson]

In VBA, go to the Tools menu, choose References, and put a check next
to "Microsoft Forms 2.0 Object Library" (or, just add a UserForm to
the project and then delete the UserForm). Then, use code like


Sub PutToClipboard(S As String)
Dim DataObj As New MSForms.DataObject
DataObj.SetText S
DataObj.PutInClipboard
End Sub

Function GetFromClipboard()
Dim DataObj As New MSForms.DataObject
DataObj.GetFromClipboard
GetFromClipboard = DataObj.GetText
End Function

You would then use these functions like

Sub AAA()
Dim S As String
PutToClipboard "hello"
S = GetFromClipboard
MsgBox S
End Sub

See http://www.cpearson.com/excel/Clipboard.aspx for more details.


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Mitch Powell]

Perfect. Thank you very much!

In VBA, go to the Tools menu, choose References, and put a check next
to "Microsoft Forms 2.0 Object Library" (or, just add a UserForm to
the project and then delete the UserForm). Then, use code like


Sub PutToClipboard(S As String)
Dim DataObj As New MSForms.DataObject
DataObj.SetText S
DataObj.PutInClipboard
End Sub

Function GetFromClipboard()
Dim DataObj As New MSForms.DataObject
DataObj.GetFromClipboard
GetFromClipboard = DataObj.GetText
End Function

You would then use these functions like

Sub AAA()
Dim S As String
PutToClipboard "hello"
S = GetFromClipboard
MsgBox S
End Sub

See http://www.cpearson.com/excel/Clipboard.aspx for more details.


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)