I modofied an old macro that I wrote to do this request. I didn't us
filesearch becasue it doesn't run on some PC's. Instead I used
recursive routine that I wrote which goes through all sub directorie
and searches for filename starting with specific characters. chang
BasefileName in the code below as required and change the startin
folder name.
I ran this code I my PC and got Book4.xls as the largest version numbe
on my PC.
Sub GetFiles()
Dim VersionNumber As Integer
strFolder = "c:\temp"
BaseFileName = "Book"
VersionNumber = 0
Set fso = CreateObject _
("Scripting.FileSystemObject")
Set folder = _
fso.GetFolder(strFolder)
Call GetSubFolderSize(strFolder + "\", _
BaseFileName, VersionNumber)
MsgBox ("Highest Version Number is : " & VersionNumber)
End Sub
'Recursive function that call itself to get all the subfolders
Sub GetSubFolderSize(ByVal strFolder As String, _
ByVal BaseFileName As String, ByRef VersionNumber As Integer)
'get number of characters in BasefileName
BaseNameLen = Len(BaseFileName)
Set fso = CreateObject _
("Scripting.FileSystemObject")
Set folder = _
fso.GetFolder(strFolder)
If folder.subfolders.Count > 0 Then
For Each sf In folder.subfolders
On Error GoTo 100
Call GetSubFolderSize(strFolder + sf.Name + "\", _
BaseFileName, VersionNumber)
100 Next sf
End If
'folder size in bytes
On Error GoTo 200
For Each Myfile In folder.Files
ShortName = Myfile.Name
If Left(UCase(ShortName), BaseNameLen) = _
UCase(BaseFileName) Then
'check if there is a period in the filename
If InStr(ShortName, ".") > 0 Then
'remove the extension from the file name
BaseName = Left(ShortName, InStrRev(ShortName, ".") - 1)
'get the number portion of the filename
VerNumber = Mid(BaseName, BaseNameLen + 1)
If IsNumeric(VerNumber) Then
If Val(VerNumber) > VersionNumber Then
VersionNumber = Val(VerNumber)
End If
End If
End If
End If
Next Myfile
200 On Error GoTo 0
End Sub