About Us

SteveZmyname

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

.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Folder with largest number	SteveZmyname	Excel Discussion (Misc queries)	3	March 13th 10 02:10 AM
Finding a folder with the largest number.	SteveZmyname	Excel Discussion (Misc queries)	1	March 5th 10 08:52 PM
Choose filename to go in a cell	Tim in Ottawa	Excel Discussion (Misc queries)	0	May 23rd 06 09:36 PM
How to choose a folder	Ctech[_37_]	Excel Programming	2	November 4th 05 12:36 PM
ENTER DATE, CHOOSE WKSHEET, SAVE IN FOLDER	jlegoland4	Excel Programming	2	September 1st 04 01:09 PM

Menu

About Us