What it does
Lists all the folders and files within a specified folder (and optionally all subfolders below it), exporting the parent/containing folder, the full folder/file path, the file name, and the file type.

When to use it
When you want to export a list of folders and files to Excel.

Why to use it
It’s a very quick way to list out a folder/file structure in Excel.  The list of files is often used for bulk tasks like copying and pasting files, renaming files, reorganizing folders/files.

Default shortcut
None

Other Details

• Category: Sheets / Folders and Files
• Difficulty: 3/5
• Usage/frequency: 2/5
• Automation factor: 4/5 (estimated 180 seconds saved each time used)
• Type: Bulk
• Date added: 12/2/2016
• Tags: Directory, folders, files

Related Macros
Bulk File Copy
Bulk File Rename
Create Subfolders

Other Articles
None

None

Prerequisites
Copy the entire path to your clipboard of the folder that contains files and/or subfolders that you want to list out in Excel.

Instructions
After you have copied the folder path to your clipboard, run the List Directory Contents macro.  A prompt is displayed where you can enter the folder path that will be used to list out the subfolders/files in Excel.  Your clipboard contents are used to display the default folder to use (which you can change if desired).  Click the OK button (or press the Enter key) to create a new sheet called Directory_Contents and list out all the folders and files that are in the specified folder.  Note that if that sheet name already exists in the active workbook, a prompt is displayed for whether to confirm overwrite the existing sheet.

Note: as of 1/10/2023, you can now export all folders and files within the entire hierarchy of a folder, not just directly within a folder.  After specifying the folder path, if it contains any subfolders, a second prompt is displayed asking if you would also like to export a list of files/folders in all the subfolders within the original folder path specified.