Get the last updated file name.

**Admin** · 04-10-2012, 03:35 PM

Hi

Try

Code:

Function GetLastModifiedFile(ByVal FolderName As String, Optional FileType As String = "Excel") As String
    
    Dim objFso          As Object
    Dim Fldr            As Object
    Dim f, FileName     As String
    Dim d               As Single
    Dim t As Single, e  As String
    
    Set objFso = CreateObject("Scripting.FileSystemObject")
    
    If Right$(FolderName, 1) <> Application.PathSeparator Then FolderName = FolderName & Application.PathSeparator
    
    Set Fldr = objFso.getfolder(FolderName)
    d = 0
    For Each f In Fldr.Files
        FileName = f.Name
        e = Mid$(FileName, InStrRev(FileName, ".") + 1)
        Select Case UCase$(FileType)
            Case "EXCEL"
                If e Like "xl*" Then
                    t = f.datelastmodified
                    If t > d Then
                        d = t: GetLastModifiedFile = FileName
                    End If
                End If
            Case "WORD"
                If e Like "do*" Then
                    t = f.datelastmodified
                    If t > d Then
                        d = t: GetLastModifiedFile = FileName
                    End If
                End If
            Case "POWERPOINT"
                If e Like "pp*" Then
                    t = f.datelastmodified
                    If t > d Then
                        d = t: GetLastModifiedFile = FileName
                    End If
                End If
        End Select
    Next

End Function

and call the function like

Code:

Sub kTest()
    
    MsgBox GetLastModifiedFile("YourFolder")
    
End Sub

**Rick Rothstein** · 04-10-2012, 10:31 PM

You could also write Admin's function using VB's built in FileDateTime function instead of calling out to the FileSystemObject scripting object (note that I reorganized the code in keeping with my own personal programming preferences)...

Code:

Function GetLastModifiedFile(ByVal FolderName As String, Optional FileType As String = "Excel") As String
  Dim FN As String, FileName As String, FileNamePattern As String
  Dim NewestFileName As String, MaxDate As Date
  If Right$(FolderName,1) <> Application.PathSeparator Then FolderName = FolderName & Application.PathSeparator
  Select Case UCase(FileType)
    Case "EXCEL":      FileNamePattern = "*.xl*"
    Case "WORD":       FileNamePattern = "*.do*"
    Case "POWERPOINT": FileNamePattern = "*.pp*"
  End Select
  FN = Dir$(FolderName & FileNamePattern)
  Do While FN <> ""
    If FileDateTime(FolderName & FN) > MaxDate Then
      MaxDate = FileDateTime(FolderName & FN)
      NewestFileName = FN
    End If
    FN = Dir$
  Loop
  GetLastModifiedFile = NewestFileName
End Function