VBA – Looping through all files in a folder
This posts looks a lot like my previous – but it’s a bit simpler. Here I just show how to loop through files in a specific folder, which the user has chosen in a modal window.
Sub ListFiles() Dim fd As FileDialog Dim PathOfSelectedFolder As String Dim SelectedFolder Dim SelectedFolderTemp Dim MyPath As FileDialog Dim fs Dim ExtraSlash ExtraSlash = "\" Dim MyFile 'Prepare to open a modal window, where a folder is selected Set MyPath = Application.FileDialog(msoFileDialogFolderPicker) With MyPath 'Open modal window .AllowMultiSelect = False If .Show Then 'The user has selected a folder 'Loop through the chosen folder For Each SelectedFolder In .SelectedItems 'Name of the selected folder PathOfSelectedFolder = SelectedFolder & ExtraSlash Set fs = CreateObject("Scripting.FileSystemObject") Set SelectedFolderTemp = fs.GetFolder(PathOfSelectedFolder) 'Loop through the files in the selected folder For Each MyFile In SelectedFolderTemp.Files 'Name of file MsgBox MyFile.Name 'DO STUFF TO THE FILE, for example: 'Open each file: 'Workbooks.Open FileName:=MyFile Next Next End If End With End Sub
M | T | W | T | F | S | S |
---|---|---|---|---|---|---|
1 | 2 | 3 | ||||
4 | 5 | 6 | 7 | 8 | 9 | 10 |
11 | 12 | 13 | 14 | 15 | 16 | 17 |
18 | 19 | 20 | 21 | 22 | 23 | 24 |
25 | 26 | 27 | 28 | 29 | 30 |
Recent Posts
- VBA – Import CSV file
- VBA – Get name of file without extension
- UserForm Listbox – Populate list and extract selected items
- VBA – Retrieve Last Row From More Than One Column
- VBA – Check Extension of File
- VBA – Delete PivotTables
- VBA – Add New WorkSheet After The Last Worksheet
- VBA – Toggle Between Open Excel Files
- VBA – Looping through all files in a folder
- VBA – Create and add items to dynamic arrays
- VBA – Loop through arrays
- Excel formula – Miscellaneous
- VBA – Delete all files in a folder
- VBA – Loop through sheets
- VBA – Define PageSetup (PaperSize, Orientation, etc.) and Print Excel Sheet