Delete old files, keep the newest file

[ghudson]

I have tried searching on this quest but I can not find exactly what I need. If I missed a post please reply with the link and I will go from there.

I need to delete the oldest files in a directory and keep the newest file for each file type. I have code to delete files but the twist is I need to keep the newest file for each type of file. The type is determined by the file name prefix, not the file extension and I will have six different types of files. The files are automatically downloaded from a system. Each file type has a specific file name prefix, the system assigns the process number to the file name type which is the series of six numbers just before the .xls file extension. Here is an example of the file in the directory...

XYZ_123_A-258743.xls [file date/time = 07/08/2005 13:42:21]
XYZ_123_A-345782.xls [file date/time = 07/08/2005 09:24:36] *
XYZ_123_A-154952.xls [file date/time = 07/08/2005 11:12:45] *
ABC_987_Z-654785.xls [file date/time = 07/08/2005 08:36:14] *
ABC_987_Z-742589.xls [file date/time = 07/08/2005 10:05:42] *
ABC_987_Z-875123.xls [file date/time = 07/08/2005 14:30:54]

Based on the above example, I would need the four files with an asterisks * deleted so that the following two files are left in the directory because they have the newest modified date/time stamp.

XYZ_123_A-258743.xls [file date/time = 07/08/2005 13:42:21]
ABC_987_Z-875123.xls [file date/time = 07/08/2005 14:30:54]

Does anybody already have a rountine that will accomplish what I want? Thanks in advance for your help!

 

[modest]

Hudson... I'm shocked.. This doesn't seem like it should be a problem for you

If you're using LDAP, just tweak your Query using the WHERE clause
If you're doing it manually, in your loop search for the newest file, then loop through and delete all files where the time does not equal that file timestamp.

If you want me to do it for you, i'll look into it when I get home, it's been a few months since i've looked at the File System Object functions, but that's the pseudo code above.

 

[ghudson]

Loops and arrays are my weak point! I have been messing with this all day and I have made no progress. The filesystemobject is the way to go I know but I can not figure out how to exclude the newest file based on the file name prefix and delete all the older duplicate files. Beggars can not be choosy. Thanks in advance for any assistance!

 

[modest]

Try this out:

Public Function Temp()
    Dim fsoFile         As File
    Dim fsoFolder       As Folder
    Dim dOldDate        As Date
    Dim strFileName     As String
    Dim fso             As FileSystemObject
    
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fsoFolder = fso.GetFolder("C:\Path of Folder\")

    For Each fsoFile In fsoFolder.Files
        Select Case Left$(fsoFile.Name, 10)
            Case "XYZ_123_A-"
                If DateDiff("s", dOldDate, fsoFile.DateCreated) > 0 Then
                    dOldDate = fsoFile.DateCreated
                    strFileName = fsoFile.Name
                End If
        End Select
    Next

    Set fsoFile = Nothing
    Set fsoFolder = Nothing
    Set fso = Nothing
    
End Function

[COLOR=Green]'After loop is done strFileName will hold the newest file in the folder 
'for files beginning with "XYZ_123_A-"
'dOldDate will hold the date it was created[/COLOR]

I didn't fully test it, so try it out and make it work with what you need.

 

[WayneRyan]

G & Modest,

Couldn't resist tinkering with this a bit. I don't know that a single traversal
of the directory can provide enough info; you can't sort by Left(Filename,10)
AND then by date descending within each "group".

I just threw all of the info into a table and let Access handle it.

The attached sample will create (and work in) the folder: "C:\tmpAAA".
If you already have one of those ... I don't know what would happen.

Anyway, I got a bit carried away, but I hope you find "some" of it useful.

Wayne

 

[modest]

you can't sort by Left(Filename,10) AND then by date descending within each "group"

You're not sorting. He wants to delete all the files that aren't the newest. Therefore, create an algorithm that does what i have done to find the newest.

What I suspect that you're suggesting is that you load all the file information into a table so that you can find the file quicker by loading the information into memory and using queries, which would work as well. Again there are many ways to handle this problem.

The process for my code above is as follows:
1)It loops through each file in the folder
2a)If the name of the file matches the name of the file you want to import then it sees if the date is the newest
2b)If it is the newest it stores the new date and file name
3)After looping through the whole directory, you'll either have the newest file name/date stored in your variable (or you wont have anything because there were no file names that began with what you were looking for)..

After you have the newest file name you can do whatever you want. Go back through and delete the other ones if that's what you want.

 

[modest]

There are different ways to compare dates too... I think you may use the "<" or ">"