This is probably not so much an Excel Programming question, but I'm
hoping someone can help me here.

I have a script that sends an Excel file by email each day. It has to
choose the most recent file from a given folder. The file names are
like this:

MyFile Jan 01.xls
MyFile Jan 02.xls
MyFile Jan 03.xls
etc..........

Is there a way to use FileSystemObject to get the file with the latest
date? I can't use the Format function in script, or otherwise this
would be easier.

tod

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"tod" wrote in message
om...
This is probably not so much an Excel Programming question, but I'm
hoping someone can help me here.

I have a script that sends an Excel file by email each day. It has to
choose the most recent file from a given folder. The file names are
like this:

MyFile Jan 01.xls
MyFile Jan 02.xls
MyFile Jan 03.xls
etc..........

Is there a way to use FileSystemObject to get the file with the latest
date? I can't use the Format function in script, or otherwise this
would be easier.

tod

Tod,

Here's a solution using FileSearch

Function GetLatestFile(Folderr As String) As String
Dim LastFile
Dim LastFileDateTime
Dim i As Long

LastFileDateTime = 0

With Application.FileSearch
.NewSearch
.LookIn = Folder
.FileType = msoFileTypeExcelWorkbooks
.SearchSubFolders = False
.Execute
For i = 1 To .FoundFiles.Count
If FileDateTime(.FoundFiles(i)) LastFileDateTime Then
LastFileDateTime = FileDateTime(.FoundFiles(i))
LastFile = .FoundFiles(i)
End If
Next i
End With

GetLatestFile = Replace(LastFile, "~$", "")

End Function


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"tod" wrote in message
om...
This is probably not so much an Excel Programming question, but I'm
hoping someone can help me here.

I have a script that sends an Excel file by email each day. It has to
choose the most recent file from a given folder. The file names are
like this:

MyFile Jan 01.xls
MyFile Jan 02.xls
MyFile Jan 03.xls
etc..........

Is there a way to use FileSystemObject to get the file with the latest
date? I can't use the Format function in script, or otherwise this
would be easier.

tod