[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1027
  • Last Modified:

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
            Next
      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
      Next
End Function
'=======================

Open in new window

0
rsburge
Asked:
rsburge
  • 2
  • 2
  • 2
2 Solutions
 
thenelsonCommented:
Try changing these lines:
      Dim folder, file, fileCollection, folderCollection, subFolder
      Set folder = fso.GetFolder(folderName)
To:
      Dim folder, file, fileCollection, folderCollection, subFolder, fso

      Set fso = CreateObject("Scripting.FileSystemObject")
      Set fs = CreateObject("Scripting.FileSystemObject")
      Set folder = fso.GetFolder(folderName)
Remove:
      Set fso = CreateObject("Scripting.FileSystemObject")
From: Public Sub DeleteFiles()
0
 
thenelsonCommented:
Oops
Change to:
      Dim folder, file, fileCollection, folderCollection, subFolder, fso

      Set fso = CreateObject("Scripting.FileSystemObject")
      Set folder = fso.GetFolder(folderName)
0
 
Helen FeddemaCommented:
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.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Helen FeddemaCommented:
It is a good idea to declare all your variables as specific data types.
0
 
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!!
0
 
rsburgeAuthor Commented:
Thank you for your help!  I apologize it took so long to get it tested.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now