CODEWITHSUNDEEP
excelvba
Prashant Shete
Prashant Shete

Reputation: 60

Finding the Oldest and Newest file in a Directory

I am creating excel macro in which I need to put first and last modified file time in excel sheet, like first logfile modified time and last modified time.(Assume there are multiple log files in a directory). I need modified time for first file and last file.

I am not able to compare the date, please suggest.

Sub test()
Dim fso As Object
Dim fol As Object
Dim fil As Object
Dim temp As Date

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fol = fso.GetFolder("Z:\Logfiles\Monitor\Logon")

    For Each fil In fol.Files
        temp = fil.DateLastModified
    Next fil
MsgBox temp
End Sub

Upvotes: 0

Views: 937

Answers (2)

Error 1004
Error 1004

Reputation: 8220

Option Explicit

Sub LoopAllFilesInFolder()

    Dim strFolder As String
    Dim Library As Object, File As Object, Folder As Object

    strFolder = "Z:\Logfiles\Monitor\Logon\"

    Set Library = CreateObject("Scripting.FileSystemObject")
    Set Folder = Library.GetFolder(strFolder)
    Set File = Folder.Files

    For Each File In File

        MsgBox File.Name & " details:" & vbNewLine & vbNewLine & _
                "Date Created: " & File.datecreated & vbNewLine & _
                "Date Last Accessed: " & File.datelastaccessed & vbNewLine & _
                "Date Last Modified: " & File.dateLastModified

    Next

    Set Library = Nothing
    Set Folder = Nothing
    Set File = Nothing

End Sub

Upvotes: 0

PeterT
PeterT

Reputation: 8557

You just need to save the oldest and newest date as you loop through the directory:

Option Explicit

Sub test()
    Dim fso As Object
    Dim fol As Object
    Dim fil As Object
    Dim temp As Date

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fol = fso.GetFolder("C:\Temp\")

    Dim oldest As Date
    Dim oldestFile As String
    Dim newest As Date
    Dim newestFile As String
    For Each fil In fol.Files
        temp = fil.DateLastModified
        If (newest = #12:00:00 AM#) Or (temp > newest) Then
            newest = temp
            newestFile = fil.Path
        End If
        If (oldest = #12:00:00 AM#) Or (temp < oldest) Then
            oldest = temp
            oldestFile = fil.Path
        End If
    Next fil
    MsgBox "Oldest File: " & oldestFile & " (" & oldest & ")" & vbCrLf & _
           "Newest File: " & newestFile & " (" & newest & ")"
End Sub

Upvotes: 2

Related Questions