Dir function returns fileNames not as expected

Hi everybody,

This came up while testing code for the thread "Incremental number for saved files".
I find this to be too generic by itself though, so I'm opening this thread.

Here we go: In order to have the Dir function return files, a path and a pattern can be specified as the function's parameter.
e.g. strFile = Dir(strPath & "\" & "VBAX" & "*")

Fine.

Now I expect this function to return all files from the specified path that start with "VBAX", right? e.g. 'VBAX.xls', 'VBAXall.txt', 'VBAX Forum.doc' and so on.

I also expect it to ignore files like 'VB AXIS.xls', 'V B A X.doc' because they just don't match the pattern I entered, right?

And how about 'VB A X.txt' for crying out loud (6 spaces and 2 spaces here, it doesn't show because the forum's text editor cuts off extra spaces but that's what I entered in this filename)!!!
It should definitely be ignored RIGHT?

WRONG!

I'm out of words here. Your comments (pretty please)...

Interesting.
Testing with two similar named files, Test.xls and Test.xlsx returned different results depending on the number of spaces inserted in the same location.

Malcolm, I know for sure that I'll be double cautious when using this function from now on.

My tests so far have not produced different results depending on the number of spaces. The function is pretty consistent in absolutely ignoring spaces within the 'root' name ('VBAX' in my example, 'Test' in yours).
I haven't tested different extension lengths so far, though...

I'll keep testing. (I' m using Excel 2003)

Refhrasing previous post#3 (to avoid misunderstandings):
The function seems to be pretty consistent in absolutely ignoring spaces found within the 'root' name of the disk files. (the 'root' name entered in the function's parameter consists of consequtive letters e.g. 'VBAX')

Dir Function's behavior update

Example: Dir(strPath & "\" & "Data" & "*")

I tested the effect of the "*" wildcard on the preceding string "Data".

It seems that the function is stripping the disk filename of all spaces and dots (".") and looks for 'Data' to be the leading four characters. If successful it returns the file.

You can check it by creating a file Da.ta.xls

Amazing... It returns Da.ta.xls as well, when I ask it for the Data* files...

I'm calling Bill... I bet HE doesn't know either...

Could be that we have something BIG here...

Some more...

Update on the use of the * wildcard

So far all tests confirm what I noted in my post #5.

----------------------------------------------

Update on the use of the ? wildcard

? wildcard will pick any character in the filename INCLUDING THE SPACE (as it should) EXCEPT FOR THE DOT "." (pretty evasive this little bugger...)

Example filenames:
Da.ta.xls
Da,ta.xls
Da ta.xls (a space separates 'da' from 'ta')

Function: Dir(strPath & "\" & "Da?ta" & "*")
will NOT return Da.ta.xls (it WILL return the other two files)

In order for the function to trace the dot and pick Da.ta.xls we will have to explicitly enter the dot in the function's parameter Dir(strPath & "\" & "Da.ta" & "*").

Care for more? I don't have any... That's just about enough for me for the time being...
Have you called Silicon Valley yet?

What happened to this? I have problem with this now, 12 years later... My word macro mixes up files. In the document I reference the file with three digits, e.g. PlotRef = 123 and use

Code:

---

fileName = Dir(plotLocation & "\*" & plotRef & "*.*")

---

The filename ends up being "<Some path on my computer>\012 30-1000MHz.rtf", totally ignoring the space between the 2 and the 3. I expected it to choose the file "123 150k-30M Rear.rtf".