Choose folder with largest filename number
 

Hello
How would I filter through a set of directories that have numbers for
filenames and determine which filename is the largest

thanks for any help

Choose folder with largest filename number
 

I modofied an old macro that I wrote to do this request. I didn't use
filesearch becasue it doesn't run on some PC's. Instead I used a
recursive routine that I wrote which goes through all sub directories
and searches for filename starting with specific characters. change
BasefileName in the code below as required and change the starting
folder name.

I ran this code I my PC and got Book4.xls as the largest version number
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


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=187198

http://www.thecodecage.com/forumz/chat.php

Choose folder with largest filename number
 

thanks Joel
I'll check this out right away!

"joel" wrote:


I modofied an old macro that I wrote to do this request. I didn't use
filesearch becasue it doesn't run on some PC's. Instead I used a
recursive routine that I wrote which goes through all sub directories
and searches for filename starting with specific characters. change
BasefileName in the code below as required and change the starting
folder name.

I ran this code I my PC and got Book4.xls as the largest version number
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


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=187198

http://www.thecodecage.com/forumz/chat.php

.