Show Buttons
Share On Facebook
Share On Twitter
Share On Google Plus
Share On Linkdin
Share On Reddit
Contact us
Hide Buttons

VBA-Excel: Putting Text In The Windows Clipboard

For work­ing with Win­dows Clip­board you need DataOb­ject, the object in MSForms library. It pro­vides sup­port for text-string.
For that you must add the ref­er­ence “Microsoft Forms 2.0 Object Library”
How to add “Microsoft Forms 2.0 Object Library”
Now, For putting text in the Clip­board, Fol­low the below steps.
Steps:
•    Ini­tial­ize the Data Object, the type of MSForms.DataObject
•    Cre­ate a String
•    Set the String into Data Object using Set­Text() method.
•    Put the data in Clip­Board using PutIn­Clip­board

Ini­tial­ize the Data Object, the type of MSForms.DataObject
Dim obj­Data As New MSForms.DataObject
Cre­ate a String
str­Text = “I will be in Clip­Board“
Set the String into Data Object using Set­Text() method.
objData.SetText str­Text
Put the data in Clip­Board using PutIn­Clip­board
obj­Data.PutIn­Clip­board

Com­plete Code:

Function FnPutDataInClipBoard()

    Dim objData As New MSForms.DataObject
    Dim strText

    strText = "I will be in ClipBoard"
    objData.SetText strText
    objData.PutInClipboard

End Function 

NOTE: Now if you open the Notepad and press Ctrl+V, you can see that string you had put in, gets pasted in the Notepad

Read about:

Get Text from the Win­dows Clipboard

Stor­ing mul­ti­ple data In the Win­dows Clipboard

 

You may also like...

9 Responses

  1. Nick Warren says:

    Thanks for post­ing this, it’s the only solu­tion I’ve found that actu­ally works. I included and acti­vated the cell I wanted to copy from above the code and changed the item in “” to Active­cell and bingo.

  2. John Frim says:

    Sumit,

    The macro stuff above is almost per­fect for my needs. The only twist is that I want the text that is going to the clip­board to be taken from the Active­Cell on a work­sheet — but not the entire cell con­tent, rather just what I have high­lighted in the cell. If I do Command-C to man­u­ally copy to the clip­board I get what I want and can paste from the clip­board. I would like to just click on a cell, drag over the text snip­pet that I want to copy, and then click a but­ton to put that text into the clip­board. Thanks for any assistance.

    J

  3. Unfor­tu­nately, there is a bug in Win­dows 8.1 that stops this from work­ing. You only ever end up with “??” in the clipboard.

    • SJ says:

      Hello Julian, Checked on win­dows 8.1, it is work­ing fine. Make sure the ref­er­ence How to add “Microsoft Forms 2.0 Object Library” is added. Arti­cle has a link how to add it as well.

  4. I’ve seen the same prob­lem reported by Julian whereby “??” is returned from the clip­board when using Ctrl+V even if the .Get­Text method returns what was put in the clip­board. This used to work in code I wrote sev­eral years ago and I just noticed it fail­ing in this way on Win­dows 10 x64 + MSO 2016 x32. It seems that Microsoft may have bro­ken some­thing as the project ref­er­ence for MSForms 2.0 is present and cor­rect and the iden­ti­cal project code works on Win­dows 7.

  5. BB says:

    Have the same issue with Excel 2013 and Win­dows 10. Just get­ting gib­ber­ish in the clip board.
    Made sure that I have “Microsoft Forms 2.0 Object Library” added in the references.

  1. April 2, 2015

    […] document.write(”); My mis­take. To use the clip­board you need to code dif­fer­ntly. See Win­dows Clip­board VBA-Excel: Putting Text In The Win­dows Clipboard […]

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: