No need to load all the files in an array and sort, unless you need that for
some other reason.
Just make a small modifiction to the code:


Function MostRecentFileInFolder2(strFolder As String, _
strFileMask As String) As String

Dim strDirReturn As String
Dim lMax As Long
Dim lSuffix As Long
Dim strLastFile As String

On Error GoTo ERROROUT

If Not Right$(strFolder, 1) = "\" Then
strFolder = strFolder & "\"
End If

strDirReturn = Dir$(strFolder & strFileMask, _
vbArchive Or _
vbHidden Or _
vbReadOnly Or _
vbSystem)

Do While Len(strDirReturn)
lSuffix = Val(Right$(strDirReturn, 4))
If lSuffix lMax Then
lMax = lSuffix
strLastFile = strFolder & strDirReturn
End If
strDirReturn = Dir$()
Loop

MostRecentFileInFolder2 = strLastFile

Exit Function
ERROROUT:

MostRecentFileInFolder2 = ""
On Error GoTo 0

End Function


RBS


"Enohp Aikon" wrote in message
...
New method new problem.

The FileDateTime returns the date and time when a file was created or last
modified. I was using the FileSearch object to return the date the file
was
created (not modified).
I did not fully explain exactly what I was trying to accomplish in my
original post. I have a procedure that creates a workbook on demand,
names
it and then saves it to a particular directory. The file names consist of
a
common prefix name combined with a incremental suffix number (ie:
MyWorkbook-0001, MyWorkbook-0002, and so on).

I need to:
Find the file with the highest suffix number
Get the suffix portion of the name and convert it to a number
Increment the number by 1
Convert the number back into text with leading zeros (0003).
Create a new file name by combining the constant prefix name with the
newly
created suffix ( ie: sNewFileName = gsFileSufixName & sNextSheetSuffix)

I guess I will need to load all of the files into an array, sort by
descending order and they select the 1st item in the array (or something
like
that). Any suggestions?


"Enohp Aikon" wrote:

Thanks very much. This works great and works faster than the FileSearch
object.

"RB Smissaert" wrote:

You could do it like this. Pretty fast as well.


Function MostRecentFileInFolder(strFolder As String, _
strFileMask As String) As String

Dim strDirReturn As String
Dim dMax As Date
Dim strLastFile As String

On Error GoTo ERROROUT

If Not Right$(strFolder, 1) = "\" Then
strFolder = strFolder & "\"
End If

strDirReturn = Dir$(strFolder & strFileMask, _
vbArchive Or _
vbHidden Or _
vbReadOnly Or _
vbSystem)

Do While Len(strDirReturn)
If FileDateTime(strFolder & strDirReturn) dMax Then
dMax = FileDateTime(strFolder & strDirReturn)
strLastFile = strFolder & strDirReturn
End If
strDirReturn = Dir$()
Loop

MostRecentFileInFolder = strLastFile

Exit Function
ERROROUT:

MostRecentFileInFolder = ""
On Error GoTo 0

End Function

Sub test()
MsgBox MostRecentFileInFolder("C:\TestFolder", "*.txt")
End Sub


RBS


"Enohp Aikon" wrote in message
...
The file search object does not work properly on all platforms. This
is a
known issue but the workarounds provided in article 305342 do not
resolve
the
problem when using a local hard drive mapped to €œK:€�. I need
recommendations for returning the most recent file in a subdirectory
with
out
using the FileSearch object.

The following code works as expected except when using Office XP with
Windows 2000.
------------------------------------------------------------------------------
With Application.FileSearch
.Newsearch
.Lookin = €œK:\€�
.SearchSubFolders = false

NumFound = .Execute(SortBy: msoSortByLastModified, _
SortOrder: = msoSortOrderDescending _
AlwaysAccurate = True)

If NumFound 0 then NewestFile = FilesFound(1)
-------------------------------------------------------------------------------

I need code that works on any combination of Office 2000 and above
with
Window 2000 and above. The code above is generic and I will actually
be
using a function.

Thanks