Excel - Date comparision in VBA

[Silencer116]

Hey all,

I got this problem.
Every day i have a Macro creating a logfile, that contains the date of
today.

Like "Test" & Cstr(Date) & ".txt".

This works okay and stuff. But now i would like to have a logfile that
is 7 days or older; deleted.

That made me creating the following:

ExpiredDate = Date - 7
Expired = Date >= ExpiredDate

this would result in:

Kill "H:/Test" & Cstr(Expired) & ".txt"

But this doesn´t seem to work.. Could someone help me?

 

[Ivan Raiminius]

Hi,

Expired seems to me to be boolean false or true, so you get something
like:
Kill "H:/TestTrue.txt" (you attempt to delete "H:/TestTrue.txt").

Regards,
Ivan

 

[Silencer116]

uh that´s not quite what i mean.

i mean like, every day there is a logfile created with the today´s
date.
these files will remain as they are not automatically deleted.

I want a macro to delete logfiles that are older than 7 days, resulting
in that there are always only 7 existing logfiles, from today to 7 days
back.

so when a logfile doesn´t compare with one of these 7 logfiles, it
should be deleted

 

[Bob Phillips]

Are you sure that the CStr(Expired) is creating a date compatible with your
file name. On my system, it returns 25/05/2006 as an example, but filenames
cannot contain /.

Probably best to use

Kill "H:/Test" & Format(Date - 7,"yyyymmdd") & ".txt"

just change the format to how you store them.


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

uh that´s not quite what i mean.

i mean like, every day there is a logfile created with the today´s
date.
these files will remain as they are not automatically deleted.

I want a macro to delete logfiles that are older than 7 days, resulting
in that there are always only 7 existing logfiles, from today to 7 days
back.

so when a logfile doesn´t compare with one of these 7 logfiles, it
should be deleted

 

[Silencer116]

Hey Bob,

Thanks for your reply. The date´s format that is stored is " 24-5-2006
". This is allowed.

But about your statement, does this also deletes the files that are
older than 7 days?
Like, not only the file that is exactly 7 days old, but also files that
could be 9 days , 20 days or even a month old.

 

[Bob Phillips]

No, you need a loop for that. Something like

Sub DeleteOldFiles()
Dim oFSO As Object
Dim oFolder As Object
Dim oFile As Object
Dim dteFile As Date

Set oFSO = CreateObject("Scripting.FileSystemobject")
Set oFolder = oFSO.getfolder("C:\MyTest")
For Each oFile In oFolder.Files
If Left(oFile.Name, 4) = "Test" Then
On Error Resume Next
dteFile = DateValue(Replace(Replace(oFile.Name, ".xls", ""),
"Test", ""))
On Error GoTo 0
If dteFile <> 0 And dteFile <= Date - 7 Then
Kill oFile
End If
End If
Next oFile

Set oFile = Nothing
Set oFolder = Nothing
Set oFSO = Nothing

End Sub


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

Hey Bob,

Thanks for your reply. The date´s format that is stored is " 24-5-2006
". This is allowed.

But about your statement, does this also deletes the files that are
older than 7 days?
Like, not only the file that is exactly 7 days old, but also files that
could be 9 days , 20 days or even a month old.