How to get clipboard into a 2D array (excel vba)

Question

I'm very new to Excel VBA and I want to get text from clipboard into a 2D array, with first delimiter vbNewLine and second delimiter space. How do I create a 2D array with unknown size? Can I split an array, like below where I fail?

Sub CommandButton1_Click()
    Dim DataObj As MsForms.DataObject
    Set DataObj = New MsForms.DataObject
    Dim strArray() As String

    On Error GoTo ERRROR

    '~~> Get data from the clipboard.
    DataObj.GetFromClipboard

    '~~> Get clipboard contents
    myString = DataObj.GetText(1)

    '~~> Split into string, delimiter vbNewLine
    strArray = Split(myString, vbNewLine)

Here is here I fail

    '~~> Split each strArray and store in strArray2, delimiter " ".
    Dim strArray2() As String
    For ii = LBound(strArray2) To UBound(strArray2)
        strArray2(ii) = Split(strArray(ii))
    Next ii

Exit Sub
ERRROR:
    If Err <> 0 Then    
    'Handel error
End If
End Sub

Siddharth Rout · Accepted Answer · 2014-11-28 08:28:47Z

2

You are doing it the wrong way.

Is this what you are trying? (UNTESTED)

Sub CommandButton1_Click()
    Dim DataObj As MsForms.DataObject
    Dim strArray, strArray2
    Dim i As Long, j As Long
    Dim myString As String

    On Error GoTo ERRROR

    Set DataObj = New MsForms.DataObject

    '~~> Get data from the clipboard.
    DataObj.GetFromClipboard

    '~~> Get clipboard contents
    myString = DataObj.GetText(1)

    '~~> Split myString, delimiter vbNewLine
    strArray = Split(myString, vbNewLine)

    '~~> Split each strArray and store in strArray2, delimiter " ".
    For i = LBound(strArray) To UBound(strArray)
        strArray2 = Split(strArray(i))

        For j = LBound(strArray2) To UBound(strArray2)
            Debug.Print strArray2(j)
        Next j
    Next i

    Exit Sub
ERRROR:
    If Err <> 0 Then Debug.Print Err.Description
End Sub

answered Nov 28, 2014 at 8:28

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

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

1 Comment

uranibaba Over a year ago

It looks very much like what I am trying to do! Thanks! I'll give it a try after work.

Collectives™ on Stack Overflow

How to get clipboard into a 2D array (excel vba)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related