Delete Old Files Using VBA

I am having trouble making the code below work.  An old co-worker wrote the code, but I don't think I have the knowledge to figure out what is wrong.

The code stops at this line in the function stating an object is required.  I can verify that the folderName is correct when I scroll over it.
Set folder = fso.GetFolder(folderName)

What this is supposed to do is go out to the starting location and delete all files in the subfolders where the file was modified more more than 10 days ago, but excluding the files and folders listed in the array.

This code is being called using a command button within an Access 2007 database, and as far as I can tell, it has never worked.

Your help in getting this workin is greatly appreciated.
Public Sub DeleteFiles()

' Create a FileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject")
' Enter your folder to start deleting files from
startFolder = "C:\Users\Public\aLXE-Pricing\"
' Enter the names of folders to exclude from checking:
arrFoldersToExclude = Array("Guidelines", "Layout", "Masters", "Template", "Templates", "aGuidelines", "adjustments from xle", "adjustments from lxe", "merge", "guides", "zzCleanup")
arrFilesToExclude = Array("AFRwholesale", "ACMG", "BayEquity", "BBT", "1-BoAw", "Conforming", "Government(FHA_VA_FHAJumbo)", "Non-Conforming Jumbo", "2-BoAw", "3-BoAw", "4-BoAw", "5-BoAw", "Carnegie", "Emigrant", "sumnetbroker", "DenverRateSheet", "JacksonvilleRateSheet", "famc_ratesheet", "Fifth-Third", "FPF Wholesale NoCal", "Gateway", "CLC Rate Sheet", "clrates", "HomeSavings", "Jmtg", "Eastern", "LibertyMtg", "Metlife", "Metlife3", "Sac.Broker", "NYWC", "Metlife5", "MI HLS", "MI HLS27", "MI HLS27hb", "MI HLS28", "NetMore", "NewLine", "PMC Rate Sheet", "Wholesale Rates - PMAC", "Polaris", "Government", "VA", "ProvidentBank", "Branch300", "Sierra", "Sierra2", "Sierra3", "Sovereign", "Santa Rosa Rates1", "Correspondent_MID_Region_Contact", "Nashville_Key_&_A", "Seattle__Key_&_A", "Cranford_L7T_Rate_Sheet", "Portsmouth_Key_&_A", "Terrace", "Supreme")

strFoldersToExclude = ";" & Join(arrFoldersToExclude, ";") & ";"
strFilesToExclude = ";" & Join(arrFilesToExclude, ";") & ";"
' This sets the amount of days old for files to be, before
' they are deleted.  The number must be negative.
OlderThanDate = DateAdd("d", -10, Date)  ' 10 days (adjust as necessary)
' This calls the function that actually deletes the files.
DeleteOldFiles startFolder, OlderThanDate
End Sub
Public Function DeleteOldFiles(folderName, BeforeDate)
      Dim folder, file, fileCollection, folderCollection, subFolder
      ' Get the folder to delete files from
      Set folder = fso.GetFolder(folderName)
      ' Check if the current folder name is in the strFoldersToExclude String
      If InStr(LCase(strFoldersToExclude), ";" & LCase(folder.Name) & ";") = 0 Then
            ' Return a collection of all of the files in that folder
            Set fileCollection = folder.Files
            ' Go through each file....
            For Each file In fileCollection
                  ' ... to check if the DateLastModified value is before
                  ' the minimum age of files to delete.
                  If file.DateLastModified < BeforeDate Then
                        If InStr(LCase(strFilesToExclude), ";" & LCase(file.Name) & ";") = 0 Then
                              fso.DeleteFile (file.Path)
                        End If
                  End If
      End If
      ' Get the next collection of SubFolders to go through
      Set folderCollection = folder.SubFolders
      ' Go through each subFolder
      For Each subFolder In folderCollection
            DeleteOldFiles subFolder.Path, BeforeDate
End Function

Open in new window

Who is Participating?
thenelsonConnect With a Mentor Commented:
Change to:
      Dim folder, file, fileCollection, folderCollection, subFolder, fso

      Set fso = CreateObject("Scripting.FileSystemObject")
      Set folder = fso.GetFolder(folderName)
Try changing these lines:
      Dim folder, file, fileCollection, folderCollection, subFolder
      Set folder = fso.GetFolder(folderName)
      Dim folder, file, fileCollection, folderCollection, subFolder, fso

      Set fso = CreateObject("Scripting.FileSystemObject")
      Set fs = CreateObject("Scripting.FileSystemObject")
      Set folder = fso.GetFolder(folderName)
      Set fso = CreateObject("Scripting.FileSystemObject")
From: Public Sub DeleteFiles()
Helen FeddemaConnect With a Mentor Commented:
Declare a folder variable (don't use the work Folder, as it is a reserved word).  I suggest this:

Dim fld as Scripting.Folder

Then use fld instead of folder as the variable name, and the code should work.
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Helen FeddemaCommented:
It is a good idea to declare all your variables as specific data types.
rsburgeAuthor Commented:
I apologize for the delayed response; I have had a crazy busy morning.

I will make all of the changes suggested and see if it works.  I will try to get it tested today, but it may be tomorrow as I have a meeting that is expected to last the rest of the day.

Thank you!!
rsburgeAuthor Commented:
Thank you for your help!  I apologize it took so long to get it tested.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.