VBA, FileSystemObject, Windows sort order

Question

I'm tying to make something in VBA that will basically list all the files in one or more directories starting from a root folder. Long story short, I'm using filesystemobject to run through all of the folders and then getting all the files in those folders. Moving to the next folder, etc.

The problem I'm running into is that I need to spit out my data (onto a sheet) in the same folder sort order as one might find in Windows. I know this isn't a fixed concept per say, so here's a quick example, as it's displayed in Windows(for me):

Windows Sort Order:

FolderTest\000
FolderTest\0
FolderTest\0001

Not too surprisingly, when using FSO it returns the sub folders in a different (perhaps more logical) order:

FolderTest\0
FolderTest\000
FolderTest\0001

I was hoping someone might have an idea of what one could do to get this to be resorted as it's displaying in Windows. This is just an example obviously, the files could be named anything, but it certainly seems to behave a lot better with alpha characters in the name. I'm not necessarily married to using FSO, but I don't even know where else to look for an alternative. I know I could potentially resort these in an array, but I'm not sure what kind of wizardry would be required to make it sort in the "proper" order. For all I know, there's some method or something that makes this all better. Thanks in advance for any help!

By "in windows", I assume you mean "windows explorer", which usually sorts by name. Since that's obviously not the case here, I would suggest that it is being sorted by modification date or some other field. Without knowing that, we can't really answer your question. — paddy, Jul 09 '13 at 01:43
@paddy The folders are definitely sorted by name in windows explorer. — Finch042, Jul 09 '13 at 01:49
Might be worth noting that the way the files are "sorted" in Windows Explorer is merely the way they are *displayed* to the user. FSO is returning them in a logical order. Using `DIR` function return files in the same order but it is faster than FSO. With that in mind, this is somewhat of a duplicate question, and BrettDJ's answer should point you in the right direction. http://stackoverflow.com/a/8691872/1467082 — David Zemens, Jul 09 '13 at 02:20
possible duplicate of [Use Dir to return files from a folder in file system order](http://stackoverflow.com/questions/8690792/use-dir-to-return-files-from-a-folder-in-file-system-order) — David Zemens, Jul 09 '13 at 02:20

Finch042 · Answer 1 · 2013-07-09T03:55:46.377

To whoever it may end up helping, the following code looks like it's giving me the results I was looking for, converting a list of subfolders into the same sort orders you (probably) find in Windows Explorer. Feeding in Subfolders from a Filesystem object, it spits the results out in an array (fnames). The code... it's not pretty. I'll be the first to admit it. Don't judge me too harshly. Big thanks @Paddy (see above) for pointing me towards StrCmpLogicalW (http://msdn.microsoft.com/en-us/library/windows/desktop/bb759947(v=vs.85).aspx)

Private Declare PtrSafe Function StrCmpLogicalW Lib "shlwapi" _
(ByVal s1 As String, ByVal s2 As String) As Integer

Sub filefoldersortWindows()
Dim folder As String
Dim fnames() As String, buffer As String, content As String

folder = "Your Path"
 Set fsol = CreateObject("Scripting.fileSystemObject")
Set fold = fsol.GetFolder(folder)
FoldCount = fold.SubFolders.Count

ReDim fnames(FoldCount)
cFcount = 0
For Each fld In fold.SubFolders
    cFcount = cFcount + 1
    Namer$ = fld.Name

   fnames(cFcount) = StrConv(Namer, vbUnicode)
Next
For AName = 1 To FoldCount
    For BName = (AName + 1) To FoldCount
        If StrCmpLogicalW(fnames(AName), fnames(BName)) = 1 Then
            buffer = fnames(BName)
            fnames(BName) = fnames(AName)
            fnames(AName) = buffer
        End If
    Next
Next
For i = 1 To FoldCount
   fnames(i) = StrConv(fnames(i), vbFromUnicode)
    If i > 1 Then
    content = content & "," & fnames(i)
    Else
    content = fnames(i)
    End If
Next



End Sub

score 0 · Accepted Answer · answered Jul 09 '13 at 02:02

0

Ahh, I see now. I made a bunch of directories with numeric names to see what's going on. Windows explorer does an integer conversion on the value. The sort rule is like this:

numeric value   : ascending
padding length  : descending

So, if have 01 and 001, both evaluate to the integer 1, but 001 will appear first because it is longer (has more zero-padding). The 'length' in this case only refers to the numeric part (ie the padding), and is not affected by any characters that appear after (they only matter if the numeric value and the padding length are the same - then normal ordering applies):

Numeric folder names in Explorer

answered Jul 09 '13 at 02:02

paddy

60,864
6
61
103

To follow up, I just found [this old post](http://stackoverflow.com/questions/442429/windows-explorer-sort-method) which contains a good link. The problem is trickier than I suggested, because filenames can be numerically suffixed as well. – paddy Jul 09 '13 at 02:11
the resource is a big help. I'll probably need to mess with it for... well, a long time, if I'm being honest, but I'm a lot better off than I was earlier. Thanks very much. – Finch042 Jul 09 '13 at 02:21

VBA, FileSystemObject, Windows sort order

2 Answers2