• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Delete files in folder; keep last file of each day

Dr. Demento

Dr. Demento

Member
I posted this at Mr. Excel but no one was able to provide what I was looking for. I'm hoping that folks here might have some ideas.

I create a timestamped backup of my Personal.xlsb (filename & "_yyyymmdd_hhss") everytime I save it. As you can imagine, this leads to a lot of versions each day. Sometimes I need to go back a few hours within the day, so creating the backups using the date only doesn't work.

Could someone give me some pointers how I might loop thru all files within a specified folder, and deleting all files EXCEPT the last saved file of each individual day. I'd likely run this once a week, so I keep the number of files manageable.

I'm not looking to immediately delete all files at the end of each day; instead, I would run this once a week. Given this, the sub would need to cycle thru each individual date, identify the last time for each date, and delete (or move; probably safer than outright deleting) the other files within that date.

For example, if the following files were in a folder:

Personal.xlsb_20160914_1023.bak
Personal.xlsb_20160914_1700.bak
Personal.xlsb_20160915_1156.bak
Personal.xlsb_20160915_1444.bak
Personal.xlsb_20160915_1603.bak

All files except the two below would get moved to another folder:

Personal.xlsb_20160914_1700.bak
Personal.xlsb_20160915_1603.bak

I would imagine that it would consist of two loops - the outside loop to cycle thru the date portion of the filename and the inside loop to cycle thru the times within that date. Would the inner loop use Step -1 (start at the last time, and move/delete any time less than the daily max time)? I just can't figure out how to construct loops that look in the middle of a string and evaluate/discriminate.

Any thoughts would be most appreciated. Thanks y'all.
__________________________________________________________________
Mod edit : thread moved to appropriate forum !
​
 
Hi Demento,

You can try enclosed file.

Dr. Demento said:
I posted this at Mr. Excel but no one was able to provide what I was looking for. I'm hoping that folks here might have some ideas.

I create a timestamped backup of my Personal.xlsb (filename & "_yyyymmdd_hhss") everytime I save it. As you can imagine, this leads to a lot of versions each day. Sometimes I need to go back a few hours within the day, so creating the backups using the date only doesn't work.

Could someone give me some pointers how I might loop thru all files within a specified folder, and deleting all files EXCEPT the last saved file of each individual day. I'd likely run this once a week, so I keep the number of files manageable.

I'm not looking to immediately delete all files at the end of each day; instead, I would run this once a week. Given this, the sub would need to cycle thru each individual date, identify the last time for each date, and delete (or move; probably safer than outright deleting) the other files within that date.

For example, if the following files were in a folder:

Personal.xlsb_20160914_1023.bak
Personal.xlsb_20160914_1700.bak
Personal.xlsb_20160915_1156.bak
Personal.xlsb_20160915_1444.bak
Personal.xlsb_20160915_1603.bak

All files except the two below would get moved to another folder:

Personal.xlsb_20160914_1700.bak
Personal.xlsb_20160915_1603.bak

I would imagine that it would consist of two loops - the outside loop to cycle thru the date portion of the filename and the inside loop to cycle thru the times within that date. Would the inner loop use Step -1 (start at the last time, and move/delete any time less than the daily max time)? I just can't figure out how to construct loops that look in the middle of a string and evaluate/discriminate.

Any thoughts would be most appreciated. Thanks y'all.
Click to expand...
 

Attachments

  • GetFiles and Delete.xlsm
    19.5 KB · Views: 13
xlstime,

Thank you for the file. Unfortunately, it' doesn't address my primary issue - how to identify the last file of each day. Since I have almost 1000 files, manually going thru the list is what I hoped to avoid.

I was hoping someone could help me understand how to parse the time/date string in your workbook (or the file property "DateLastModified") so that each can be analyzed separately and I can identify the maximum within each group (where files are grouped by date and the maximum of interest is the timestamp).

Thanks again.
 
Hi, you can use below code to get file name along with last modified timestamp.

Hope this solves your problem
Code: 
Sub get_file_names()


    Dim FSO As Scripting.FileSystemObject
    Dim SourceFolder As Scripting.Folder
    Dim FileItem As Scripting.File ' Please add "Microsoft Scripting Runtime" Library

    SourceFolderName = "C:\Desktop\foler1\sub_folder\rawfiles" ' update the path before executing macro
   
    Set FSO = New Scripting.FileSystemObject
    Set SourceFolder = FSO.GetFolder(SourceFolderName)

    Range("A1:C1") = Array("File_Name", "Path", "Date Last Modified")

    i = 2
    For Each FileItem In SourceFolder.Files
        Cells(i, 1) = FileItem.Name
        Cells(i, 2) = FileItem
        Cells(i, 3) = FileItem.DateLastModified
        i = i + 1
    Next FileItem

    Set FSO = Nothing
   
End Sub
 
I appreciate the input, but I guess I haven't made my need very clear. I am NOT in need of code to extract the filenames (although that is helpful in its own right). Instead, I'm looking for a way to identify the maximum with each group (latest time in each day)

Tushar provided a method to find the MAXIMUM within each grouping, but I could use a hand understanding/adapting it for use in VBA.

Code: 
{=IF(MAX(IF($C$2:$C$41=C2,$D$2:$D$41))=D2,"max","")} <-- CSE

Where:
column C is the date
column D is the time
(Tushar Mehta)

Thanks y'all.
 
Dr. Demento said:
how to construct loops that look in the middle of a string and evaluate/discriminate.
Click to expand...
Warming a couple of neurones, only one loop is necessary
to move older files as you can see running this simulation
(see result in VBE Immediate window) :​
Code: 
Sub Demo()
        Dim SP$, TE$, V
    With CreateObject("Scripting.Dictionary")
        For Each V In [{"Personal.xlsb_20160914_1700.bak","Personal.xlsb_20160914_1023.bak","Personal.xlsb_20160915_1156.bak","Personal.xlsb_20160915_1444.bak","Personal.xlsb_20160915_1603.bak"}]
                   SP = Split(V, "_")(1)
                   TE = .Item(SP)
            If V > TE Then
                .Item(SP) = V
                If TE > "" Then Debug.Print TE; " : moved"
            Else
                Debug.Print V; " : moved"
            End If
        Next
             Debug.Print vbLf; "The"; .Count; "last files :"; vbLf; Join(.Items, vbLf)
            .RemoveAll
    End With
End Sub
Do you like it ? So thanks to click on bottom right Like !
​
 
Marc L,

Thank you so much! That works perfectly!

Could you help me understand how it works? I'm not familiar with scripting dictionaries. I understand the array loop and the SPLIT function (I wasn't aware of the limit parameter before) but the .ITEM(SP), JOIN(.ITEMS), .REMOVEALL, and tests you use in the IF/THEN are unfamiliar to me.

Thanks again! Great stuff!!!
 
Dictionary object (Windows only) works with a couple Key / Item,
here the date as key and the file as item …
See VBA help for this object or on MSDN website.
(A VBA inner Collection works too for MAC/Windows users.)

You can use VBA Dir function to list files within a loop
(see example in VBA help) and move older ones
using Name statement (to see in VBA inner help) …
​
 
You must log in or register to reply here.
Back
Top