Order files in a list

Question

I want to list all files in a folder, but order isn't correct. I have file1, file2 to file15. Unfortunately file10 will be listed before file2. I have no influance on the names of the files, but I have to sort them like file1, file2 ... file9, file10 and so on.

Sub orderFiles()
    Dim strFolder As String
    Dim fso As Scripting.FileSystemObject
    Dim fld As Scripting.Folder
    Dim strWert As Variant

    Dim n As Long
    Dim arrNames() As String
    Dim x As Long, y As Long
    Dim TempTxt1 As String
    Dim TempTxt2 As String


    Set fso = New Scripting.FileSystemObject
    ' Modify as needed
    strFolder = p_PR1ImportPath & "\PR1\importiert"
    Set fld = fso.GetFolder(strFolder)

    ' Set up arrays
    n = fld.Files.Count
    ReDim arrNames(1 To n)

    ' Fill arrays
    For Each fil In fld.Files
        i = i + 1
        arrNames(i) = fil.Name
    Next fil

    'Alphabetize Sheet Names in Array List
      For x = LBound(arrNames) To UBound(arrNames)
        For y = x To UBound(arrNames)
          If UCase(arrNames(y)) < UCase(arrNames(x)) Then
            TempTxt1 = arrNames(x)
            TempTxt2 = arrNames(y)
            arrNames(x) = TempTxt2
            arrNames(y) = TempTxt1
          End If
         Next y
      Next x

    ' Do something with the arrays, e.g.
    For i = 1 To n
        Debug.Print arrNames(i)
    Next i
End Sub

The result isn't changed. How can I sort the list of files?

I did a simple workaround and renamed the file with `Name uo.Path & "\" & currentName As uo.Path & "\" & "File" & Format(Replace(Split(File, "File")(1), ".txt", ""), "00") & ".txt"` which isn't the best solution, but it works. Any suggestions? — Paintitblack3k, Mar 25 '19 at 17:02
Maybe save file names to a table with number part extracted to a separate field. Use a query to sort. What is purpose of saving to an array just for Debug.Print? — June7, Mar 25 '19 at 18:45
Debug.Print is only for this demo. I will use arrNames(i) later on to load each file into Access. I don't prefer to create an extra table just to sort the list. In other languages like PHP you can easily sort array and with VBA it's seems really complicated :-( I prefer to rename the files instead of creating an extra table.Does anybody else has an idea how can I sort the array directly in VBA? — Paintitblack3k, Mar 25 '19 at 20:18
@HansUp: The files have always the same pattern with just a different number at the end and the extantion will be always .txt — Paintitblack3k, Mar 25 '19 at 20:20
Correct, sorting array in VBA is not simple. Your solution may be as good as any but review https://stackoverflow.com/questions/152319/vba-array-sort-function and https://www.thespreadsheetguru.com/the-code-vault/2015/3/24/applying-an-alphabetical-sort-to-your-vba-array-list. — June7, Mar 26 '19 at 03:28

score 0 · Answer 1 · edited May 04 '19 at 12:16

I rename each file now with Format "00" like this:

' rename files
currentName = Dir(uo.Path & "\*.txt")
Do While Len(currentName) > 0
    desiredName = "file" & Format(Replace(Split(currentName, "file")(1), ".txt", ""), "00") & ".txt"
        If (currentName <> desiredName) Then
            Name uo.Path & "\" & currentName As uo.Path & "\" & desiredName
        End If
    currentName = Dir
Loop

This is just a workaround, but it works pretty well.

Order files in a list

1 Answers1