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, Aug 28 '15 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, Aug 28 '15 at 20:04

score 1 · Accepted Answer · answered Aug 28 '15 at 19:56

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.

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! — Brian Powell, Aug 28 '15 at 20:03

score 1 · Answer 2 · answered Aug 31 '15 at 09:27

1

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 '15 at 09:27

brettdj

54,857
16
114
177

score 0 · Answer 3 · answered Aug 28 '15 at 20:18

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.

score 0 · Answer 4 · edited May 23 '17 at 11:58

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

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

4 Answers4