VBA producing most recently modified .XLS file in a folder

Question

What VBA code can I use to use a folder path displayed in a cell to retrieve the most recently modified .xls file within that folder? So far, I have filenames showing but not the correct files:

Function GetFileNames(ByVal FolderPath As String) As Variant
Dim Result As Variant
Dim i As Integer
Dim MyFile As Object
Dim MyFSO As Object
Dim MyFolder As Object
Dim MyFiles As Object
Set MyFSO = CreateObject("Scripting.FileSystemObject")
Set MyFolder = MyFSO.GetFolder(FolderPath)
Set MyFiles = MyFolder.Files
ReDim Result(1 To MyFiles.Count)
i = 1
For Each MyFile In MyFiles
Result(i) = MyFile.Name
i = i + 1
Next MyFile
GetFileNames = Result
End Function

Your code correctly fills up the `Result` array with the file names from the given folder. What about those are not the correct files? — PeterT, Jun 07 '18 at 18:53
@PeterT it currently has no restriction on file type nor does it return the most recently modified file name — Brian, Jun 07 '18 at 18:55

DecimalTurn · Accepted Answer · 2018-06-09T20:13:08.603

I think that what you are looking for is something like the selected answer for this question.

You could adapt the code to fit your specific needs of passing the argument inside the like the function below. Note that the argument directory must include the backward slash at the end (eg. "C:\Users\").

Function NewestFile(Directory As String) As String
'PURPOSE: Get the newest file name from specified directory
Dim FileName As String
Dim MostRecentFile As String
Dim MostRecentDate As Date
Dim FileSpec As String

'Specify the file type, if any
 FileSpec = "*.xls"
FileName = Dir(Directory & FileSpec)

If FileName <> "" Then
    MostRecentFile = FileName
    MostRecentDate = FileDateTime(Directory & FileName)
    Do While FileName <> ""
        If FileDateTime(Directory & FileName) > MostRecentDate Then
             MostRecentFile = FileName
             MostRecentDate = FileDateTime(Directory & FileName)
        End If
        FileName = Dir
    Loop
End If

NewestFile = MostRecentFile

End Function

EDIT: For more flexibility, you can also add the option (like in PeterT's revised answer) to search for another type of file with the optional FileSpec argument like in the alternative function below. For this function, if you don't provide any value for FileSpec, it will look at all files.

Function NewestFile(ByVal Directory As String, Optional ByVal FileSpec As String = "*.*") As String
'PURPOSE: Get the newest .xls file name from
Dim FileName As String
Dim MostRecentFile As String
Dim MostRecentDate As Date

'Specify the file type, if any
FileName = Dir(Directory & FileSpec)

If FileName <> "" Then
    MostRecentFile = FileName
    MostRecentDate = FileDateTime(Directory & FileName)
    Do While FileName <> ""
        If FileDateTime(Directory & FileName) > MostRecentDate Then
             MostRecentFile = FileName
             MostRecentDate = FileDateTime(Directory & FileName)
        End If
        FileName = Dir
    Loop
End If

NewestFile = MostRecentFile

End Function

Speed issue: Dir Function vs FileSystemObject

In terms of speed, if the folder you want to look at contains a small number of files, the 2 methods will give you the same results in roughly the same amount of time. However, if you have a lot of files in that folder, using the Dir Function approach instead of the FileSystemObject should speed up greatly the execution of your macro. I haven't tested it, but that seems to be what was concluded from the answers in this question.

PeterT · Answer 2 · 2018-06-07T19:32:50.763

You just need to check the DateLastModified timestamp of each file in the folder. A quick check to see if it's the most recent will "sort" it to the top.

Option Explicit

Sub test()
    Debug.Print "most recently modified file is " & GetNewestModifiedFilename("C:\Temp")
End Sub

Function GetNewestModifiedFilename(ByVal folderPath As String, _
                                   Optional fileType As String = "xls*") As String
    Dim MyFSO As Object
    Dim MyFolder As Object
    Dim MyFiles As Object
    Set MyFSO = CreateObject("Scripting.FileSystemObject")
    Set MyFolder = MyFSO.GetFolder(folderPath)
    Set MyFiles = MyFolder.Files

    Dim mostRecentFilename As String
    Dim mostRecentTimestamp As Date
    Dim MyFile As Object
    For Each MyFile In MyFiles
        Debug.Print MyFile.Name & ", modified " & MyFile.DateLastModified
        If Mid(MyFile.Name, InStrRev(MyFile.Name, ".") + 1) Like fileType Then
            If MyFile.DateLastModified > mostRecentTimestamp Then
                mostRecentFilename = MyFile.Name
                mostRecentTimestamp = MyFile.DateLastModified
            End If
        End If
    Next MyFile
    GetNewestModifiedFilename = mostRecentFilename
End Function

Great answer! However, the question mentions that the function should only look at *xls files. — DecimalTurn, Jun 07 '18 at 19:25
A simple and optional parameter to restrict the file type search has been added. It already was returning the filename most recently modified. — PeterT, Jun 07 '18 at 19:33

VBA producing most recently modified .XLS file in a folder

2 Answers2