How can I sort folders/files listed in a combo box

[colezpapa3]

I am loading the contents of a folder and its subfolders into a combo box. I am displaying the sub-folder, then the contents. It looks like this..

\x1
--------
file1.txt
file2.txt

\xtest
--------
file5.txt
file7.txt

Can I sort the combo box in such a way that the folders are sorted in order. The code is as follows...


Set objFSO = New FileSystemObject
Set objFld = objFSO.GetFolder(strFolder)

For Each objFolder In ThisFolderSubs

x = StrReverse(objFolder)
y = InStr(x, "\")
z = Mid$(x, 1, y)
z = StrReverse(z)

Me.ctlList.AddItem " "
Me.ctlList.AddItem z
Me.ctlList.AddItem "------------------ "

For Each objFile In objFolder.Files
Me.ctlList.AddItem objFile.Name
Next
'

 

[lameid]

Nothing jumps out at me but I'm not that familiar with the FileSystemObject.

Assuming you don't have control of reading in sequence, the easiest thing may be to write the values into a table and query the results so that you can put them in the combobox sorted int he order you want. You could also read the values into an array and programatically sort it (google bubble sort for an example).

 

[JerryKlmns]

colezpapa3

I would use a disconnected recordset to read the folders. Sort the recordset and load it to your combo box. It would be much faster than storing them in a table and querring it for sorting. You also would have to empty the table and keep the user running that part of code.

 

[Remou]

How about FileSearch? That has a sort capability. Roughly:

With Application.FileSearch
    .NewSearch
    .LookIn = "C:\Docs"
    .SearchSubFolders = True
    .FileName = "*.*"
    .MatchTextExactly = False
    .FileType = msoFileTypeAllFiles

    If .Execute(SortBy:=msoSortByFileName, _
        SortOrder:=msoSortOrderAscending) > 0 Then
        For i = 1 To .FoundFiles.Count
            strList = strList & vbCrLf & .FoundFiles(i)
        Next i
    End If
End With
MsgBox strList

 

[JerryKlmns]

Remou

I was to mention the Application.FileSearch but OP has to split the paths and make a collection of folders which has to be sorted

colezpapa3
I think a combination of Remou's and mine suggestion might be the solution.

 

[lameid]

I've never had the occasion to use an offline recordset. It's good to know you can sort them.

colezpapa3, as it would involve less objects I would lean towards Remou's solution of reading and populating the directories and files in order although JerryKlmns's solution should work too. Whatever you do, don't use my recommendation.

 

[colezpapa3]

Hey all... I actually solved this by creating a recordset appending the files into the table and then binding the table to the recordsource of the drop down. Worked fine.

 

[JerryKlmns]

Postcolezpapa3
Glad you got it working

lameid
I use them to build pieces of grouped/categorized data that a big fat heavy SQL statement would run for 30 min! Not only you can sort it, but you can filter it too! Think how fast you loop an array to match an element against a recordset filtering!