How to "excel vba find newest file in folder"?

For answering this inquiry we will mostly use the code for the folder picker in my other article where 90% of the code is already done for us that we will slightly modify. https://www.pls-fix-thx.com/post/open-file-folder-with-vba

Short background for the folder picker is that we can use it to pick either a folder or a file. This way we can let the user pick the folder for us where we want to find the latest saved file.

MsoFileDialogType can be one of these constants:

• msoFileDialogFilePicker. = Allows user to select a file.

• msoFileDialogFolderPicker. = Allows user to select a folder.

​Getting all files from a folder​

​In this case we will use the script "getting all files from a targeted folder" in order to find the newest excel file in there. This script already loops through our files in the folder so we will just need to add a statement to evaluate the newest file that we want excel vba to find.

This a handy one I have been using a few times when I want to collect all files from a folder, in order to create attachments for emails.

Sub GetAllFilesInAFolder()

Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
FolderSheet.Activate
​
'*** Allowing the user to select the folder'***
'Getting the FileDialog object.
Dim fDialog As FileDialog
Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
​
'Changes the upper left corner of the filedialog picker
fDialog.Title = "Select a folder" 
'Changes the "OK" button text
fDialog.ButtonName = "Select a folder"
​
    'Opening the dialog. -1 means that it works.
    If fDialog.Show = -1 Then
       'The full path to the file selected by the user
       PathString = fDialog.SelectedItems(1) 
    End If
 
'Our Folder is now stored in PathString

'*** END - Allowing the user to select the folder'***

'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
​
If PathString <> "" Then
    Set objFolder = objFSO.GetFolder(PathString) 'Opens function.
 
    'loops through each file in the directory 
    'and prints their names and path
    For Each objFile In objFolder.Files
    
        FilePath = objFile.Path 'filepath of each file.
        FileSave = objFile.DateLastModified 'modified date
        
        'First file is assigned as our latest file and then we
        'start comparing for the next one.
        If FileLatestTime = "" Or FileLatestTime < FileSave Then
            FileLatestTime = FileSave
            FileLatest = FilePath
        End If
        
        i = i + 1
        
    Next objFile
​
End If
​
MsgBox ("Done")
'In case we want to open the newest excel file for the end user we can just run the .open command. 
  Workbooks.Open FileLatest 
  
End Sub

Download Excel Workbook here.

Feel free to download my prepared workbook containing the code for my previous article to get a jumpstart on this problem.

FolderPickerScript

.zip

Download ZIP • 24KB

Learn more about VBA here for all my posts: https://www.pls-fix-thx.com/vba

Learn more about Python here for all my posts: https://www.pls-fix-thx.com/python

If you have found this article or website helpful. Please show your support by visiting the shop below.

Coffee Mug - Money Green

Buy Now