4

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

It used to be possible in Excel 2003 using the Application.FileSearch method, but this has be depreciated. (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

Irfy
  • 2,469
  • 4
  • 25
  • 31

1 Answers1

7

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

To summarize:

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
Richard Morgan
  • 7,601
  • 9
  • 49
  • 86
  • Thanks Richard, works a treat. Just need to go through the list of file and maintain a variable with the file with the greatest DateLastModified. – Irfy Jun 22 '11 at 09:55
  • 1
    As an aside, doing it this way also means that it works with all Excel 2003/2007/2010 (at the very least). – Irfy Jun 23 '11 at 08:37
  • not work, his find not last modified but last looped – Senior Pomidor May 28 '18 at 06:08