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

excel vba file-io last-modified
Brian picture Brian · Jul 11, 2011 · Viewed 68.1k times · Source

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?

Answer

Lance Roberts picture Lance Roberts · Jul 11, 2011

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.

Related questions

Open Excel file for reading with VBA without display

I want to search through existing Excel files with a macro, but I don't want to display those files when they're opened by the code. Is there a way to have them open "in the background", so to speak?

Read More
Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More
How do I declare a global variable in VBA?

I wrote the following code: Function find_results_idle() Public iRaw As Integer Public iColumn As Integer iRaw = 1 iColumn = 1 And I get the error message: "invalid attribute in Sub or Function" Do you know what I did wrong? I tried …

Read More