Different file sorting between Microsoft Explorer and dir command

Question

I have a lot of measurement files as csv-files and I want to merge them next to each other in one excel file. My VBA code works fine except the fact that it kind of sorts the file names in the wrong alphabetical order. When I look in the Explorer I get my files in the proper order:

But when I either use the dir command in the command line or use VBA code to list/sort the file names:

Sub Dateien_eines_Ordners_Auflisten()

Dim lngZeile As Long
Dim objFileSystem As Object
Dim objVerzeichnis As Object
Dim objDateienliste As Object
Dim objDatei As Object

Set objFileSystem = CreateObject("scripting.FileSystemObject")
Set objVerzeichnis = objFileSystem.GetFolder("D:\Dokumente\Masterarbeit-Bertrandt\Versuche_Prüfstand\Messungen\150RPM\")
Set objDateienliste = objVerzeichnis.Files

lngZeile = 1

For Each objDatei In objDateienliste
     If Not objDatei Is Nothing Then
          ActiveSheet.Cells(lngZeile, 1) = objDatei.Name
          lngZeile = lngZeile + 1
     End If
Next objDatei

End Sub

I get the folling output:

How can I sort the files like the Explorer does? And why is there a difference in the sorting?

Thanks a lot for any help!

`FileSystemObject` is not sorting at all. See for example https://stackoverflow.com/questions/16895525/order-of-files-collection-in-filesystemobject — FunThomas, Sep 16 '19 at 12:43
Listing a file-system directory returns files in whatever order they happen to have in the directory record. With NTFS, the result will be ordered by Unicode ordinal, because NTFS happens to store files in a b-tree that's ordered that way. Other file systems may return files with a random order. CMD's `dir` command has a `/o` option to sort the result, such as `/on` to sort by [n]ame. But this is also a simple sort by Unicode ordinal. What you're seeing in Explorer is called a [natural sort order](https://en.wikipedia.org/wiki/Natural_sort_order). — Eryk Sun, Sep 16 '19 at 12:43

score 2 · Answer 1 · answered Sep 16 '19 at 13:49

2

Since FileSystemObject does no sorting at all (it returns the names as they are in the file system which might look random) just sort your column with filenames after you inserted them using the Range.Sort method.

Option Explicit

Sub SortColumnA()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")

    With ws.Sort
        .SortFields.Clear
        .SortFields.Add2 Key:=ws.Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

        .SetRange ws.Range("A:A")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

answered Sep 16 '19 at 13:49

Pᴇʜ

56,719
10
49
73

In general your sorting works but not on my specific example. It's because the algorithm sorts by characters and not by numbers. So 01kHz is smaller than 1kHz... – Moebo Sep 16 '19 at 14:43
Since this is the only sort algorithm VBA supports the only option would be to write your own natural sort algorithm, or use zeros and make all kHz the same length. – Pᴇʜ Sep 16 '19 at 14:49

score 1 · Answer 2 · answered Sep 16 '19 at 14:48

I made it run by renaming my files with a renaming tool and now it works as expected. It's because the sorting algorithms sort by characters and not as a human understands it and that was my mistake.. Changing -1kHz- to -01kHz- or -250mA to -0250mA for example solved it.

score -1 · Answer 3 · answered Sep 16 '19 at 13:31

I am not aware of how the dir command sorts files, but luckily, looking at your files we can write simple logic to obtain the files in the correct order.

Only the frequency (kHz) and the ampere (mA) are changing while the rest of the file name stays the same. So we just have to make the changing parts of the file name (frequency and ampere) a variable and loop through the possibilities.

Use the code below. I commented on the sections that require you to change them if needed. Hope this helps.

Sub SortingDirCommand()

    Dim FilesLocation As String
    Dim kHz As Long
    Dim mA As Long
    Dim Count As Long
    Dim Text1 As String, Text2 As String, Text3 As String
    Dim FileName As String

    Dim Files() As String

    FilesLocation = "D:\Personal Files and Folders\VBA Related\StackOverFlow\Sorting Dir Command\Test" & "\" '' make sure the end has a slash "\"

    Text1 = "FOC-"
    Text2 = "kHz-150RPM-"
    Text3 = "mA.csv"

    Count = -1 ''' Start at -1 because Files starts at 0

    For kHz = 1 To 30 ''Make the limit the highest frequancy you have

        For mA = 0 To 4500 Step 250 '' Mkae the ampere limit the highest and make sure the steps are correct

            FileName = Dir(FilesLocation & Text1 & kHz & Text2 & mA & Text3, vbNormal)

            If FileName <> "" Then

                Count = 1 + Count
                ReDim Preserve Files(Count)
                Files(Count) = FileName
                ActiveSheet.Cells(1 + Count, 1) = FileName

            End If

        Next mA

    Next kHz

End Sub

Different file sorting between Microsoft Explorer and dir command

3 Answers3