MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Works one minute, doesn't the next?


Posted by billyjoel on October 02, 2001 4:37 AM

Why is it that this piece of code worked one minute but I can't seem to get it to work anymore?
What's goin on?


Sub FileNumbering()

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


Posted by Damon Ostrander on October 17, 2001 4:31 PM

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