Using Dir with legacy file suffixes

user_7446760 0
Viewed 50

A recently asked question includes the code:

strFilename = Dir$(fPath & "*.xls")

which is (successfully) used to find files ending xls and xlsx.

My question is, why does this find xlsx files? It doesn't follow the normal rules of wildcard pattern matching. If I had not seen this used this way I would (and have, repeatedly) use the following for files ending xls and xlsx :

strFilename = Dir$(fPath & "*.xls*")

I thought perhaps that pattern matching using Dir$ automatically added the equivalent of a * to the end of the search pattern - but after some testing with "*.xl" I can rule that out. Also, to further muddy things, "*.xls" will not pick up a xlsm or xlsb file.

So this would suggest that perhaps Dir$ has some inbuilt recognition of legacy vs current file suffixes, or an inbuilt list of similarities/synonyms that it should include. But it appears to be one way - as "*.xlsx" will not find a xls file.

Does anyone know if this is the case? Are there others? Are they documented anywhere? When did it change?

Further to that, if I wanted to craft a Dir$ that only found xls files, how might I do that without further checks?

P.S. Dir and Dir$ are interchangeable in this question.

vba excel
1 Answers

Dir in VBA behaves like the Dir-Command on the command shell. And the command shell Dir matches both the long name (that is the name you see in Windows) and the short name. If you never heard about that: see for example https://docs.microsoft.com/en-us/windows/win32/fileio/naming-a-file#short-vs-long-names . Best documentation that I found is https://ss64.com/nt/syntax-wildcards.html - where it simply states "Wildcards match both the Short and Long filename"

The extension for short name of a xlsx-file is xls, that's the reason your Dir finds the files.

As far as I know, there is nothing you can do, you will have to check the real file name manually. See also https://stackoverflow.com/a/66674917/7599798

user_75997980
Related