Function NewestFileInFolder(strFolder As String, strFileType As String)
'Usage - gets the latest file of a specified type in a selected folder
'Use "*.*" for any files; "*.txt" for txtfiles etc
'e.g. NewestFileInFolder ("c:\windows\system32\", ".txt")
Dim strFileName As String
Dim MostRecentFile As String
Dim MostRecentDate As Date
'check for trailing '\'
If Right(strFolder, 1) <> "\" Then strFolder = strFolder & "\"
'get files matching folder & file type
strFileName = Dir(strFolder & strFileType)
'find latest file
If strFileName <> "" Then
MostRecentFile = strFileName
MostRecentDate = FileDateTime(strFolder & strFileName)
Do While strFileName <> ""
If FileDateTime(strFolder & strFileName) > MostRecentDate Then
MostRecentFile = strFileName
MostRecentDate = FileDateTime(strFolder & strFileName)
End If
strFileName = Dir
Loop
End If
'output latest file name
NewestFileInFolder = MostRecentFile
'Debug.Print NewestFileInFolder
End Function
Function NewestFilePath(strFolder As String, strFileType As String)
'Usage - gets the full path of the latest file of a specified type in a selected folder
'Use "*.*" for any files; "*.txt" for txtfiles etc
Dim strFileName As String
Dim MostRecentFile As String
Dim MostRecentDate As Date
'check for trailing '\'
If Right(strFolder, 1) <> "\" Then strFolder = strFolder & "\"
'get files matching folder & file type
strFileName = Dir(strFolder & strFileType)
'find latest file
If strFileName <> "" Then
MostRecentFile = strFileName
MostRecentDate = FileDateTime(strFolder & strFileName)
Do While strFileName <> ""
If FileDateTime(strFolder & strFileName) > MostRecentDate Then
MostRecentFile = strFileName
MostRecentDate = FileDateTime(strFolder & strFileName)
End If
strFileName = Dir
Loop
End If
'output latest file name
If MostRecentFile <> "" Then
NewestFilePath = strFolder & MostRecentFile
Else
MsgBox "There are no " & strFileType & " files in the folder : " & strFolder, vbExclamation, "No files"
End If
'Debug.Print NewestFilePath
End Function