Sequential reading of file names in a folder using excel VBA

Question

I am trying to read nearly 1000 files in a folder using VBA. I wish the code to pick the file name in an incremental order such as Dummy3_1, Dummy3_2, Dummy3_3, etc. But instead, the current code picks Dummy3_10 after Dummy3_1. How I could make the code to read the file name sequentially. Thanks

Application.ScreenUpdating = False
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Please select a folder"
        .ButtonName = "Pick Folder"
        If .Show = 0 Then
            MsgBox "Nothing was selected"
            Exit Sub
        Else
            FileDir = .SelectedItems(1) & "\"
        End If
    End With
FiletoList = Dir(FileDir & "")
    Do Until FiletoList = ""

Are all the files consistently named _1, _2, _3, ... _10, _11, _12, ... _997, _998, _999, _1000 ? — Richard Crossley, Feb 09 '20 at 03:40

score 1 · Answer 1 · answered Feb 09 '20 at 05:43

1

Dir does not gaurantee returning files in any particular order (see here and here).

To get them in sorted order, you will need to read all files into some data structure, and sort that before returning them to your List.

As Matthew has described, how you sort will depend on the file name structure and your sorting rules.

answered Feb 09 '20 at 05:43

chris neilsen

52,446
10
84
123

Thanks for the pointer, Chris. I didn't know the Dir returned "in no particular order" until now. Much appreciated. +1 – Matthew Feb 09 '20 at 17:04

score 1 · Answer 2 · answered Feb 09 '20 at 10:26

Option Explicit

Sub test()

    Dim folder
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Please select a folder"
        .ButtonName = "Pick Folder"
        If .Show = 0 Then
            MsgBox "Nothing was selected"
            Exit Sub
        Else
            folder = .SelectedItems(1) & "\"
        End If
    End With
    With CreateObject("Scripting.FileSystemObject")
        Dim i
        For i = 1 To 1000
            Dim path
            path = folder & "Dummy3_" & i
            If .FileExists(path) Then
                '
                ' your code here
                Debug.Print path
                '
                '
            End If
        Next
    End With

End Sub

Thanks. I will update the code to avoid any error that may pop up due to no-guarantee of the file order by 'Dir' — Nithin Sudarsanan, Feb 10 '20 at 16:46
@NithinSudarsanan Please accept the answer if it solves the issue. — omegastripes, Feb 10 '20 at 17:14

Matthew · Accepted Answer · 2020-02-09T17:07:04.590

The Dir command returns filenames "in no particular order" (See the answer by Chris Neilsen for links) so you are not guaranteed to get the order you want.

Additionally, when dealing with strings, the computer will sort them using alphabetical order. With letters, you should be able to see that these are in alphabetical order:

b
ba
c

Numbers are no different when they are in strings. The correct alphabetical order for these numbers is:

1
10
2

If you want numbers to automatically sort "properly" in numerical order when they are part of strings, you need to pad them with leading zeroes, so these numbers are both in alphabetical order (like strings) and numerical order (like numbers)

01
02
10

You have two options in your case. (EDIT: Based on the behavior of the Dir command, option #1 is not as easy as I would have thought)

1) If you control the input files, the easiest (and also best practice) way to handle this is to create the files with padded numbers, so instead of Dummy3_1, Dummy3_10, etc, you would choose a number of zeroes which will hold the largest number of files you could have (you said more than 1000, and I will assume less than 10k) and pad the names, so name the files Dummy3_0001, Dummy3_0010, etc. If you are able to do this, your code should just start working without modification.

2) If you do not have control of the filenames, you can't use the Dir command as it is. You would have to read the files in to an array, split them into the "Header" part (Dummy3_ in your case) and the "numeric" part (everything after the _) and then sort the list yourself in numeric order. This is a bit of work to do.

I hope this helps you.

re your option 1 - [Dir does not gaurantee the order of return files](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/dir-function?f1url=https%3A%2F%2Fmsdn.microsoft.com%2Fquery%2Fdev11.query%3FappId%3DDev11IDEF1%26l%3Den-US%26k%3Dk(vblr6.chm1008898)%3Bk(TargetFrameworkMoniker-Office.Version%3Dv16)%26rd%3Dtrue), so OPs code may not return files in sorted order — chris neilsen, Feb 09 '20 at 05:47
Right, and thanks for that. I edited my answer to reflect your link. Much appreciated. — Matthew, Feb 09 '20 at 18:06
Thanks. Once I renamed all the files as per the recommendation, the code is reading the files in sequence. — Nithin Sudarsanan, Feb 10 '20 at 16:42
If you like the answer, it would be appreciated if you accept it, or upvote it. Glad we could be of help. — Matthew, Feb 10 '20 at 18:05

Sequential reading of file names in a folder using excel VBA

3 Answers3