dir function returns not always implicit extension

[bartw]

Hello,

excel 2010 works with implicit extensions (.xlsx)
When I run a macro on my PC,
the following
<<
fName = Dir(wsDir & wsFile & "*")
Do While Len(fName)
>>
gives back the required files with implicit extension .xlsx.

Unfortunately when a user runs the macro it does not find the required files.


When I change it to
<<
fName = Dir(wsDir & wsFile & ".*")
Do While Len(fName)
>>
it does work on the PC of the user, but not on my PC.


Is there a general setting which provokes this ?

[patel45]

I don't know the content of wsDir and wsFile

[bartw]

it is a folder which contains excel 2010 documents (so with implicit file extension .xlsx)

[patel45]

I can not help you if you don't want help me

[bartw]

I try to explain with an example

So wsDir contains the netwerk path and wsFile contains the name without extentions.
To avoid avoid testing on full extension .xls or .xlsx or ..., I used the syntax Dir(wsDir & wsFile & ".*")

On my PC it did not work. It only works when I change to "*", then it returns all the documents which are in the new windows7 explorer, which works with implicit extension (so you do not see the extension), whereas in the old windowsxp explorer all documents were with an explicit extention..

But on the PC of a user which also has windows7, it only works with syntax Dir(wsDir & wsFile & ".*")

=> Therefor my question what makes that when you run a macro on a PC you have to use syntax "Dir(wsDir & wsFile & ".*")" and on other PC you have to use "Dir(wsDir & wsFile & "*") and this for the same documents both PC's already with windows7

[JasperD]

Hi Bart,

why not use this :

Please Login or Register  to view this content.

Please click the * below if this helps

[bartw]

Yes, this could be an alternative way to solve this, search through all the files in the folder and use the one which has a name to the one I need. I could also test if nothing found with the first syntax (".*), use then the second one ("*")

But I want to understand why the macro reacts differently

[JosephP]

explorer in both windows 7 and xp had the option to show or hide extensions-by default they are hidden in both. perhaps one machine has them hidden and the other does not

[bartw]

Thanks for that.
I didn't know that it aleady existed in windows xp. So probably there the default value was with explicit extension, and in windows7 with implicit.

It stays still a mystery to me because for both PC's it had the same default value, so implicit extension.

And to make it stranger, now the macro run on my PC reacts in the same way as the user so with ".*".

[Leith Ross]

Hello bartw,

The Dir function in VBA will return all files in a folder if no file extension is given in the first call of Dir. To return files of of a given type, you should always include the file extension.

Here is an example of a properly formed Dir statement to return all xlsx files in the folder wsDir.

Please Login or Register  to view this content.

[bartw]

Hi, I'm working with a name (cf. Filename in the syntax), so my purpose was to make it work for files of type .xls / .xlsx or whatever extension may be used in the future.

So the result was that I do retrieve normally 1 file (although for some time I did not retrieve any)

I still do not understand, but probably it has more to do with windows settings/what is in your registry than with vba/excel so I'll close this

[bartw]

[solved] ;;;;;;;;;;;;;