Excel 2007
Win XP Pro


Hi folks,
I'm interested in a Excel VBA procedure that will identify the newest
file (most recently saved) in a folder and the folder's
subdirectories. I have spent some time searching this forum for an
existing solution with no luck. It's interesting because some of the
FSO solutions that only search one folder won't run on my Excel/
system. The one below does run, but it doesn't search the sub
directories.
I found this procedure (not mine) in the forum and have spent several
hours trying to modify it with a nested loop to examine the sub
folders of the target folder. I haven't had any luck (the holiday
season must be affecting my brain..). Could some kind soul assist me
with the code that loops through the sub folders?
Thanks in advance for any help you may provide.


Sub LatestFile()
'Jim Cone - San Francisco, USA - June 02, 2005
'Requires a project reference to "Microsoft Scripting
Runtime" (scrrun.dll)
'Displays the latest file name in the strPath folder.


Dim objFSO As Scripting.FileSystemObject
Dim objFolder As Scripting.Folder
Dim objFile As Scripting.File
Dim strPath As String
Dim strName As String
Dim varDate As Variant


' Specify the folder...
' strPath = "C:\Program Files\Microsoft Office\Office"
' strPath = "C:\Temp" <My Target folder
Set objFSO = New Scripting.FileSystemObject
Set objFolder = objFSO.GetFolder(strPath)


For Each objFile In objFolder.Files
If objFile.DateLastModified varDate Then
varDate = objFile.DateLastModified
strName = objFile.Name
End If
Next 'objFile


MsgBox strName & " - is latest file - " & varDate


Set objFSO = Nothing
Set objFolder = Nothing
Set objFile = Nothing
End Sub


find newest file in all sub folders sub directory