VBA: WildCard * - find a file ending to a digit - finds the wrong file

Question

I am Windows user. If I set the condition to "*1.jpg" then it lists a file ending with "2". (if I set condition to *2.jpg or *3.jpg those work fine)

Sub prob()

Dim x As String

x = Dir("C:\Users\" & Environ("UserName") & "\Downloads\folder\jpg\*" & "1" & ".jpg")

End Sub

There are 3 files: company 34 31.jpg (this is never listed) company 34 32.jpg (this is listed if condition is 1 or 2) company 34 33.jpg (this is listed if condition 3)

There are several other examples, like in other folder it returns a jpg file ending 2 while condition is *3.jpg. I can not find any logic or pattern in this. Png format seems to not work only with condition "*1.png" and for example by pdf format seems everything works without errors.

There are no hidden files, there is no 1000 lines of code to have multiple bug possibilites. It's just a single line. I want a file ending in a digit and other file will be returned.

Quite confusing situation for me and feels so bad I can't even figure out a pattern, just seems totally random.

2.

see second screenshot: it seems like if there is a 1 digit ending file, and a lot of double digit ending files, then the 1 digit ending file takes precedence.

user_7599798 · Answer

You are probably a victim of the short filenames (8.3) that Windows generates for compatibility reasons. To check this, open a CMD-window, change to the folder and issue the command Dir /x *1.jpg - this should show you the same result as the Dir in VBA - probably including your file company 34 32.jpg, which has an short name of something like compan~1.jpg

Best I can think about is to change the wildcard to *.jpg and then check manually if the last character before the . is a 1.

fileName = Dir("C:\Users\" & Environ("UserName") & "\Downloads\folder\jpg\*.jpg")
Do While filename <> ""
    If Mid(filename, InStrRev(filename, ".") - 1, 1) = "1" Then
        Debug.Print filename
    End If
    filename = Dir
Loop

Admin · Answer

Seems you are right. It's unbelievable for me that somebody could solve it, thank you very much for your idea. I just tested with all problems I mentioned in the post and I see exactly these short names are making the problems.

Now my only task is to find out how can I avoid these, but your idea seems legitim, I just have to check last character manually.

VBA: WildCard * - find a file ending to a digit - finds the wrong file

2 Answers