How to delete workbooks within an specific folder?

tarzan538

New Member
Joined
Dec 8, 2005
Messages
5
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?
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Leonard of Quirm

Board Regular
Joined
May 19, 2005
Messages
180
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

New Member
Joined
Dec 8, 2005
Messages
5
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

Board Regular
Joined
May 19, 2005
Messages
180
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:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,535
Messages
5,572,754
Members
412,482
Latest member
arooshrana2
Top