On Thu, 15 Dec 2011 10:29:43 -0800 (PST), DeaconBlues wrote:

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

Perhaps the following will get you started.

There may be a way to iterate through both the subfolders and the root folder in a single step, but I did not run across it in a brief look.
It works fine he Excel 2007, W7x64

==================================
Option Explicit
Dim MostRecent(0 To 1) As Variant
Dim FSO As FileSystemObject
Dim fldrs As Folder, fldr As Folder
Dim fs As Files, f As File
Sub NewestFile()
Const InitialPath As String = "D:\Users\Ron\Documents\DATA\PERSONAL\FINANCE"
Set FSO = New FileSystemObject
Set fldrs = FSO.GetFolder(InitialPath)
For Each fldr In fldrs.SubFolders
CheckMostRecent
Next fldr
Set fldr = FSO.GetFolder(InitialPath)
CheckMostRecent

Debug.Print MostRecent(0), MostRecent(1)

Set FSO = Nothing
End Sub
Private Sub CheckMostRecent()
Set fs = fldr.Files
For Each f In fs
If f.DateLastModified MostRecent(1) Then
MostRecent(0) = f.Path
MostRecent(1) = f.DateLastModified
End If
Next f
End Sub
===================================