VBA - File Dialog vs Grab Newest File in Directory?

Question

I currently provide a file dialog to permit a user to select the desired file from a pre-defined directory. Works as expected; I am just looking to reduce steps/interaction.

The files are simple timestamped log files, many in the directory. I do not know the actual filename, but I do know the desired file will always be the NEWEST file in that directory.

Is there a way to select the filename for the file with the newest date/time from a known directory?

Thanks!

If there's a will, there's a way. Lookup how to get a file's creation date, and then iterate the folder, locate the youngest file, *and then still prompt the user for confirmation* - that way the user still gets a shot at locating the file themselves, and then when (not if; *when*) your assumption that "it will *always* be the newest file in that directory" is shattered, you won't have to rewrite anything. — Mathieu Guindon, May 25 '17 at 14:31
As it stands your question has a yes/no answer if answered at face value.. which makes a rather boring Q/A (FWIW the answer is "yes"). If you meant to ask "how to grab the newest file in a given directory", then your question is either a duplicate of [this question](https://stackoverflow.com/q/6656023/1188513), or simply *too broad*, for you're not providing your attempt at solving the problem, nor describing how that attempt isn't working. — Mathieu Guindon, May 25 '17 at 14:35
@Mat, Yes I suppose I should have asked "HOW" to perform that task. Looking at the link you provided. — Mark Pelletier, May 25 '17 at 14:37

score 0 · Accepted Answer · edited Aug 16 '17 at 00:52

I found a good approach here:

finding latest file in a folder and opening it (vba access)

Function NewestFile()

Dim FileName As String
Dim MostRecentFile As String
Dim MostRecentDate As Date
Dim FileSpec As String

'Specify the file type, if any
 FileSpec = "*.*" 
'specify the directory
 Directory = "C:"
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

` A few minor adjustments to suit my needs; works as desired.

VBA - File Dialog vs Grab Newest File in Directory?

1 Answers1