Sort FileNames in Listbox

[PeterM]

I have a listbox that I'm populating with filenames from the users hard
drive. I set the Drive and Path and then issue:

myName = dir("*.*", vbNormal)
Do While myName <> ""
If myName <> "." And myName <> ".." Then
If (GetAttr(remotePath) And vbNormal) = vbNormal Then
Me.customer_files.AddItem Chr(34) & myName & Chr(34), ctr
ctr = ctr + 1
End If
End If
myName = dir
Loop

It works great but I need to sort the results. How can I sort the
resulting listbox?

Thanks in advance for your help!

 

[Douglas J. Steele]

Either write the file names to a table, and use a query to as the row source
for your listbox, or else save them to an array (or dictionary), sort the
array, and then add them to the listbox.

 

[PeterM]

That's pretty much what I thought. Which would be faster? using an array or
using a local table? Max number of entries would be around 500.

Can you make a recommendation? thanks!

 

[Guest]

Sorting the table will be quicker than sorting the array.

Sorting an array will take you a couple of days. You
will have to find some code (which will be complex),
copy it into your program, then figure out how to make
it work.

Sorting a table will take you a couple of hours tops,
setting up the table, working out how to add items
to a table. Binding the listbox to the sorted table
will take minutes or seconds.

Because tables are persistent and have a lot of useful
features, they are normally slower than arrays, collections,
or dictionary objects.

But we normally use tables because it is faster and simpler
to use a table than to set up all the custom code to do it
some other way.

(david)

 

[Douglas J. Steele]

I'd probably go with the array. If you're doing this frequently, you'll end
up bloating the database, as you'll need to delete whatever's in the table
and add the new data. Sorting a 500 element array shouldn't take that long.
Assume that the list is already sorted, and simply find where in the list
the new element should go. Something like the following untested aircode:

Sub AddToListbox(ListboxName As String, NewElement As String)
Dim ctlListbox As String
Dim intLoop1 As Integer
Dim intLoop2 As Integer
Dim varArray As Variant

Set ctlListbox = Me.Controls(ListboxName)
varArray = Split(ctlListbox.RowSource, ";")
If IsNull(varArray) = False Then
ReDim Preserve varArray(UBound(varArray) + 1)
For intLoop1 = LBound(varArray) To UBound(varArray) - 1
If StrComp(NewElement, varArray(intLoop1), vbTextCompare) >= 0 Then
For intLoop2 = UBound(varArray) To (intLoop1 + 1) Step -1
varArray(intLoop2) = varArray(intLoop2 - 1)
Next intLoop2
varArray(intLoop1) = NewElement
Exit For
End If
Next intLoop1
ctlListbox.RowSource = Join(varArray, ";")
End If

End If

 

[PeterM]

You are correct!

Populating a listbox from a local table is MUCH faster than sorting an array
and then populating the listbox. Sorting an array took over 1 minute for
1500 entries. Using a local table took about 4-5 seconds.

I also discovered that creating a query, setting the query as the .rowsource
of the listbox and issuing a .requery is even faster than simply issuing an
..additem for each entry read from the table withing the form. Issuing an
..additem took about 20-25 seconds to run.

I would have guessed that sorting the array was faster, that's why I tried
it first. Sorry it only took me about 2 hours to figure out how to sort an
array (knowing nothing about array's beforehand).

thanks to all for your advise.

 

[PeterM]

Douglas...

Populating a listbox from a local table is MUCH faster than sorting an array
and then populating the listbox. Sorting an array took over 1 minute for
1500 entries. Using a local table took about 4-5 seconds.

I also discovered that creating a query, setting the query as the .rowsource
of the listbox and issuing a .requery is even faster than simply issuing an
..additem for each entry read from the table withing the form. Issuing an
..additem took about 20-25 seconds to run.

I would have guessed that sorting the array was faster, that's why I tried
it first. Thanks for the aircode, it was pretty close. Thanks to you I now
know how to use arrays!

thanks to all for your advice.