sort order of FSO.GetFolder

[GraeagleBill]

Is there a way to specify that FSO.GetFolder return the list of folders in numerical sort order? The module I just created works just fine, but the names found are not sorted numerically.

Public Function DirFolders(strDirectory As String, Optional Req As String)
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
'  Find the names of folders in a directory.  Optionally, restrict
'  the names returned to folder names that contain a sub-string.
'  In either case, the names are returned as a semicolon delinated
'  list.
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
Dim objFSO As Object
Dim objFolders As Object
Dim objFolder As Object
DirFolders = ""


    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFolders = objFSO.GetFolder(strDirectory).SubFolders
  
    For Each objFolder In objFolders
        If Not IsMissing(Req) Then
            If InStr(objFolder.Name, Req) > 0 Then _
                DirFolders = DirFolders & objFolder.Name & ";"
        Else
            DirFolders = DirFolders & objFolder.Name & ";"
        End If
    Next objFolder


Set objFSO = Nothing
Set objFolders = Nothing
Set objFolder = Nothing


End Function

[Micron]

I'd say no if you mean directly from the folder/subfolders looping, but let's see what smarter people say. These folders have full/partial numeric names? Then create a recordset sorted by sql that has an Order By clause?

[GraeagleBill]

I thought as much. What I was up to was to create a value list for a combo. I'll load up a temporary table and let SQL furnish the combo with a sorted RecordSource.

[Welshgasman]

Another way might be to use the old DIR command and write the output to a file, then read that?

https://learn.microsoft.com/en-us/wi...s-commands/dir

[moke123]

Would help to know what the actual folder names look like.
You mention numerical but the folder names would be text and sort like text.

When I debug.print subfolder names in a folder with normal non-numerical names they sort in alphabetical order.

I also think that your "Optional Req As String" should be "Optional Req As Variant" in order for IsMissing to function properly.

[moke123]

Here's an example of sorting folders using a dictionary.
Can't get more specific without knowing what your folder names look like but if they need to be fudged you can do that in the item of the dictionary and sort by that.
note: need reference to MS Scripting runtime and MS Office XXX object library.

EDIT: also just noticed that your instr() syntax is incorrect.