Identifying last file of the week

Dr. Demento

Well-known Member
Joined
Nov 2, 2010
Messages
579
I've got a couple of years worth of files (last files of the day), but I'd like to narrow that down to the last file of the week. I can input all the file names into an array but I'm at a loss for how to 1) identify all the files within a week, 2) identify the last file (starting at Saturday 11:59 pm and working backwards until it encounters the "first"/last file of the week, 3) deleting the remaining files within that week, and then 4) moving onto next week.

Here's the code for identifying the last file of the day. If this can be adapted, that's cool; if a different approach is necessary, I'm happy to go that direction as well. My principal concern is speed, since I'm going thru probably seven years worth of files (+2500 files).

Thanks, y'all.

VBA Code:
Private Sub delete_PERSONALbackup()
' ~~ Delete files in folder; keep last file of each day
' http://chandoo.org/forum/threads/delete-files-in-folder-keep-last-file-of-each-day.31442/#8
' #20160930#

Dim dict As Object
  Set dict = CreateObject("Scripting.Dictionary")

Dim arrFileName As Variant, _
    var As Variant

Dim strDeletePath As String
Dim strKey As String, _
    strItem As String
  startXLpath = Application.StartupPath
 
Dim cntr As Long

If startXLpath = deskXLpath Then
    bakPath = deskArchivePath
  ElseIf startXLpath = lapXLPath Then
    bakPath = lapArchivePath
  ElseIf startXLpath = homeXLpath Then
    bakPath = homeArchivePath
  End If

  strDeletePath = bakPath & "To be deleted\"
' exist_folder strDeletePath, True  ' ~~ Test if folder for deleted files exists; if it does not, make folder
 
  On Error Resume Next
 
  arrFileName = arr_FileList(bakPath)  ' ~~ Create array of filenames found in source folder
 
  With dict
    For Each var In arrFileName
      If right(var, 4) = ".bak" Then
        strKey = Split(var, "_")(1)
        strItem = .item(strKey)
       
        If var > strItem Then
          .item(strKey) = var
          If strItem > vbNullString Then
            FileCopy bakPath & strItem, strDeletePath & strItem  ' ~~ Copy file to folder for later deletion
            Kill bakPath & strItem  ' ~~ Delete file
            cntr = cntr + 1
          End If
        Else
          Debug.Print var; " : moved"
        End If
      End If
    Next var
   
    Debug.Print vbNewLine; "There are"; .Count; "end-of-day files || " & cntr & " files deleted"  ' ; vbNewLine; Join(.Items, vbNewLine)
    .RemoveAll  ' ~~  Empty dictionary
  End With
 
  On Error GoTo 0
End Sub
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Watch MrExcel Video

Forum statistics

Threads
1,127,861
Messages
5,627,315
Members
416,239
Latest member
Counselor85027

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top