VBA DIR command malfunctioning-CodePudding

I'm trying to get the file list of a folder in VBA and ran into a problem with the DIR command, it acts as if the * wildcard is messing up the whole mask that I input.

Here is my file list:

NotAText.inf
Sample1.txt
Sample2.txt
Sample3.txt
Sample4.txt1
Sample5.txta

The original mask I'm trying to use is "*.txt" so I should get 3 files. But what I get instead is: the 3 .txt files and both the .txt1 and .txta files.

I've tested with other filenames and extensions, if I use question marks only, it works OK, e.g.: Mask: "???????.txt" returns the 3 .txt files as it should.

Does anyone experience the same fault as described here? Does anyone maybe know of an existing issue that causes this? Systems tried on: Windows 11; Excel version: 2112 Windows 10; Excel version: 2102 Windows 98; Excel 97

I'm probably missing something very lame, but if there's anyone that could help, I'll be very thankful. :)

Sub test()

    Dim i As Integer
    Dim s As String
    
    i = 1
    
    s = Dir("*.txt")
    
    Do While (s <> "")
        Cells(i, 1) = s
        s = Dir
        i = i   1
    Loop

End Sub

Fresh find: if I use a file extension longer than 3 characters as a filter, it works OK, it only returns the required files. I found under Windows 98's command prompt that Windows treats a 3-character file extension filter as a "base" and treats similar files (beginning with that filter) as "TXT" as well, I suppose this is true for modern Windows versions too, that's why the fault exists. My guess is, when a 3-character extension filter is used, it searches using the 8.3 filename format, when a longer one is used, it searches using the proper long-filename. Did I just find an ancient Windows bug???

SAMPLE1  TXT             0  21.12.14  12.04 Sample1.txt
SAMPLE2  TXT             0  21.12.14  12.04 Sample2.txt
SAMPLE3  TXT             0  21.12.14  12.04 Sample3.txt
SAMPLE~1 TXT             0  21.12.14  12.04 Sample4.txt1
SAMPLE~2 TXT             0  21.12.14  12.04 Sample5.txta

Interesting fact: under PowerShell, the command works fine with a 3-character extension filter.

CodePudding user response：

I've never had a problem with Dir but for your needs try something like this -

Dim i As Long
Dim s As String, sPath As String, sFilter As String
    
    i = 1
    sPath = CurDir & "\"
    sFilter = "*.txt"    
    s = Dir(sPath, 15)
    
    Do While Len(s)
        If LCase(s) Like sFilter Then
            Cells(i, 1) = s
            i = i   1
        End If
        s = Dir
    Loop

End Sub