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.

CodePudding user response：

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

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.