Works one minute, doesn't the next?

Hi BillyJoel,

The VBA documentation for the Name statement never states what path is assumed for a file if none is specified. One would think it would be the default (working) directory, but I checked that and it isn't, so I suspect that the problem is that it can't find the directory containing the files. And the Dir function doesn't set the default path except for subsequent calls to Dir. So the only way I can see to get it to work consistently is to specify the path explicitly, like this:

Sub FileNumbering()

Path = "U:\data\TestFile\"
strfile = Dir(Path)
Do Until strfile = ""
Name Path & strfile As Path & strfile & "-001"
strfile = Dir
Loop

End Sub

It is also worth mentioning that this will suffix "-001" to the end of the file name INCLUDING the file type, so

MyTestFile.xls

becomes

MyTestFile.xls-001

and now the system will no longer recognize this as an Excel file type. If this is an unintended consequence, and you really wanted the file to be named

MyTestFile-001.xls

then the code would be:

Sub FileNumbering()

Path = "h:\vbaexpert\Test\"
strfile = Dir(Path)
Do Until strfile = ""
Name Path & strfile As Path & left(strfile,Len(strfile)-4) & "-001" & Right(strfile,4)
strfile = Dir
Loop

End Sub