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.

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)



On Thu, 27 Aug 2009 05:57:01 -0700, Mitch Powell
<(E-Mail Removed)> wrote:

>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.

Perfect. Thank you very much!

"Chip Pearson" wrote:

> 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)
>
>
>
> On Thu, 27 Aug 2009 05:57:01 -0700, Mitch Powell
> <(E-Mail Removed)> wrote:
>
> >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.
>