I have within a folder a report worksheet and a macro worksheet tha
create reports on a daily based. Therefore this folder has tons o
reports and I need to add some code to the macro worksheet to delet
reports worksheets that are older that 90 days. This is the code that
believe could do the job; Do you have any suggestions or maybe
different approach?

Dim Filename As String, RecentFile As String, RecentDate As Date
Dim DelFile As String, Directory As String, myDir As String, OldFile A
String

Sub Delete90DaysOldFiles()

myDir = "c:\data\excel\reports"
If Right(myDir, 1) < "\" Then Directory = myDir & "\"
Filename = Dir(Directory & "" & "*.xls", 0)
Do While Filename < ""
RecentFile = Filename
RecentDate = FileDateTime(Directory & Filename)
If FileDateTime(Directory & Filename) < Date - 90 Then '90 days old
OldFile = Filename
End If
Filename = Dir
If OldFile < "" Then
Kill OldFile
End If
Loop

End Su

--
tarzan53
-----------------------------------------------------------------------
tarzan538's Profile: http://www.excelforum.com/member.php...fo&userid=3254
View this thread: http://www.excelforum.com/showthread.php?threadid=52331

Sub Delete90DaysOldFiles()

Dim Filename As String, myDir As String, OldFile As String
Dim DelFile As String, Directory As String,

myDir = "c:\data\excel\reports"
If Right(myDir, 1) < "\" Then Directory = myDir & "\"
Filename = Dir(Directory & "" & "*.xls", 0)
Do While Filename < ""
' next two lines not used
'RecentFile = Filename
'RecentDate = FileDateTime(Directory & Filename)
If FileDateTime(Directory & Filename) < Date - 90 Then '90 days old
OldFile = Filename
End If
Filename = Dir
If OldFile < "" Then
Kill OldFile
End If
' reinitialize OldFile
OldFile = ""
Loop

End Sub

Since you are changing the directory within a DIR loop, you could have
problems, but as long as you don't then it appears like it should work.


You might want to make a backup of your directory until you are sure it is
working correctly.
--
Regards,
Tom Ogilvy


"tarzan538" wrote
in message ...

I have within a folder a report worksheet and a macro worksheet that
create reports on a daily based. Therefore this folder has tons of
reports and I need to add some code to the macro worksheet to delete
reports worksheets that are older that 90 days. This is the code that I
believe could do the job; Do you have any suggestions or maybe a
different approach?

Dim Filename As String, RecentFile As String, RecentDate As Date
Dim DelFile As String, Directory As String, myDir As String, OldFile As
String

Sub Delete90DaysOldFiles()

myDir = "c:\data\excel\reports"
If Right(myDir, 1) < "\" Then Directory = myDir & "\"
Filename = Dir(Directory & "" & "*.xls", 0)
Do While Filename < ""
RecentFile = Filename
RecentDate = FileDateTime(Directory & Filename)
If FileDateTime(Directory & Filename) < Date - 90 Then '90 days old
OldFile = Filename
End If
Filename = Dir
If OldFile < "" Then
Kill OldFile
End If
Loop

End Sub


--
tarzan538
------------------------------------------------------------------------
tarzan538's Profile:
http://www.excelforum.com/member.php...o&userid=32544
View this thread: http://www.excelforum.com/showthread...hreadid=523312

Thank you for your feedback

--
tarzan53
-----------------------------------------------------------------------
tarzan538's Profile: http://www.excelforum.com/member.php...fo&userid=3254
View this thread: http://www.excelforum.com/showthread.php?threadid=52331