Check for newer file with date

[phaques]

Howdy!

I'm looking for some direction on how to write code that will check for a newer file. The file format is like this: "NewFile - 11-16-2005.xls". Within my programming already is the update file method that saves changes to the newer date in the filename (NewFile - 11-17-2005.xls).
What I am trying to accomplish is when a person opens an older file, it checks for a newer version and let's the user know this, perhaps even open it for them after a few prompts.
Is this achievable?

Thanks for any and all assistance!

 

[BrianB]

I think this is what you want. As set it checks the folder that the workbook containing the macro is in. Commenting the line indicated will check the current active folder.

Sub OPEN_LAST_FILENAME()
    Dim MyPath As String
    Dim CheckFile As String
    Dim FileDate As String
    Dim FileFilter As String
    Dim CheckDate As Date
    Dim LastDate As Date
    Dim LastFile As String
    '------------------------
    LastFile = " No file matches criteria"
    FileFilter = "*??-??-????.xls"
    LastDate = #1/1/01#  ' initialise variable low value
    MyPath = ThisWorkbook.Path & "\" ' path as this workbook
    '- filter filenames including dates with correct format
    CheckFile = Dir(MyPath & FileFilter)
    '- LOOP
    Do While CheckFile <> ""
        If CheckFile <> "." And CheckFile <> ".." Then
            FileDate = Left(CheckFile, Len(CheckFile) - 4) 'remove .xls
            FileDate = Right(FileDate, 8)   ' get file date string
            CheckDate = FileDate            ' convert to date
            If CheckDate > LastDate Then
                LastDate = CheckDate
                LastFile = CheckFile
            End If
        End If
        CheckFile = Dir    ' Get next entry.
    Loop
    '---------------------------------------------------------------
    '- finish
    rsp = MsgBox("Open file ?   : " _
        & LastFile, vbYesNo, "NEWEST FILE")
    On Error GoTo GetOut
    If rsp = vbYes Then
        Workbooks.Open FileName:=LastFile
    End If
GetOut:
End Sub

 

[phaques]

Thanks for the head start! I cleaned this up a little and added my changes to it. Works like a charm!

Sub OPEN_LAST_FILENAME()
    Dim MyPath As String
    Dim CheckFile As String
    Dim FileDate As String
    Dim FileFilter As String
    Dim CheckDate As Date
    Dim LastDate As Date
    Dim LastFile As String
    '------------------------
    LastFile = " No file matches criteria"
    FileFilter = "* - ??-??-????.xls"
    LastDate = #1/1/2001#
    MyPath = ThisWorkbook.Path & "\"
    MyFile = ThisWorkbook.Name
    CheckFile = Dir(MyPath & FileFilter)
    Do While CheckFile <> ""
        If CheckFile <> "." And CheckFile <> ".." Then
            FileDate = Left(CheckFile, Len(CheckFile) - 6) 'remove .xls
            FileDate = Right(FileDate, 8)   ' get file date string
            CheckDate = FileDate            ' convert to date
            If CheckDate > LastDate Then
                LastDate = CheckDate
                LastFile = CheckFile
            End If
        End If
        CheckFile = Dir    ' Get next entry.
    Loop
    If LastFile = MyFile Then
    GoTo GetOut
    Else
    rsp = MsgBox("There is a newer version of this file available." & Chr(13) & LastFile & Chr(13) & "Would you like to open it?", vbYesNo, "NEWEST FILE")
    If rsp = vbYes Then
        Workbooks.Open Filename:=LastFile
        Workbooks(MyFile).Close SaveChanges:=False
    End If
    End If
GetOut:
End Sub