Cycling through files

[Guest]

Hello

I would like to know how can I cycle through all the files in a directory and delete those files that are more than 1 week old
I've created a macro that will copy files into a directory ("C:\backup") and will label the file appropriate with the date in the name ("Backup-26Mar2004"). I would like to have a macro that will delete any such files that the date label is older than seven days but no delete any other potential files that may be in the directory

Thank you

Daniel

 

[Doug Bell]

G'day Daniel,
You can do this in code by referencing the File System Object:
This will need testing but should be close

Sub YourSub()
Dim datCreate As Date, stFolder as string, stFile as string
Dim fso As FileSystemObject, filFile As File, stFindFile as string
dim datKillDate as date

'set up error handling
On Error goto Err_YourSub

'set the date for deleting old files
datKillDate = dateadd("d",-7,Date)
'Set the name of your folder
stFolder = "C:\Whatever\"
'set reference to FSO Object
Set fso = CreateObject("Scripting.FileSystemObject")
'Test folder to make sure it exists
If Dir(stFolder, vbDirectory) <> "" Then
'set the pattern of the file search
stFile = "BackUp-*.*"
' return name of first occurence
stFindFile = Dir(stFolder & "BackUp-*.*")
' start looping through all occurences
Do While stFindFile <> ""
'set reference to file
Set filFile = fso.GetFile(stFolder & "\" & stFile)
'get the creation date of the referenced file
datCreate = stFolder & "\" & filFile.DateCreated
'test for dates earlier than Delete date
if datCreate < datKillDate then
' if earlier delete the file
kill stFolder & "\" & stFile
end if
' return the next file and loop
stFindFile = Dir
Loop
End If


Exit_YourSub:
Exit Sub

Err_YourSub
Msgbox "Error: " & Err.number & "; " & Err.description
resume Exit_YourSub
end sub

Hello,

I would like to know how can I cycle through all the files in a directory

and delete those files that are more than 1 week old?

I've created a macro that will copy files into a directory ("C:\backup")

and will label the file appropriate with the date in the name
("Backup-26Mar2004"). I would like to have a macro that will delete any
such files that the date label is older than seven days but no delete any
other potential files that may be in the directory.

 

[Douglas J. Steele]

Different Doug, but the problem you're encountering is because Doug is using
something that's not part of Access "out of the box".

To be able to use FSO, you need to add a reference to the scripting library.
With any code module open, select Tools | References from the menu bar.
Scroll through the list of available references until you find Microsoft
Scripting Runtime and select it. If you can't find a reference by that name,
look for the file scrrun.dll on your hard drive (it should be in your System
folder). Assuming it's on your hard drive, use the Browse button in the
References dialog to select it.

Having said that, though, and looking closer at Doug's code, you don't
actually need a reference due to how he's invoking the FSO library. Just
change

Dim fso As FileSystemObject

to

Dim fso As Object

and you should be okay.

(If you're interested, when you have a reference like I described above and
you declare something to be an object specific to that reference, it's
called Early Binding. When you don't have the reference, you declare As
Object and you use CreateObject in your code, it's called Late Binding. You
pay a little bit of a performance hit with Late Binding, but it makes you
code much easier to distribute)

HTH