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

You didn't claim it was yours and you spent some time trying to figure it out.
Attributes, many posters don't exhibit, so...
'---
Sub FindTheFiles()
' Finds the latest file in folder/subfolders and counts the number of files.
' Jim Cone October 27, 2006
Dim strPath As String
Dim oFSO As Object
Dim oFile As Object
Dim oFolder As Object
Dim dteDate As Date
Dim strName As String
Dim N As Long

strPath = "C:\Documents and Settings"
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder(strPath)
For Each oFile In oFolder.Files
If oFile.DateLastModified dteDate Then
dteDate = oFile.DateLastModified
strName = oFile.Name
End If
N = N + 1
Next 'oFile

Call FindTheSubFolderFiles(oFolder, N, dteDate, strName)
MsgBox N & " files" & vbCr & strName & " _ is latest file " _
& vbCr & "Dated _ " & dteDate

Set oFSO = Nothing
Set oFile = Nothing
Set oFolder = Nothing
End Sub

Function FindTheSubFolderFiles(ByRef oParentFolder As Object, _
ByRef lngR As Long, ByRef dteDte As Date, ByRef strNme As String)
'Jim Cone October 27, 2006
Dim oSubFolder As Object
Dim oFile As Object
For Each oSubFolder In oParentFolder.SubFolders
For Each oFile In oSubFolder.Files
If oFile.DateLastModified dteDte Then
dteDte = oFile.DateLastModified
strNme = oFile.Name
End If
lngR = lngR + 1
Next
FindTheSubFolderFiles oSubFolder, lngR, dteDte, strNme
Next 'oSubFolder
Set oSubFolder = Nothing
Set oFile = Nothing
End Function
'---
I just tested the above again and found that I have a "blocklist.xml" Firefox file dated 12/28/2011.
Today in 12/15/2011 - I give up - I just live here. <g

If you want more information on the Scripting Run Time, see...
"Microsoft Windows Script 5.6 Documentation"
http://www.microsoft.com/downloads/d...DisplayLang=en
It is a downloadable help file written in plain English with examples.
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(List Files XL add-in: finds and lists files/folders with hyperlinks)





"DeaconBlues"

wrote in message
...
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

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
===================================