How to change closed file name - Error: file not found

[PGM]

Hello,

I've been trying to write code in Excel 2003 to change the name of a
closed file. This is to prevent a new version of a form from
overwriting the old file. I used the code provided by Tom Ogilvy back
in 2004 (in addition to another function whose author name I don't
currently have handy), but every time I try to run the macro, I get an
error message that the file is not found. An example of my code is
shown below:

MyState, MyAgyNum, MyYear and MyWorkbook have been previously defined
via userform.

' File name.
sName = "S:\Dept\GROUP\CITY\STATE\Auto Agency Reports\" & MyState &
" Auto Reports\" & MyAgyNum & "\" & MyYear & "\VERSION\" & MyWorkbook &
".xls"
If Dir(sName) <> "" Then

' Get properties with Function: File_Created_Info.
MyCreated =
File_Created_Info("S:\Dept\GROUP\CITY\STATE\Auto Agency Reports\" &
MyState & " Auto Reports\" & MyAgyNum & "\" & MyYear & "\VERSION\" &
MyWorkbook & ".xls")

' Identify Date and Time.
MyDate = Left(MyCreated, InStr(MyCreated, " ") - 1)
MyTime = Format(Right(MyCreated, Len(MyCreated) -
InStr(MyCreated, " ")), "hh:mm;@")

' Format time for file name.
MyTime = Left(MyTime, 2) & "." & Right(MyTime, 2)

' Identify file to change.
oldfile = "S:\Dept\GROUP\CITY\STATE\Auto Agency Reports\" &
MyState & " Auto Reports\" & MyAgyNum & "\" & MyYear & "\VERSION\" &
MyWorkbook & ".xls"

' Identify if existing file created on current date.
If Format(Date, "m-d-yy") = MyDate Then
NewFile = "S:\Dept\GROUP\CITY\STATE\Auto Agency
Reports\" & MyState & " Auto Reports\" & MyAgyNum & "\" & MyYear &
"\VERSION\" & MyWorkbook & " (" & MyDate & " " & MyTime & ").xls"
Else:
NewFile = "S:\Dept\GROUP\CITY\STATE\Auto Agency
Reports\" & MyState & " Auto Reports\" & MyAgyNum & "\" & MyYear &
"\VERSION\" & MyWorkbook & " (" & MyDate & ").xls"
End If

' >>>>>>>This is what fails every time.<<<<<<<
Name oldfile As NewFile

Thanks in advance for any help you can provide.

 

[Doug Glancy]

PGM,

NewFile is an Excel reserved word (I think) so I suppose that cause a
problem.

Also, a shot in the dark. Is it possible that MyWorkbook already includes
an ".xls" extension, so that you are specifying something like
"MyWorkbook.xls.xls"?

What does the whole NewFile string evaluate to?

hth,

Doug

 

[Tom Ogilvy]

The problem appears to be the ":" on the end of your ELSE statement.
Remove it. Here is some culled down code that takes advantage of the fact
your are always working in the same directory:


Dim s as String, sName as String
' File name.
s = "S:\Dept\GROUP\CITY\STATE\Auto Agency Reports\" & _
MyState & " Auto Reports\" & MyAgyNum & "\" & _
MyYear & "\VERSION\"

sname = s & MyWorkbook & ".xls"
If Dir(sName) <> "" Then

' Get properties with Function: File_Created_Info.
MyCreated = File_Created_Info(sName)

' Identify Date and Time.
MyDate = Left(MyCreated, InStr(MyCreated, " ") - 1)
MyTime = Format(Right(MyCreated, _
Len(MyCreated) - InStr(MyCreated, _
" ")), "hh:mm;@")

' Format time for file name.
MyTime = Left(MyTime, 2) & "." & Right(MyTime, 2)

' Identify file to change.
oldfile = sName

' Identify if existing file created on current date.
If Format(Date, "m-d-yy") = MyDate Then
NewFile = s & MyWorkbook & _
" (" & MyDate & " " & MyTime & ").xls"
'
' fixed next line
'
Else
NewFile = s & MyWorkbook & _
" (" & MyDate & ").xls"
End If

name OldFile as NewFile

End if

 

[PGM]

Doug,
Thanks for the suggestion. I changed NewFile to MyNew File.

Tom,
I appreciate the suggestions. I removed the colon from the ELSE
statement, in addition to changing the variable name as suggested by
Doug. Alas, I still get Error 53: File not found when I step into
Name MyOldFile As MyNewFile.

I am open to any other suggestions, else I'll need to modify the code
to open the old file, save it with the new information then kill the
old file.

Thanks again!

Paul

 

[Tom Ogilvy]

I created you directory structure (using my own values for your variable
values) and this worked for me. It appears you think you are formatting
your date as m-d-yy, but you are not. It was being formatted with "/"
with is an illegal character.

Anyway, as I said, it worked for me.

Sub AAA()
Dim s As String, sName As String, t As String
MyAgyNum = "D199"
MyState = "Virginia"
MyYear = 2005
MyWorkbook = "AA_Paul"

' File name.
s = "D:\Dept\GROUP\CITY\STATE\Auto Agency Reports\" & _
MyState & " Auto Reports\" & MyAgyNum & "\" & _
MyYear & "\VERSION\"

sName = s & MyWorkbook & ".xls"
If Dir(sName) <> "" Then


' Get properties with Function: File_Created_Info.
MyCreated = File_Created_Info(sName)

' Identify Date and Time.
MyDate = Left(MyCreated, _
InStr(MyCreated, " ") - 1)
MyTime = Format(Right(MyCreated, _
Len(MyCreated) - _
InStr(MyCreated, _
" ")), "hh:mm;@")

' Format time for file name.
MyTime = Left(MyTime, 2) & "." & _
Right(MyTime, 2)
' Identify file to change.
Oldfile = s & MyWorkbook & ".xls"

' Identify if existing file created on current date.
If CDate(Date) = CDate(MyDate) Then
NewFile = s & MyWorkbook & _
" (" & MyDate & " " & MyTime & ").xls"
Else
NewFile = s & MyWorkbook & _
" (" & MyDate & ").xls"
End If

NewFile = Application.Substitute(NewFile, "/", "-")
MsgBox Oldfile & "-" & NewFile
Name Oldfile As NewFile


End If

End Sub

 

[PGM]

Tom,

Thanks for your solution. I thought I had covered all the bases, but
that "/" slipped right past me. I just ran a trial and it does
everything I wanted. I appreciate your taking the time to fine tune
this for me.

Paul