List All Files In Folder Excel VBA Macro

The following macro example triggers a dialog box to select a folder in windows explorer and then lists all files within that folder in the active worksheet.

Macro/VBA code:

 

  Sub ListAllFilesInFolder()

      Dim myDialog As FileDialog

      Dim myFolder As String, myFile As String

      ActiveSheet.Cells.Clear

      Set myDialog = Application.FileDialog(msoFileDialogFolderPicker)

      If myDialog.Show = -1 Then

          myFolder = myDialog.SelectedItems(1) & Application.PathSeparator

          myFile = Dir(myFolder & "*")

          Do While myFile <> ""

              r = r + 1

              ActiveSheet.Range("A" & r).Value = myFile

              myFile = Dir

          Loop

      End If

  End Sub

Macro explained:

• We declare a FileDialog object variable (myDialog) and two string variables to hold folder and files.
• We use the FileDialog property of the Application object to define a dialog object set to the variable myDialog. We use the folder picker dialog option with msoFileDialogFolderPicker. FileDialog accepts four possible constants:
• msoFileDialogFilePicker. Allows user to select a file
• msoFileDialogFolderPicker. Allows user to select a folder
• msoFileDialogOpen. Allows user to open a file
• msoFileDialogSaveAs. Allows user to save a file
• Then we check if a folder was picked with the Show property of the dialog object. If a folder was picked the result is -1, otherwise it would be 0.
• We use the SelectedItems property of the dialog object to select the folder.
•  Next, we use the Dir function to get the first file in that folder. The asterisk symbol (*) refers to all types of files. That can be changed to a specific name and/or extension.
• Finally, we loop through the files in the folder and add each file’s name to column A in the active worksheet.

This is how we copy filtered data to new sheets in Excel with VBA macros.

Other examples:

• How to Copy Filtered Data To New Sheets with Excel VBA macros
• How to Remove Unused Rows And Columns with Excel VBA macros 
• How to Open Workbook Safely with Excel VBA macros