Results 1 to 4 of 4

Thread: Get the last updated file name.

  1. #1
    Member
    Join Date
    Nov 2011
    Posts
    41
    Rep Power
    0

    Get the last updated file name.

    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

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    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
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    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
    Last edited by Rick Rothstein; 04-11-2012 at 10:23 AM.

  4. #4
    Member
    Join Date
    Nov 2011
    Posts
    41
    Rep Power
    0
    Hi Admin and Rick thanks alot for ur quick reply

Similar Threads

  1. Replies: 19
    Last Post: 02-11-2013, 10:49 PM
  2. Import text file to an Excel file
    By obed_cruz in forum Excel Help
    Replies: 5
    Last Post: 08-03-2011, 07:58 PM
  3. Replies: 1
    Last Post: 06-02-2011, 10:38 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •