Astra

VBA Find Last Modified File in Folder

Estimated reading: 3 minutes

In this article, we will learn to find the last modified file in a folder by using the VBA code.

Here we will consider two cases during the coding 

Case1: Find Last Modified File in Folder(without sub-folder)

Case2: Find Last Modified File in Folder(with sub-folder)

Case 1: Find the Last Modified File in the Folder(without sub-folder)

Using the following VBA code, you will find the last modified file in the folder. It should be noted that the following code will ignore the sub-folders.

Sub Last_Modified_File()
    Dim obj_FSO As Object
    Dim obj_Folder As Object
    Dim obj_File As Object
    Dim Folder_Path As String
    Dim File_name As String
    Dim Last_Modified As Date
    
    'Set the folder path
    Folder_Path = "C:\Users\USER\Desktop\SOLVED EXCEL\"
    
    'Create a FileSystemObject
    Set obj_FSO = CreateObject("Scripting.FileSystemObject")
    
    'Get the folder object
    Set obj_Folder = obj_FSO.GetFolder(Folder_Path)
    
    'Loop through all files in the folder
    For Each obj_File In obj_Folder.Files
        If obj_File.DateLastModified > Last_Modified Then
            Last_Modified = obj_File.DateLastModified
            File_name = obj_File.Name
        End If
    Next obj_File
    
    'Display the last modified file name and date
    MsgBox "The last modified file is: " & File_name & vbCrLf & _
           "Last modified on: " & Last_Modified
End Sub

Results: After running the code the following message box will show the last modified file with the last modified date in the folder

VBA Find Last Modified File in Folder

Case 2: Find the Last Modified File in the Folder(with sub-folder)

When you want to find the last modified file in the folders and sub-folders, you should use the following VBA code

Sub Last_Modified_File_in_Subfolders()
    Dim objFSO As Object
    Dim objFolder As Object
    Dim objFile As Object
    Dim strFolderPath As String
    Dim strFilename As String
    Dim dtLastModified As Date
    
    'Set the folder path
    strFolderPath = "C:\Users\USER\Desktop\SOLVED EXCEL\"
    
    'Create a FileSystemObject
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    
    'Get the folder object
    Set objFolder = objFSO.GetFolder(strFolderPath)
    
    'Call the recursive function to search through subfolders
    Last_Modified_File_Recursive objFolder, dtLastModified, strFilename
    
    'Display the last modified file name and date
    MsgBox "The last modified file is: " & strFilename & vbCrLf & _
           "Last modified on: " & dtLastModified
End Sub

Sub Last_Modified_File_Recursive(ByVal objFolder As Object, ByRef dtLastModified As Date, ByRef strFilename As String)
    Dim objFile As Object
    Dim objSubFolder As Object
    
    'Loop through all files in the folder
    For Each objFile In objFolder.Files
        If objFile.DateLastModified > dtLastModified Then
            dtLastModified = objFile.DateLastModified
            strFilename = objFile.Path
        End If
    Next objFile
    
    'Call the recursive function for all subfolders
    For Each objSubFolder In objFolder.SubFolders
        Last_Modified_File_Recursive objSubFolder, dtLastModified, strFilename
    Next objSubFolder
End Sub

Results: Similar to the code provided before, running the code by pressing F5 will display the last modified file path and the last modified date. 

VBA Find Last Modified File in Folder1

Note:  Please check the references before running the code

VBA Find Last Modified File in Folder3

2 thoughts on “VBA Find Last Modified File in Folder”

Leave a Reply

Your email address will not be published. Required fields are marked *

Share this Doc
Jump to Topics
SOLVED EXCEL
Scroll to Top