How to sort files and then populate a ListBox?

Question

This code populate a ListBox with the file names of a specific folder

Dim DIRECTORY As String
DIRECTORY = Dir(myPath & "\*.xlsx", vbNormal)
Do Until DIRECTORY = ""
ListBox1.AddItem DIRECTORY
DIRECTORY = Dir()
Loop

But I want a sorted list.
How can I sort the files firstly and then populate the ListBox.
btw sorting a listbox is (as I know) a long procedure.

Answer 1

The DIR command always displays elements sorted alphabetically but by name, not type. The correct thing would be to take advantage of the "vbDirectory" and "vbArchive" attributes and first create a list with vbDirectory and then another with vbArchive. The result is a single alphabetically sorted list that separates directories from files.

Private Sub ListarFunc()
Dim a As Integer
Dim strArchivo As String
Dim FileName As String
Dim TipoArchivo As String
Dim fso As Object, file As Object, folder As Object
Set fso = CreateObject("Scripting.FileSystemObject")

On Error Resume Next
    
    'If we are not in /root add a "\" to the PathExplorer
    If Mid(Me.BoxPathExplorer, 4, 1) <> "" Then Me.BoxPathExplorer = CurDir() & "\"
    Me.Lista1.ForeColor = RGB(25, 25, 100)
    Me.Lista1.RowSource = ""    'First, clear the list
    Me.Lista1.AddItem "___________________ TIPO ___________________" & ";" & "________________ " & _
        Dir(Me.BoxPathExplorer, vbVolume) & " ________________"
            
    strArchivo = Dir(Me.BoxPathExplorer, vbDirectory)
    While strArchivo <> ""
        Set folder = fso.GetFolder(strArchivo)
        If folder.Attributes And vbDirectory Then Me.Lista1.AddItem folder.Type & ";" & strArchivo
        strArchivo = Dir
        Set folder = Nothing
    Wend

    strArchivo = Dir(Me.BoxPathExplorer, vbArchive)
    While strArchivo <> ""
        Set file = fso.GetFile(strArchivo)
        If file.Attributes And vbArchive Then Me.Lista1.AddItem file.Type & ";" & strArchivo
        strArchivo = Dir
        Set file = Nothing
    Wend
    
    If Lista1.ItemData(1) = "." Then Me.Lista1.RemoveItem 1            'Delete the item '.'
    Me.Lista1 = "NoSelection"
End Sub

Regards.