CODEWITHSUNDEEP
windowsvbasortingfilesystems
Finch042
Finch042

Reputation: 307

VBA, FileSystemObject, Windows sort order

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!

Upvotes: 2

Views: 5674

Answers (2)

Finch042
Finch042

Reputation: 307

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

Upvotes: 1

paddy
paddy

Reputation: 63481

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

Upvotes: 0

Related Questions