VBA to delete files in a file path

Hi phbryan,

Here is some code that goes through a list of files in a folder and only keeps the 50 most recently saved. The variables "nPath", "extFile" and "LimitFile" are set based on your description above. "nPath" is the directory where the backups reside, "extFile" is the file extension, in this case ".mdb" and "LimitFile" is the number of files to keep, which is 50 based on your description. You can change these variables as necessary if your specs are different than what I assumed.

The program iterates through all the files and deletes the oldest one each time, so if you have 500 files and are keeping 50, it might take it a while to run. If there are only about 50, it should take only a few seconds. Here is the code:

Sub DeleteOldFiles()

Dim extFile As String
Dim LimitFile As Long
Dim cFile As Long
Dim nPath As String
Dim nFile As String
Dim objFso As Object
Dim objFiles As Object
Dim objFile As Object
Dim delFile As String

' Set Base Directory
nPath = "S:\AC 174 Railing Certification Program\Database Backups\"

' Set Limit of Files in directory
LimitFile = 50

' Get File Extension
extFile = ".mdb"

'Get Count of files in directory
cFile = 0
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objFiles = objFso.GetFolder(nPath).Files
For Each objFile In objFiles
   If (InStr(objFile.Name, extFile) > 0) Then cFile = cFile + 1
Next

If (cFile > LimitFile) Then
   For i = 1 To (cFile - LimitFile)
      nFile = Dir(nPath & "Backup of dbExtrusion Receiving " & "*" & extFile)
      delFile = nFile
      Do While (nFile <> "")
         If (FileDateTime(nPath & delFile) > FileDateTime(nPath & nFile)) Then delFile = nFile
         nFile = Dir()
      Loop
      ' Delete oldest file
      Kill nPath & delFile
   Next i
End If

' Clear object variables
Set objFile = Nothing
Set objFiles = Nothing
Set objFso = Nothing

' Give completion message
MsgBox "Process Complete."

End Sub

Hope that helps,

Dan