VBA – Tips & Tricks

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