How to make excel backup checker script get file with newest date

Question

I have a program that creates backups after format: <name>_<date>

Some of them have more backups, some of them have none.

There are kept up to 3 backups with same name but different dates.

I have an excel table that contains names and a script that checks if there is a backup or not, and colors the cell after the result.

I want to improve the script, so it would check if the backup is outdated (older than 30 days).

I have got the idea, but I ran into a problem. If there are multiple backups of the same name, I cant figure out, how to make it compare the newest of them.

the script:

'auto check if backup exists - on click
file = Dir("<filepath>")
Do While file <> ""
    myBool = False
    backup = Right(backup, 6)
        If InStr(file, backup) > 0 Then
            If now - FileDateTime(file) > 30 Then 'this is where I have the problem... how do I make it grab the newest of the backups?
            'outdated 
            End If

            myBool = True
            ActiveCell.Interior.ColorIndex = "35"
            Exit Do
        End If
    file = Dir
Loop
    If Not myBool Then
        ActiveCell.Interior.ColorIndex = "22"
    End If

Are the last six characters always the date? What is the format of the date: yyyy-mm-dd or mm/dd/yyyy or dd.mm.yyyy or yyyyddmm .... Is the date recognized by Excel as such? Did you verify using `IsDate(Right(backup, 6))`? I also believe that you are mixing up two questions: (1) which backup is outdated > 30 days compared to now and (2) which is the latest backup. In order to get an answer to both questions it would be good to have all backups parsed into name and date into an array and then sort it. Then you can get both questions answered. — Ralph, May 20 '15 at 09:44
Yes, the end is always the same. `` ex. `_2015-05-18_09-24-22.zip`. The name is always the same length: 7 characters + "_" I can get the exact date with `FileDateTime(file)` for a single file, but when there are two, I will still get only one result. Tried `msgbox IsDate(Mid(file, 9, 19)`, I get false as result. The question I want to get the answer for looks like: If the file exists: `If InStr(file, backup) > 0` than if the latest file backup older than 30 days? `If now - FileDateTime(file) > 30` Problem is that I can not refer to **latest file** — Divin3, May 20 '15 at 10:08
I believe the code you are looking for can be found (in a modified context) here: http://stackoverflow.com/questions/22305857/vba-loop-through-directory — Ralph, May 20 '15 at 11:43

Divin3 · Accepted Answer · 2015-05-21T09:11:19.333

I have found a way around it using the Dir() function:

'auto check if backup exists - on click

Dim i
i = 1

file = Dir(path)
Do While file <> ""
    myBool = False
    isnew = False
    backup = Right(backup, 6)
        If InStr(file, backup) > 0 Then

            myBool = True
            cfile = Dir(path & Left(c, 7) & "*") 'c is the active cell

            Do While cfile <> ""
                ReDim arr(i)
                arr(i) = FileDateTime(path & cfile)

                If Now - arr(i) < 30 Then
                    isnew = True
                End If

                i = i + 1
                cfile = Dir()
            Loop                

            If isnew = False Then
                ActiveCell.Interior.ColorIndex = "6"
            ElseIf isnew = True Then
                ActiveCell.Interior.ColorIndex = "35"
            End If

            Exit Do
        End If
    file = Dir
Loop
    If Not myBool Then
        ActiveCell.Interior.ColorIndex = "22"
    End If

How to make excel backup checker script get file with newest date

1 Answers1