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.
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.