VBA - How to get the last modified file or folder in a directory in Excel 2010

Question

What I want to do is more complex than selecting a file from a list of files. I will start in a directory, then I want to change to the most recently modified directory. I then want to repeat that process in a sub-directory, and then, inside of that, I want to select the most recently modified excel file and open it.

What is the best approach to do this?

What objects / methods should I be looking into?

Solution

The simplest function is

FileDateTime(pathname)

where pathname can be a directory for folder.

Alternatively, you can use the FileSystemObject object, DateLastModified property:

Dim fileModDate As String

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(<filenamestringhere>)

fileModDate = f.DateLastModified

All of the above can be explored in VBA help.

Comments

[Jean-François Corbett]

... and, for the FileSystemObject, here. +1

[athos]

FileSystemObject doesn't work on folder, and FileDateTime only returns the last modified time, not the create time, if it's modified after creation...