Get the file list of a folder with Dir

Question

I'm trying to get the file list of a folder in VBA

The DIR command 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 tried is "*.txt"
I should get 3 files.
Instead I get: the 3 .txt files and both the .txt1 and .txta files.

I 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.

Systems tried on:
Windows 11; Excel version: 2112
Windows 10; Excel version: 2102
Windows 98; Excel 97

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 with a 3-character extension filter.

Answer 1

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