Extract one dimernsion of a 2D variant array to a string

Question

Similar to: Convert Variant Array to String - but the accepted answer there does not work in my case.

My code:

SQLStr = "SELECT lineID FROM alldata where asdf = '" & asdf & "'"
Set rs = New ADODB.Recordset
rs.Open SQLStr, conn, adOpenStatic

Dim arr
Dim arrString
rs.MoveFirst
arr = rs.GetRows
arrString = Join(arr, ", ")

after the statement arr = rs.GetRows executes, it has the following value in my console:

I'm trying to join these two values, separated by a comma space into a string, so the end results is:

arrString = "25616, 99607"

However, I get an Invalid procedure call or argument error when I run the Join line. What am I doing wrong?

I suspect it has to do with the array being two dimensional. I am looking in to testing it right now. — Soulfire
– Soulfire, Commented Aug 28, 2015 at 19:37
exactly - I couldn't figure out how to transpose it to a one-dimensional array either, which is why the code was breaking.... You can't join nothing :) — Brian Powell
– Brian Powell, Commented Aug 28, 2015 at 20:04

brettdj · Accepted Answer · 2015-08-31 09:27:21Z

3

As you are in Excel you can use Index to slice through and return a 1D array

 Dim arr(0, 2) As Variant

 arr(0, 0) = "25616"
 arr(0, 1) = "99607"
 arr(0, 2) = "12345"
 MsgBox Join(Application.Index(arr, 0), ", ")

answered Aug 31, 2015 at 9:27

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

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

Comments

Soulfire · Accepted Answer · 2015-08-28 19:56:27Z

1

While I am certain there is a more elegant way to accomplish your task, I did write some code that appears to be running successfully.

Option Explicit

Private Sub TestArray()

        Dim arr(0, 1) As Variant 'Mimic the structure of your array
        Dim arrString As String
        Dim sDelimiter As String
        Dim j As Long

        'Mimic the structure of your array
        arr(0, 0) = "25616"
        arr(0, 1) = "99607"

        sDelimiter = ", " 'Set your delimiter

        'Loop through the inner array
        For j = 0 To UBound(arr, 2)
                'Concatenate the elements
                arrString = arrString & sDelimiter & arr(0, j)
        Next j

        'Remove the leading delimiter / cleanup
        arrString = Replace(arrString, sDelimiter, "", 1, 1, vbTextCompare)

        MsgBox arrString

End Sub

The interesting code is from sDelimiter = ", " and down.

I could not figure out how to Transpose the array into a one-dimensional array (from an array of two dimensional arrays), so I decided to loop through the inner array and concatenate the values.

The concatenation results in an extra delimiter at the front of the list so there is a line that replaces that delimiter with nothing.

answered Aug 28, 2015 at 19:56

Soulfire

4,3161 gold badge25 silver badges34 bronze badges

1 Comment

Brian Powell Over a year ago

Wow, this is really nice. I like how you solved the issue - building the string as itself + the new stuff in a loop. Thank you so much!

Dick Kusleika · Accepted Answer · 2015-08-28 20:18:43Z

Join only accepts 1-d arrays. Here's a non looping way to get a recordset to a comma separated string.

Sub TestArray()

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sArr As String

    Set cn = New ADODB.Connection
    cn.Open gsCON

    Set rs = cn.Execute("SELECT * FROM InvoiceLine")

    rs.MoveFirst
    sArr = Join(Split(rs.GetString, vbTab), ", ")

    Debug.Print sArr

    rs.Close
    cn.Close
    Set cn = Nothing

End Sub

GetRows puts the data in the wrong row/column orientation. You could use Application.Transpose to get it right, but you still can't pass it to Join because it's 2D.

GetString returns a tab/lf delimited string. I split that on tabs, then rejoin it on comma-space, and it's good to go.

Community · Accepted Answer · 2017-05-23 11:58:02Z

Just 2 days ago there was a question which prompted me to write a function which I called MultiSplit() which took a string and 2 delimiters and split it into a 2-dimensional array. This current question requires a sort of inverse:

Function MultiJoin(A As Variant, Optional ItemDelimiter As String = ",", Optional RowDelimiter As String = ";") As String
    'assumes that A is a 2-dimensional array of strings
    Dim s As String, i As Long, j As Long
    Dim lb1 As Long, lb2 As Long, ub1 As Long, ub2 As Long
    lb1 = LBound(A, 1)
    lb2 = LBound(A, 2)
    ub1 = UBound(A, 1)
    ub2 = UBound(A, 2)
    For i = lb1 To ub1
        For j = lb2 To ub2
            s = s & A(i, j) & IIf(j < ub2, ItemDelimiter, "")
        Next j
        s = s & IIf(i < ub1, RowDelimiter, "")
    Next i
    MultiJoin = s
End Function

Tested like:

Sub test()
    Dim A As Variant
    ReDim A(0 To 0, 0 To 1)
    A(0, 0) = "25616"
    A(0, 1) = "99607"
    Debug.Print MultiJoin(A, ", ")
    ReDim A(0 To 1, 0 To 1)
    A(0, 0) = 1
    A(0, 1) = 2
    A(1, 0) = 3
    A(1, 1) = 4
    Debug.Print MultiJoin(A, " ", vbCrLf)
End Sub

Output:

25616, 99607
1 2
3 4

Collectives™ on Stack Overflow

Extract one dimernsion of a 2D variant array to a string

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related