List All Files from a Directory into Excel

Add to Favorites

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







Similar Content on TeachExcel
Remove All Filtering From a Worksheet in Excel
Macro: This Excel macro removes all filtering from a worksheet in Excel. This allows you to ...
Require a Unique List of Numbers in a Range in Excel
Tutorial: I'll show you how to require a user to enter a unique number into a range of cells in Exce...
Excel Function to Remove All Text OR All Numbers from a Cell
Tutorial: How to create and use a function that removes all text or all numbers from a cell, whichev...
Get the First Word from a Cell in Excel
Tutorial: How to use a formula to get the first word from a cell in Excel. This works for a single c...
Basic Web Query in Excel - Import Data from the Web into Excel
Macro: Import data from the web into Excel with this macro. This is a basic web query macro ...
Get Comment Text from Cell Comments in Excel - UDF
Macro: This free Excel UDF outputs all text from a comment in Excel. This benefit of this UDF is ...



How to Install the Macro
  1. Select and copy the text from within the grey box above.

  2. Open the Microsoft Excel file in which you would like the Macro to function.

  3. Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.  Or For other ways to get there, Click Here.

  4. On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.

  5. If the Macro goes in a Module, Click Here, otherwise continue to Step 8.

  6. If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.

  7. If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.

  8. Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.

  9. You are now ready to run the macro.


Similar Content
Remove All Filtering From a Worksheet in Excel
Macro: This Excel macro removes all filtering from a worksheet in Excel. This allows you to ...
Require a Unique List of Numbers in a Range in Excel
Tutorial: I'll show you how to require a user to enter a unique number into a range of cells in Exce...
Excel Function to Remove All Text OR All Numbers from a Cell
Tutorial: How to create and use a function that removes all text or all numbers from a cell, whichev...
Excel Forum