Finding a folder with the largest number.

[SteveZmyname]

Hello
Is it possible to search through a set of folders or directories and choose
the one with the largest number? Excel has a MAX function for finding the
largest number on a ss which I need to use later but I have to find a dir
with the largest number which in turn is the newest date. It's an odd date
format.

thanks for any help
Steve

 

[Chip Pearson]

You can use code like the following. Change the line marked with '<<<
to the start folder name, which contains all the subfolders whose
dates are to be compared. Since you don't specify how the folder names
translate to numbers, I've left that function empty. In the
DateFromFolderName function, put in whatever code you need to get the
appropriate value from the String variable FolderName. This variable
will contain the unqualified name (no path information) of the folder
passsed to it.

You'll need a reference to the Scripting runtime. In VBA, go to the
Tools menu, choose References, and scroll down to and check the entry
for "Microsoft Scripting RunTime".

Sub AAA()
Dim FSO As Scripting.FileSystemObject
Dim SubF As Scripting.Folder
Dim StartFolderName As String
Dim LatestDate As Long
Dim LatestFolderName As String

StartFolderName = "D:\Test" '<<<< CHANGE
Set FSO = New Scripting.FileSystemObject
For Each SubF In FSO.GetFolder(StartFolderName).SubFolders
DoSubFolder FSO, SubF, LatestDate, LatestFolderName
Next SubF

MsgBox "Latest Folder: " & CStr(LatestFolderName) & vbNewLine & _
"Latest Date: " & CStr(LatestDate)
End Sub

Sub DoSubFolder(FSO As Scripting.FileSystemObject, _
FF As Scripting.Folder, ByRef LatestDate As Long, _
ByRef LatestFolderName As String)

Dim SubF As Scripting.Folder
Dim L As Long
L = DateFromFolderName(FF)
If L > LatestDate Then
LatestDate = L
LatestFolderName = FF.Path
End If
For Each SubF In FF.SubFolders
DoSubFolder FSO, SubF, LatestDate, LatestFolderName
Next SubF
End Sub

Function DateFromFolderName(FF As Scripting.Folder) As Long
Dim FolderName As String
FolderName = FF.Name
DateFromFolderName = FF.DateCreated
End Function



Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com