[SOLVED] Access VBA loop through listbox select items and add to array

Access VBA loop through listbox select items and add to array

I'm trying to loop through a listbox and add the contents to an array....

My code is this:

Private Sub exportfolders_Click()

Dim list As String
Dim folderlist As String
Dim folderarray() As String
'Dim i As Interger

For i = 0 To Me.selectedfolders.ListCount - 1
    'folderlist = (Me.selectedfolders.Column(0, i))
    'folderarray() = Join(Me.selectedfolders.Column(0, i), ",")
    list = (Me.selectedfolders.Column(0, i))
    folderarray() = Join(list, ",")
    ReDim Preserve folderarray(i)
Next i
   folderlist = folderarray
    'folderarray() = Join(folderlist, ",")
    MsgBox (folderlist)

End Sub

You can see the bits I have commented out, trying all sorts to get it to work. But I keep getting the message "Can't assign to array" at folderarray(i) = Join(list, ","). Any pointers as to where I am failing?

Solution

You can concatenate the list box items into a string, and then use Split() to load your array. That way, the array is sized automagically without you needing to ReDim.

I tested this code in Access 2010:

Dim folderarray() As String
Dim i As Long
Dim strList As String

For i = 0 To Me!selectedfolders.ListCount - 1
    strList = strList & "," & Me!selectedfolders.Column(0, i)
Next
' use Mid() to exclude the first comma ...
folderarray = Split(Mid(strList, 2), ",")

Note I don't know what you want to do with the array after loading it. MsgBox folderarray would throw Type mismatch error. MsgBox Mid(strList, 2) would be valid, but if that's what you want, you wouldn't need the array.