No problem.
Tom and Jim may be experts, but not me.
Post plenty of questions myself.

RBS


"Enohp Aikon" wrote in message
...
I realized that the code needed to be changed slightly to account for the
file extension prior to reading the latest replies. I was able to get it
working but your way is WAY better (if instr(1,strDirReturn,".").

Thanks Jim, RBS and Tom for both your help and your time! You guys
underscore the difference between amateur and experts.


"RB Smissaert" wrote:

Yes, quite right.
This was air-code and hadn't thought about the details.

RBS

"Tom Ogilvy" wrote in message
...
wouldn't you need to strip off the extenstion first?

Do While Len(strDirReturn)
if instr(1,strDirReturn,".") then _
strDirReturn = Left(strDirReturn,Instr(1,strDirReturn,".")-1)
lSuffix = Val(Right$(strDirReturn, 4))

--
Regards,
Tom Ogilvy

"RB Smissaert" wrote in message
...
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