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 all of our available files. We can then test if their filetype to see if they are of filetype .csv, and if they are we can just open them in the loop so that all csv files are opened from the folder the user selects.
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 FileType = Mid(FilePath, InStrRev(FilePath, ".") , Len(FilePath)) 'Checking if the File is of type .csv and if yes, 'we open the file If FileType = ".csv" Then Open(FilePath) End If i = i + 1 Next objFile End If MsgBox ("Done, all of our CSV files are opened.") 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.