Naturally Sort a Directory in VBA

Question

I am running a VBA script through a directory but I need to go through the files as if they were sorted in Windows Explorer. For example I have a directory like so:

32 Read.log
64 Write.log
256 Read.log
512 Write.log
1024 Write.log
4 Read.log

When I sort it with VBA, it only looks at the first character to sort it like so:

1024 Write.log
256 Read.log
32 Read.log
4 Read.log
512 Write.log
64 Write.log

Any ideas on how to sort from smallest to largest number before I go through the directory?

score 1 · Answer 1 · answered Oct 10 '13 at 20:30

I actually went to the trouble of building an algorithm for it:

Dim a As Variant
Dim c As String
Dim d As String
Dim x As Long
Dim y As Long
Dim s As Boolean
Dim p As Long
Dim q As Long
Dim e As Long
Dim n1 As String
Dim n2 As String

'Create a dummy array to test
a = Array("1024 Write.log", "256 Read.log", "32 Read.log", "4 Read.log", "512 Write.log", "64 Write.log")

'Loop through the array and look for values that need to change position
For x = LBound(a) To UBound(a) - 1
    For y = x + 1 To UBound(a)

        'Check if the values at x and y must be swapped
        s = False

        'Loop through each character in both strings to do a compare
        If Len(a(x)) > Len(a(y)) Then e = Len(a(x)) Else e = Len(a(y))
        For p = 1 To e
            If Len(a(x)) < p Then
                'y is longer, so it should come last
                Exit For
            ElseIf Len(a(y)) < p Then
                'y is shorter, so it should come first
                s = True
                Exit For
            ElseIf InStr("0123456789", Mid(a(x), p, 1)) = 0 Or InStr("0123456789", Mid(a(y), p, 1)) = 0 Then
                'The char at p in x or y is not a number, so do a text compare
                If Mid(a(x), p, 1) < Mid(a(y), p, 1) Then
                    Exit For
                ElseIf Mid(a(x), p, 1) > Mid(a(y), p, 1) Then
                    s = True
                    Exit For
                End If
            Else
                'The char at p for both x and y are numbers, so get the whole numbers and compare

                'Get the number for x
                n1 = ""
                q = p
                Do While q <= Len(a(x)) And InStr("0123456789", Mid(a(x), q, 1)) <> 0
                    n1 = n1 & Mid(a(x), q, 1)
                    q = q + 1
                Loop

                'Get the number for y
                n2 = ""
                q = p
                Do While q <= Len(a(y)) And InStr("0123456789", Mid(a(y), q, 1)) <> 0
                    n2 = n2 & Mid(a(y), q, 1)
                    q = q + 1
                Loop

                If Len(n1) > Len(n2) Then
                    'n1 is a bigger number, so it should be last
                    s = True
                    Exit For
                ElseIf Len(n1) < Len(n2) Then
                    'n1 is smaller, so it should remain first
                    Exit For
                ElseIf n1 > n2 Then
                    'n1 is a bigger number, so it should be last
                    s = True
                    Exit For
                ElseIf n1 < n2 Then
                    'n1 is smaller, so it should remain first
                    Exit For
                End If
            End If
        Next

        'Do the swap
        If s Then
            c = a(y)
            a(y) = a(x)
            a(x) = c
        End If

    Next
Next

'Verify that it worked
c = ""
For p = LBound(a) To UBound(a)
    c = c & a(p) & vbCrLf
Next
MsgBox c

score 0 · Answer 2 · edited May 23 '17 at 12:10

0

Read the directory into a dictionary object as in CreateObject("Scripting.Dictionary") and write a function that will sort the dictionary in the exact way you want.

An example can be found at this question: Sort dictionary

EDIT: If you already have it in an array, you can adjust the code to just sort the array

EDIT: Simple example of using a dictionary:

Dim vArray As Variant
Dim vDict As Object
Dim i As Variant

vArray = Array("F1", "F2", "F3")
Set vDict = CreateObject("Scripting.Dictionary")
For i = LBound(vArray) To UBound(vArray)
    vDict.Add i, vArray(i)
Next
For Each i In vDict
    MsgBox "Key: " & i & ", Value: " & vDict(i)
Next

edited May 23 '17 at 12:10

Community

1
1

answered Oct 10 '13 at 15:00

neelsg

4,802
5
34
58

I am having trouble adding things to a dictionary. I have all my files in an array so how can I import them into a dictionary? – user2847371 Oct 10 '13 at 15:12
I thought there was no Natural Sorting of an array, only ASCII sorting? – user2847371 Oct 10 '13 at 15:32
@user2847371 I am suggesting that you write a function to do the sort. See the linked question. If you write a function, you can do the sort any way you want and you are not relying on any built-in sorting capabilities of an array – neelsg Oct 10 '13 at 19:15

score 0 · Answer 3 · answered Oct 10 '13 at 15:04

0

Once the data has been imported into Excel, parse the data so the size is in column A and the name is in column B. Then insure (or convert) the data in column A to be values rather than Text. Then sort columns A & B by A Ascending.

answered Oct 10 '13 at 15:04

Gary's Student

95,722
10
59
99

I need it sorted before I import it into Excel – user2847371 Oct 10 '13 at 15:19

Naturally Sort a Directory in VBA

3 Answers3

Linked