List All Files from a Directory into Excel

Get a list of all files from a directory into an Excel worksheet. This allows you to navigate to anywhere on your computer and have a listing of the files within the directory output into Excel.

This macro works by allowing you to visually navigate through your computers directories until you find the one you want and then, when you click the OK button, all files from that directory will be output into Excel. Each file will also have its file extension output along with it.

A second column of data is output by default that includes the size of the file in bytes. To stop that from appearing, comment-out that line of code under where it says to do so in the code.

Note: when you run this macro, no files will appear in the directories; this is due to the fact that you are selecting a directory instead of individual files.

Where to install the Macro: Module

Sub GetFileNames()
Dim xRow As Long
Dim xDirect, xFname
Dim Dest As Range

'Change this to put the data in a different place in the workbook.
'ActiveCell places the data in the currently selected cell and below.
Set Dest = ActiveCell

With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = Application.DefaultFilePath & "\"
.Title = "Select a folder"
.Show
    If .SelectedItems.Count <> 0 Then
        xDirect = .SelectedItems(1) & "\"
        xFname = Dir(xDirect, 7)

        Do While xFname <> ""
            Dest.Offset(xRow) = xFname
            'remove the next line to prevent displaying the file size (in bytes)
            Dest.Offset(xRow, 1) = FileLen(xDirect & xFname)
            xRow = xRow + 1
            xFname = Dir
        Loop

    End If
End With

End Sub

---