How to get the last modified file in a directory using VBA in Excel 2010

Question

I am looking for a way to do this in Excel 2010 using VBA.

This was possible in Excel 2003 using the Application.FileSearch method, but it was discounted. (see below)

Dim sFileName As String sFileName = "" With Application.FileSearch .NewSearch .LookIn = sDir .Filename = "*.*" .Execute msoSortByLastModified, msoSortOrderDescending If .FoundFiles.Count > 0 Then sFileName = .FoundFiles(1) End With 

Any ideas how to do this in Excel 2010?

thanks

Answer 1

If using FileSystemObject is acceptable, you can use the method described here .

Summarizing:

 Dim fso As Scripting.FileSystemObject Dim fol As Scripting.Folder Dim fdr As Scripting.Folder Dim fil As Scripting.File Dim flc As Scripting.Folders Set fso = CreateObject("Scripting.FileSystemObject") Set fol = fso.GetFolder("YourPathName") Set flc = fol.SubFolders For Each fdr In flc For Each fil In fdr.Files Debug.Print fil.DateLastModified Next fil Next fdr Set fso = Nothing Set fol = Nothing Set flc = Nothing