Get the last updated file name.

[princ_wns]

Hi All,

I m having a folder which consist of multiple files and i want the name of the file ie last accesd by any one means i need to know which file has updated most recently.


regards and thanks

[Admin]

Hi

Try


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


Sub kTest()

MsgBox GetLastModifiedFile("YourFolder")

End Sub

[Rick Rothstein]

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)...


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

[princ_wns]

Hi Admin and Rick thanks alot for ur quick reply