How to delete workbooks within an specific folder?

[tarzan538]

I have a macro that creates workbook files on a daily based. I need to be able to delete workbooks that are older than 90 days, within that specific folder. Do you have any suggestions?

 

[Leonard of Quirm]

Tarzan,
Try using the BuiltInDocumentProperties("Last Save Time") property of a workbook, with the DateDiff Function to find out if a workbook is older than 90 days. Then combineit with with the VB command .Kill to delete the file.

See the VBA help files for the details.

Matt

 

[tarzan538]

Matt,

Thank you for your quick response.



Any other suggestions?

 

[tarzan538]

Matt,

How do you feel about this script? Does it look like it will do the job?

Function OldFile(Directory, FileSpec)
' Returns the name of the most recent file in a Directory
' That matches the FileSpec (e.g., "*.xls").
' Returns an empty string if the directory does not exist or
' it contains no matching files
Dim FileName As String
Dim MostRecentFile As String
Dim MostRecentDate As Date
If Right(Directory, 1) <> "\" Then Directory = Directory & "\"
FileName = Dir(Directory & FileSpec, 0)
If FileName <> "" Then
MostRecentFile = FileName
MostRecentDate = FileDateTime(Directory & FileName)
Do While FileName <> ""
If FileDateTime(Directory & FileName) < Date - 90 Then '90 days old
MostRecentFile = FileName
MostRecentDate = FileDateTime(Directory & FileName)
End If
FileName = Dir
Loop
End If
OldFile = MostRecentFile
End Function
Sub Delete90DaysOldFiles()
Dim myDir As String

myDir = "c:\vf-test"
Kill myDir & OldFile(myDir, "*.xls")
End Sub

 

[Leonard of Quirm]

This seems to be doing the trick, but have you tried running this on a directory wih no files older than 90 days? It still deletes the oldest file!

You should try not to use the variable MostRecentFile twice. Pick another named variable to use in its place one of the times and make sure you perform the test on it to see whether to kill the file.

Also, it only kills one file. You'd need to reactivate this until all the files older than 90 days had been deleted.

How about:

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:\"
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
        Filename=Dir
    Loop
    
End Sub

Its a bit sharper, does what is required and the loop means it will keep going until all files older than 90 days have been deleted.

If you want to customise this, you could add a counter which will let you know at the end how many files have been deleted, and if necessary, which files they were (might be good for control purposes).

Hope this helps,

Matt

 

[tarzan538]

Thank you so much.