How can I copy an array to clipboard in excel vba

Question

I'm currently looking for a methode to copy a 1D array to clipboard by using excel vba. So far, I already fail by copying only one string to the clipboard. What I did is:

First: I included the "Microsoft Forms 2.0 Object Library" to my project

Second: I tried to copy the strText string to the clipboard by using the following code.

Sub copyToClipboard()
   Dim objData As New MSForms.DataObject       
   strText= "1"
   objData.SetText strText
   objData.PutInClipboard

   objData.GetFromClipboard
   resultString = objData.GetText
End Sub

If I paste the clipboard to a text Editor I get some funny symbols. If I check the content of resultString, I get "??".

EDIT: As mentionded below, I only get the "??" if I debug the code. A normal run is giving the expected output. Here is an image of the output window

The second output line is while debuging the code, the first one is a normal run.

Sorry I forget to copy one line. Dim objData As New MSForms.DataObject — Stefan
– Stefan, Commented May 16, 2017 at 5:55

gizlmo · Accepted Answer · 2017-05-15 13:11:20Z

5

I wasn't able to find "Microsoft Forms 2.0 Object Library", but it's also possible to create a UserForm inside your Workbook (The UserForm can be ignored then). Then use this code:

Dim objData As New MSForms.DataObject

'Load Test to Clipboard
objData.SetText "TEST"
objData.PutInClipboard

'Get Text from Clipboard
objData.GetFromClipboard
Debug.Print objData.GetText

Then, to load an Array to the Clipboard, use something like this:

Dim objData As New MSForms.DataObject
'Join combines all Array items to one string with the specified delimiter
objData.SetText Join(ARRAY, ",") 'ARRAY is your Array-Variable, "," is your delimiter
objData.PutInClipboard

answered May 15, 2017 at 13:11

gizlmo

1,9221 gold badge16 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Stefan Over a year ago

Thanks for the help, but unfortunatly your code gave the same output as my code befor. Output window shows "??" :-/ To make it more curio, I figured out, that the code only print "??" if i run it in debug mode. In normal mode I get the "TEST". Why?

gizlmo Over a year ago

@Stefan this is really weird

FunThomas · Accepted Answer · 2017-05-16 07:20:10Z

0

Maybe you run into what is described here DataObject .setText and .putInClipboard sequence puts invalid data (Hex 63 characters) in clipboard

answered May 16, 2017 at 7:20

FunThomas

31.3k4 gold badges24 silver badges40 bronze badges

Collectives™ on Stack Overflow

How can I copy an array to clipboard in excel vba

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related