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

Answer 1

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.