PDA

View Full Version : [SOLVED] Check the creation date of a file and delete a file when older then...



Regouin
05-12-2005, 06:49 AM
Is there a way to programatically check the creation date of a file and when this creation date is older then say 3 months have the file deleted?
I now have a backup routine built in my program which backs up the program once every month but everytime this creates a file with the current date included in the filename, now this builds up over time and is there a way that i can have it check for the creation dates of the files (or in the filename for that matter) and have it delete all the files older then 3 months?

tia

here is the backup routine i made:


Option Explicit

Private Sub backup()
Dim fsoObj As Object
Dim Fs As Object
Set fsoObj = CreateObject("Scripting.FileSystemObject")
Set Fs = CreateObject("Scripting.FileSystemObject")
If (Day(Now())) > "23" Then Exit Sub
With fsoObj
If .FolderExists("G:\techniek\backup\") Then
Else
.CreateFolder ("G:\techniek\backup\")
End If
End WithActiveWorkbook.SaveAs ("G:\techniek\backup\Backup Onderhoudsprogramma van " & Date & ".xls")
End Sub

Bob Phillips
05-12-2005, 07:11 AM
... is there a way that i can have it check for the creation dates of the files (or in the filename for that matter) and have it delete all the files older then 3 months?

Here is one way



Private Sub backup()
Const kDir As String = "G:\techniek\backup\"
Dim fsoObj As Object
Dim Fs As Object
Set fsoObj = CreateObject("Scripting.FileSystemObject")
Set Fs = CreateObject("Scripting.FileSystemObject")
If (Day(Now())) > "23" Then Exit Sub
With fsoObj
If .FolderExists(kDir) Then
KillOldFiles fsoObj.getfolder(kDir)
Else
.CreateFolder (kDir)
End If
End With
ActiveWorkbook.SaveAs (kDir & "Backup Onderhoudsprogramma van " & Date & ".xls")
End Sub

Private Sub KillOldFiles(fldr As Object)
Dim dteCreated As Date
Dim oFolder As Object
Dim oFile As Object
For Each oFile In fldr.Files
dteCreated = oFile.DateCreated \ 1
If dteCreated < DateSerial(Year(Date), Month(Date) - 3, Day(Date)) Then
Kill oFile.Path
ActiveWorkbook.Close savechanges:=False
End If
Next oFile
End Sub

Regouin
05-12-2005, 11:26 PM
Great i got it, I only replaced saveas by savecopyas because a saveas command will make you work in the newly saved workbook afterwards and I dont want that.

thanks again

Ken Puls
05-12-2005, 11:39 PM
As an aside, I'm curious on something here...

xld, do you know if the DateCreated property you are using here is read only? I can't find it in the object browser to check. It doesn't affect this routine at all, but I saw someone else ask if they could modify those dates using VBA.

Bob Phillips
05-13-2005, 02:25 AM
xld, do you know if the DateCreated property you are using here is read only?

It most certainly is read-only. It would be a bit odd if you could change the date created on a file to a value that was not the date created.


I can't find it in the object browser to check

Did you set a reference to the Microsoft Scripting Runtime library? The OP used late binding so that was not necessary for the solution, but is necessary to see it in the OB. In there, you will see it under Scripting>File 2nd property (3rd item) down.

You can also find details here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/jsprodatecreated.asp

Glaswegian
05-13-2005, 04:55 AM
Ken

See here
http://www.mrexcel.com/board2/viewtopic.php?t=89249&highlight=
for some discussion on changing file properties, such as DateLastModified etc. Not exactly what was posted here, but related nonetheless.

Regards