Using FileSystemObject to get File

[tod]

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

 

[Bob Phillips]

--

HTH

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

 

[Bob Phillips]

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)