Count files in folder – Strange behaviour of Dir

Question

I am using the following function to count files of a certain file type sFileType in folder sPath.

Function CountFilesInFolder(sPath As String, Optional sFileType As String) As Long

    Dim vFile As Variant
    Dim lFileCount As Long
    
    If Right(sPath, 1) <> "\" Then sPath = sPath & "\"
    vFile = Dir(sPath & sFileType)
    While (vFile <> "")
        lFileCount = lFileCount + 1
        vFile = Dir
    Wend
    
    CountFilesInFolder = lFileCount
End Function

When testing the function on a folder that contains:

• 2 xls files and
• 3 xlsx files

With

Debug.Print CountFilesInFolder(“C:\test\”, “*.xls”)

I would expect it to return 2, however, the function is also counting the xlsx files and it returns 5.

If I test the function

Debug.Print CountFilesInFolder(“C:\test\”, “*.xlsx”)

it returns 3, like expected. Why does the function in the first example also count the xlsx files? I am not specifying any wildcard, but still Dir behaves like it. Am I doing something wrong here? I could probably add an If statement in the While/Wend, but I assume I am doing something wrong with the Dir function.

Answer 1

From: https://learn.microsoft.com/en-us/windows-server/administration/windows-commands/dir#:~:text=The%20asterisk%20wildcard%20always%20uses%20short%20file%20name%20mapping%2C%20so%20you%20might%20get%20unexpected%20results.%20For%20example%2C%20the%20following%20directory%20contains%20two%20files%20(t.txt2%20and%20t97.txt)%3A

The asterisk wildcard always uses short file name mapping, so you might get unexpected results.

Windows short name for files with (eg) "xlsx"/"xlsm"/etc extension all end with ".XLS"

More-detailed overview at GSerg's link in the comments above.