sort a listbox

[Jaye7]

I have the following code to fill a listbox with file names.

I can not get it to sort it, I found a script but it errors on .list

Fill the listbox

Me.Listbox1.RowSource = ""

Dim MyFolder As String
Dim MyFile As String
'Dim j As Integer
MyFolder = "C:\dbase"
MyFile = Dir(MyFolder & "\*.accdb")
Do While MyFile <> ""
    Listbox1.AddItem MyFile
    MyFile = Dir
Loop

Sort - not working

Dim i As Long
    Dim j As Long
    Dim temp As Variant
       
    With Me.Listbox1
        For j = 0 To Listbox1.ListCount - 2
            For i = 0 To Listbox1.ListCount - 2
                If .List(i) > .List(i + 1) Then
                    temp = .List(i)
                    .List(i) = .List(i + 1)
                    .List(i + 1) = temp
                End If
            Next i
        Next j
    End With

 

[vbaInet]

... but it errors on .list

Perhaps you could furnish us with the error code and description Jaye7

 

[Jaye7]

Compile error:
Method or data member not found.

Dim i As Long
    Dim j As Long
    Dim temp As Variant
       
    With Me.Listbox1
        For j = 0 To Listbox1.ListCount - 2
            For i = 0 To Listbox1.ListCount - 2
               [SIZE=5] If[B][COLOR=Red] .List[/COLOR][/B](i) > .List(i + 1) Then[/SIZE]
                    temp = .List(i)
                    .List(i) = .List(i + 1)
                    .List(i + 1) = temp
                End If
            Next i
        Next j
    End With

 

[gemma-the-husky]

I don't know for sure, but maybe you cannot sort a list like that.

you could store the names in an array, sort the array, and then populate the list box though.

 

[Jaye7]

Hi Gemma,

Are you able to provide the array script?

 

[JHB]

Compile error:
Method or data member not found.

A listbox in MS-Access doesn't have a property called List, (which also the error message makes clear), I think you found the code in an Excel forum.

 

[vbaInet]

A listbox in MS-Access doesn't have a property called List, (which also the error message makes clear), I think you found the code in an Excel forum.

The equivalent are ItemData and Column, however, the code that follows won't work:

                    .List(i) = .List(i + 1)
                    .List(i + 1) = temp

If your aim is to sort the items in a listbox and the listbox is bound to a table/query then you can sort the table/query instead and refresh the listbox.

If on the other hand it's a Value List, i.e. unbound and the list items added through code, then you want to sort the items before adding to the list.

 

[jdraw]

As others have said, you could put values in a table and sort the table; or sort a set of values before adding to a listbox.

Here's a link that may be of interest, but it uses a table and is of broader scope than your post.

 

[Jaye7]

Hi VBAInet,

As per my code in the original post, they are added through a code into a value list, can you please help with the code to sort the items as I thought that when looping through a folder it would put them in the correct sort order but it doesn't.

Me.Listbox1.RowSource = ""
   
  Dim MyFolder As String
  Dim MyFile As String
  'Dim j As Integer
  MyFolder = "C:\dbase"
  MyFile = Dir(MyFolder & "\*.accdb")
  Do While MyFile <> ""
      Listbox1.AddItem MyFile
      MyFile = Dir
  Loop

 

[jdraw]

Jaye,

I looked and revised some old code that gets file names from a folder using FileSystemObject. It brings the file names back sorted by name.
You could use the technique to populate a list box - this sample just brings back the file names and prints them to immediate window.

'---------------------------------------------------------------------------------------
' Procedure : jShowFileInFolderList
' Author    : mellon
' Date      : 21/05/2015
' Purpose   : This procedure gets a  list of files from a defined folder using
' fileSystemObject. The files are returned in sorted sequence.
'
'---------------------------------------------------------------------------------------
'
Sub jShowFilesInFolderList()
    Dim fs As Variant _
        , f As Variant _
        , f1 As Variant _
        , fc As Variant _
        , s As String _
        , folderspec As String
10  On Error GoTo jShowFilesInFolderList_Error

20  folderspec = "C:\users\mellon\downloads"    'folder containing the files to list
30  Set fs = CreateObject("Scripting.FileSystemObject")
40  Set f = fs.GetFolder(folderspec)
50  Set fc = f.Files
60  For Each f1 In fc
70      If f1.name Like "a*.*" Then  ' to limit to accdb files use "*.accdb"
80          s = s & f1.name
90          s = s & vbCrLf
100     End If
110 Next
120 Debug.Print s
121 Debug.Print vbCrLf & vbCrLf & " Total files   " & f.Files.Count ' can comment this line Files in Folder

