Excellent VBA to delete old files SOLVED!!

[Sven62]

Here you go! You can thank some dude over at Ozgrid. Was posted in Jan 2012. Just replace the path and how many days old the files you want to delete. It will delete ALL files in that folder that are too old.

On a side note... can someone please tell me what that little circle is before "FSO" in the second code? I've never seen those before.

Sub DeleteOldFiles() 
    Set FSO = CreateObject("Scripting.FileSystemObject") 
    For Each fcount In FSO.GetFolder(ThisWorkbook.Path & "\Backups\ ").Files 
        If DateDiff("d", fcount.DateCreated, Now()) >7 Then 
            Kill fcount 
        End If 
    Next fcount 
End Sub

AND..

Sub DelOldFiles() 
     
     
     ''Clear out all files over 7 days old from Dir_Path folder.
     '
    Dir_Path = "C:\Folder\SubFolder\" 
    iMaxAge = 7 ' Set the number of days
     
    Set oFSO = CreateObject("Scripting.FileSystemObject") 
    If oFSO.FolderExists(Dir_Path) Then 'Check that the folder exists
        For Each oFile In oFSO.GetFolder(Dir_Path).Files 
            If DateDiff("d", oFile.DateLastModified, Now) > iMaxAge Then 'Look at each file to check if it is older than 7 days
                oFile.Delete 
            End If 
        Next 
    End If 

    End Sub

 

[MARK858]

In the VBE under Tools - References do you have Microsoft Scripting Runtime checked on the computers it isn't running on.

 

[Sven62]

I dunno! I will check when I get back to work in about two weeks. Thanks for the suggestion. Is it possible the problem is with the Windows 7 setup? Or is this definitely going to be an Excel config issue?

 

[Sven62]

I can't check until Monday, but I checked my personal laptop. The program runs fine and Microsoft Scripting Runtime is not selected.

 

[Sven62]

This turned out to be a folder permissions issue, I think. Anyway problem solved.

BUT.. I was having a different issue on other computers macros getting hung up on Date VBA. Fixed by noticing in Tool>References that one of the references was MISSING. Microsoft Calendar Control 8.0 The file doesn't even use a calendar control anymore so I removed the reference and the file now works fine. So thanks for letting me know about the Tools>References option!

 

[jassi2006]

Here you go! You can thank some dude over at Ozgrid. Was posted in Jan 2012. Just replace the path and how many days old the files you want to delete. It will delete ALL files in that folder that are too old.

On a side note... can someone please tell me what that little circle is before "FSO" in the second code? I've never seen those before.

Sub DeleteOldFiles()
    Set FSO = CreateObject("Scripting.FileSystemObject")
    For Each fcount In FSO.GetFolder(ThisWorkbook.Path & "\Backups\ ").Files
        If DateDiff("d", fcount.DateCreated, Now()) >7 Then
            Kill fcount
        End If
    Next fcount
End Sub

AND..

Sub DelOldFiles()
   
   
     ''Clear out all files over 7 days old from Dir_Path folder.
     '
    Dir_Path = "C:\Folder\SubFolder\"
    iMaxAge = 7 ' Set the number of days
   
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    If oFSO.FolderExists(Dir_Path) Then 'Check that the folder exists
        For Each oFile In oFSO.GetFolder(Dir_Path).Files
            If DateDiff("d", oFile.DateLastModified, Now) > iMaxAge Then 'Look at each file to check if it is older than 7 days
                oFile.Delete
            End If
        Next
    End If

    End Sub

Code is working fine but only when we create a new module and place code in it but when use the same in already created module it ask to define variables for various terms used in this Code

 

[MARK858]

Comment out "Option Explicit" at the top of the code window

 

[jassi2006]

Comment out "Option Explicit" at the top of the code window

I know that Option Explicit is causing error... but I have so a large no of modules in my Excel and for this little code I have to create a new module. please update code with Define variables. I tried to define variable but not working.

 

[MARK858]

Not that the number of modules makes any difference whatsoever....

Sub DeleteOldFiles()
Dim FSO As Object, fcount
    Set FSO = CreateObject("Scripting.FileSystemObject")
    For Each fcount In FSO.GetFolder(ThisWorkbook.Path & "\Backups\ ").Files
        If DateDiff("d", fcount.DateCreated, Now()) > 7 Then
            Kill fcount
        End If
    Next fcount
End Sub

 

[jassi2006]

Thanks

fcount needs to be declared as Variant

Code runs Thanks a lot

 

[MARK858]

fcount needs to be declared as Variant

If you omit the "As Whatever" part when declaring Variables it defaults to Variant so no fcount didn't need to be "declared as Variant" as it already was the way I typed it in the code..

Happy you now have it working.