Paste some info on the Clipboard

Question

im trying to put some data in the clipboard with VBA

The code goes like:

Sub PasaraformatoSQLDATA()

Dim clipboard As MSForms.DataObject
Dim ArraySelc As Variant
Dim SourceRange As Range
Dim Lenr As Long
Dim ArrayString As Variant
Set SourceRange = Selection.CurrentRegion

ArraySelct = SourceRange.Value
Lenr = UBound(ArraySelct)

For i = 1 To Lenr
ArraySelct(i, 1) = "'" & ArraySelct(i, 1) & "'"
Next i

'Condicion zero
ArrayString = ArraySelct(1, 1)
'Rellenar el resto
For p = 2 To Lenr
ArrayString = ArrayString & "," & ArraySelct(p, 1)
Next p

    clipboard.SetText ArrayString
    clipboard.PutInClipboard
End Sub

What this code is suppouse to do is to take any given info in a Range, take it and put into a single string with a format 'data,' & 'data2,' & ...

But I'm having problems with the last part that is putting my info into the clipboard. I get an

error 424, an object is required.

Can you help me out? I think the error might be on the ArrayString that actually is an Array and should be an String, but I'm just guessing.

Thanks in advance for any help!

Best Regards

Possible duplicate of [Excel VBA code to copy a specific string to clipboard](https://stackoverflow.com/questions/14219455/excel-vba-code-to-copy-a-specific-string-to-clipboard) — cyboashu, Feb 06 '18 at 17:08
@cyboashu I saw that, but I'm looking to understand how is this suppouse to work with any given array I just simplify the problem to be able to play with it, but thank I'll use it for any related problems that I'll have! — VVV, Feb 06 '18 at 17:11
@VincentG sorry I missed that part of the code, I edited it. — VVV, Feb 06 '18 at 17:35
you declared it, but did not affect anything to the variable `clipboard`. You need a `new` and/or a `set` somewhere. maybe `Dim clipboard As New MSForms.DataObject` — Vincent G, Feb 06 '18 at 17:49
@VincentG Excelent! Thank you, the solution was adding the Dim clipboard As New MSForms.DataObject — VVV, Feb 06 '18 at 17:53
@VincentG Can you explain me a little bit how does this work or why does this actually solve my problem? — VVV, Feb 06 '18 at 17:57
When you use `Dim`, you declare that you want to use a variable of a certain type. For basic types, it is enough, but for (more complex) object you need to also create the object itself (using keyword `New`or some function) or get an already existing object and affect it to the variable (using `Set`). See [here](https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/declaring-variables) and lots of other places. — Vincent G, Feb 06 '18 at 18:19

score 0 · Answer 1 · answered Feb 06 '18 at 17:55

Solution given by @vicentG was adding the first Dim and the new on the same line!

Sub PasaraformatoSQLDATA()

Dim clipboard As new MSForms.DataObject


Dim ArraySelc As Variant
Dim SourceRange As Range
Dim Lenr As Long
Dim ArrayString As Variant
Set SourceRange = Selection.CurrentRegion

ArraySelct = SourceRange.Value
Lenr = UBound(ArraySelct)

For i = 1 To Lenr
ArraySelct(i, 1) = "'" & ArraySelct(i, 1) & "'"
Next i

'Condicion zero
ArrayString = ArraySelct(1, 1)
'Rellenar el resto
For p = 2 To Lenr
ArrayString = ArrayString & "," & ArraySelct(p, 1)
Next p

    clipboard.SetText ArrayString
    clipboard.PutInClipboard
End Sub

Paste some info on the Clipboard

1 Answers1