130 On Error GoTo 0
140 Exit Sub

jShowFilesInFolderList_Error:

150 MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure jShowFolderList of Module AWF_Related"
End Sub

My result for "a*.*"

Access Sample Invoices.zip
access_basics_crystal_080218.pdf
acronisPWD.txt
ActionUA.txt
adt-bundle-windows-x86_64-20140702.zip
AgeGroupQuery_A2003.zip
Analyzer_141028_vbWatchdog_OpenBypass.zip
AS SSD Benchmark.zip
AS SSD Benchmark_inst.exe
ATIH2014_userguide_en-US.pdf
atih_installer_hd_s_e.exe
AutoNum2k.zip



 Total files   136

 

[Solo712]

vbaINet's solution is probably the way to go. Just for fun, though, I wonder if the listbox can be sorted this way:

Dim OutOfSequence as Boolean
    Dim i As Long, j as Long
    Dim temp As Variant
       
    With Me.Listbox1
        Do
            OutOfSequence = False 
            For i = j to .ListCount - 2
                If .ItemData(i) > .ItemData(i + 1) Then
                    temp = .ItemData(i)
                    j=i
                    .RemoveItem(i)
                    .AddItem temp
                    OutOfSequence = True
                    Exit For
                End If
            Next i
        Loop While OutofSequence 
    End With

Code is untested. Just thinking...

Best,
Jiri

 

[vbaInet]

A bit more involved

I remember needing to do this sort of thing and I ended up building a recordset from scratch, populating it, applying a sort to the appropriate field(s) and reading off the values. Even sorting an array (like gemma-the-husky hinted) is another possibility too but it's best to use one of the sorting algorithms, i.e. Bubble Sort or Quick Sort; and I think I used Quick Sort for sorting a dictionary once. There should be code online for both methods otherwise if the poster really needs it I can try and find the project I used them on.

Nevertheless, here's another proof of concept I was just playing with, using command prompt. It can be expanded to sort on other types too so do to it as you wish:

Public Sub SortFileList(DirPath As String)
    Dim objShell    As Object
    Dim objFso      As Object
    Dim objStream   As Object
    Dim strTempFile As String
    
    Const TEMP_FILE As String = "%TEMP%\sortfile100.txt"
    
    Set objShell = CreateObject("WScript.Shell")
    
    strTempFile = objShell.ExpandEnvironmentStrings(TEMP_FILE)
    
    ' Sorts the output of dir and outputs it to sortfile100.txt in the TEMP folder
    ' The /O switch is what does the sorting
    objShell.Run "%comspec% /C dir " & _
                 Chr(34) & DirPath & Chr(34) & _
                 " /A:-D /B /D /O:N " & _
                 "> " & Chr(34) & strTempFile & Chr(34), 0, True
    
    ' Open the temp file
    Set objFso = CreateObject("Scripting.FileSystemObject")
    Set objStream = objFso.OpenTextFile(strTempFile, ForReading)
    
    ' Return each line as a string and print to the immediate window
    With objStream
        Do Until .AtEndOfStream
            Debug.Print .ReadLine
        Loop
    End With
    
    objStream.Close
    Set objStream = Nothing
    Set objFso = Nothing
    Set objShell = Nothing
    
    ' Delete the temp file
    If Dir(strTempFile) <> vbNullString Then Kill strTempFile
End Sub

Fyi: If you don't mind seeing the black command prompt window flash for a second, then the code above can be amended to use the Exec method of WScript instead and from there you can read the output from StdOut, so there will be no need to output to a file. There's just no way of hiding the command prompt window completely using Exec hence the above approach of using Run.

 

[Jaye7]

Sorry for the late reply,

Thanks for your help everyone, I have gone with Jdraw's script as it is the first I tested and it does the job nicely.

 

[Galaxiom]

The simplest way to sort and manage listboxes is to use a fabricated ADO recordset as the listbox's recordset.

This technique provides both Sort and Filter methods.

 

[vbaInet]

The simplest way to sort and manage listboxes is to use a fabricated ADO recordset as the listbox's recordset.

This technique provides both Sort and Filter methods.

Ditto! My preferred option. We were thinking along the same lines

I remember needing to do this sort of thing and I ended up building a recordset from scratch, populating it, applying a sort to the appropriate field(s) and reading off the values.