Possibly something like this. (assumes the files will be sequentially
numbered)


Dim Route as String, ii as Long
Dim bFound as Boolean
Dim sStr as String
dim list(1 to 99) as String
Route = "C:\Data"
Dim Name As String
Dim I As Longr
If Right(Route, 1) < "\" Then Route = Route & "\"
Name = GetName(Now())
If Dir(Route & Name) < "" Then
I = 1
Name = Left(Name, Len(Name) - 4)
Do
sStr = Dir(Route & Name & " Version (" & format(I,"00") & ")" & ".xls")
if sStr < "" then
list(i) = sStr
if not bFound ii = i
bFound = true
elseif sStr = "" and bFound then
exit loop
end if
I = I + 1
Loop while i < 98
Name = Name & " Version (" & format(I+1,"00" & ")" & ".xls"
ActiveWorkbook.SaveAs Filename:=Route & Name, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False, AccessMode:=xlShared
if i = 99 then
msgbox "Out of names"
end if
for k = ii to i - 6
kill Route & list(k)
Next
ActiveWindow.Close


"wayliff" wrote in
message ...

I have a routine that saves files to the network and I'm trying to find
a way to have the macro delete the oldest file if it reaches seven.


Function GetName(Dia As Date) As String
Dim AuxStr As String
AuxStr = Format(Dia, "dd-mmm-yyyy")
GetName = "TC Report - " & AuxStr & ".xls"
End Function

********************

Route = "C:\Data"
Dim Name As String
Dim I As Integer
If Right(Route, 1) < "\" Then Route = Route & "\"
Name = GetName(Now())
If Dir(Route & Name) < "" Then
I = 1
Name = Left(Name, Len(Name) - 4)
While Dir(Route & Name & " Version (0" & I & ")" & ".xls") < ""
I = I + 1
Wend
Name = Name & " Version (0" & I & ")" & ".xls"
End If
ActiveWorkbook.SaveAs Filename:=Route & Name, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False, AccessMode:=
_
xlShared
ActiveWindow.Close

This is how the macro currently works.
What I want it to do is delete the oldest file and save the new
one...by looking at the same name and date or something to the effect.
There are other files that I dont want it to even consider.

Can this be done?

Any kind of help or guidance appreciated.


--
wayliff
------------------------------------------------------------------------
wayliff's Profile:
http://www.excelforum.com/member.php...o&userid=29860
View this thread: http://www.excelforum.com/showthread...hreadid=496139