here is a little function I put together rapidly to return the filename of
the most recent file in a directory.
'---------------------------------------------------------------------------------------
' Procedure : MostRecentFile
' Author : Daniel Pineault
' Purpose : Determine the most recent file in a directory
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sDir ~ Directory to find the most recent file in
'---------------------------------------------------------------------------------------
Function MostRecentFile(sDir As String) As Variant
On Error GoTo Error_Handler
Dim objFS As Object
Dim objFolder As Object
Dim objFile As Object
Dim sobjFileName As String
Dim vDT As Date
Dim iFileCount As Integer
If Right(sDir, 1) <> "\" Then
sDir = sDir & "\"
End If
Set objFS = CreateObject("Scripting.FileSystemObject")
'Make sure the directory exist
If objFS.FolderExists(sDir) Then
Set objFolder = objFS.GetFolder(sDir)
iFileCount = objFolder.Files.Count
If iFileCount = 0 Then
MsgBox "There are no files in the specified directory '" & sDir
& "'"
Set objFile = Nothing
Set objFolder = Nothing
Exit Function
End If
For Each objFile In objFolder.Files
If objFile.DateLastModified > vDT Then
sobjFileName = objFile.Name
vDT = objFile.DateLastModified
End If
Next
Set objFile = Nothing
Set objFolder = Nothing
Set objFS = Nothing
MostRecentFile = sobjFileName
Else
MsgBox "The directory '" & sDir & "' does not exist."
Set objFile = Nothing
Exit Function
End If
If Err.Number = 0 Then Exit Function
Error_Handler:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf &
"Error Number: " & _
Err.Number & vbCrLf & "Error Source: MostRecentFile" & vbCrLf & "Error
Description: " & _
Err.Description, vbCritical, "An Error has Occured!"
Set objFile = Nothing
Set objFolder = Nothing
Set objFS = Nothing
Exit Function
End Function
--
Hope this helps,
Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples:
http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.
