Complete Guide: How To Get A List Of Folder Names In Excel Native Methods And Automated Workflows

Discover how to extract directory paths and catalog subfolder names into Microsoft Excel using native capabilities like Power Query, Visual Basic for Applications (VBA), and Command Prompt utilities. This technical guide outlines reliable methods for mapping file systems, transforming raw folder metadata, and automating directory audits across local, networked, and cloud-synced storage environments.

Technical Requirements & Environment Setup

Cataloging file system architecture into a spreadsheet requires selecting an approach matched to your operating system permissions, folder depth, and data refresh requirements. While standard Excel functionality allows basic manual copying, enterprise-grade file audits demand automated, repeatable processes capable of handling thousands of directory objects without system timeouts.

Before starting a directory extraction workflow, verify that your workstation environment satisfies these operational requirements:

• Supported Applications: Microsoft 365, Excel 2021, Excel 2019, or Excel 2016 (Power Query functions vary slightly in legacy 2010/2013 add-in versions).
• Operating System Permissions: Read permissions for target root folders, network shares (UNC paths), or synchronized cloud directories (OneDrive/SharePoint local syncs).
• Prerequisite Knowledge: Basic familiarity with absolute file paths (such as C:\Users\Username\Documents), UNC paths (such as \ServerName\SharedFolder), and Excel ribbon navigation.
• Security Configuration: Visual Basic for Applications requires macro execution permissions enabled under Excel Trust Center settings if choosing the VBA automation method.
• Estimated Execution Benchmark: 2 to 10 minutes for initial setup; sub-second processing execution for directories containing under 50,000 subfolders.

Step-by-Step Directory Cataloging Methods

Step 1: Extracting Folder Names via Power Query (No-Code Native Method)

Power Query provides the most robust, non-programmatic approach to extracting subfolder names. It generates a dynamic data connection that refreshes instantly whenever folders are added, renamed, or deleted within the source directory.

1. Launch Microsoft Excel and open a blank workbook.
2. Navigate to the Data tab on the top ribbon menu.
3. Click on Get Data, hover over From File, and select From Folder.
4. In the pop-up navigation window, paste or browse to the target root folder directory path whose subfolders you wish to extract, then click Open.
5. Excel will display a preview grid showing all items inside the directory. Instead of selecting Load, click the Transform Data button in the bottom right corner to launch the Power Query Editor.
6. To isolate folder names rather than individual files, locate the column named Folder Path or inspect the Attributes record column. Click on the expand icon on the Attributes column header, select Kind, and click OK.
7. Filter the newly expanded Kind column by clicking its drop-down arrow, unchecking all options except Folder, and clicking OK. Alternatively, if you simply want the list of parent paths, select the Folder Path column, right-click its header, and select Remove Other Columns.
8. To eliminate duplicate entries created by multi-tiered nested subfolders, right-click the remaining folder column header and select Remove Duplicates.
9. Clean up the folder text strings using the Transform tab if necessary (such as using Text Transformations to isolate specific subfolder levels or stripping trailing backslashes).
10. Click Close & Load on the Home tab ribbon. Power Query will import the finalized list of folder names into a structured Excel Table on a new worksheet.

Pro-Tip: To refresh your folder list in the future, simply navigate to the Data tab and click Refresh All (or press Ctrl + Alt + F5). Power Query will re-scan the file system and update the table automatically.

Step 2: Automating Deep Directory Scans with VBA Macros

When you require programmatic customization—such as recursively parsing deeply nested directory trees, logging folder creation dates, or bypassing specific system folders—a custom Visual Basic for Applications (VBA) macro offers maximum control.

1. Open your target Excel workbook and press Alt + F11 to open the Visual Basic Editor environment.
2. Click Insert from the top menu bar and select Module to generate a clean code window.
3. Build a recursive directory search procedure utilizing the FileSystemObject library. In the code module window, enter a routine that initializes the Scripting.FileSystemObject object, prompts the user for a folder path using Application.FileDialog(msoFileDialogFolderPicker), and assigns the target directory to a Folder object variable.
4. Program a primary execution sub-routine to write headers into Sheet1, establishing columns for Folder Name, Full Path, Date Created, and Last Modified.
5. Create a secondary recursive sub-routine that takes a Folder object as an input parameter. Within this sub-routine, execute a For Each loop iterating through the SubFolders collection of the parent folder object.
6. Inside the loop, command the script to output the current SubFolder properties (Name, Path, DateCreated, DateLastModified) into the next open row of the active worksheet using incremental integer counters.
7. Call the secondary recursive routine within itself for every child folder discovered, allowing the macro to dive infinitely deep into nested subfolder hierarchies.
8. Press F5 or click the green Run arrow to execute the script, select your target root folder when prompted, and observe the subfolder inventory populate line by line.

Warning: Running macro-based recursive scans across vast enterprise network drives containing millions of folders can cause Excel to temporarily display a "Not Responding" status. Ensure you include Application.ScreenUpdating = False at the start of your script and re-enable it upon completion to optimize run speeds.

Step 3: Fast Extraction Using Command Prompt (CMD) and Excel Import

For large storage volumes where Power Query or VBA encounters long file path limits or processing lag, utilizing the native Windows Command Prompt terminal provides an ultra-fast alternative.

1. Press Win + R, type cmd into the Run dialog box, and press Enter to open the Command Prompt terminal.
2. Navigate to your target directory by typing cd followed by a space and your full target directory path enclosed in quotation marks, then press Enter (for example: cd "C:\CompanyData\Projects").
3. Type the directory listing command optimized for folders only: dir /b /ad /s > "C:\Users\Public\FolderList.txt" and press Enter.
   
   
   • The parameter /b outputs bare format (no heading information or summary stats).
   • The parameter /ad filters strictly for Directory attributes (excluding files).
   • The parameter /s recursively includes all subdirectories within the target root.
   • The output redirector symbol (>) writes the resulting output straight to a plain text file.
4. Open Microsoft Excel, navigate to File > Open, select Browse, and set the file format filter dropdown to All Files (.) or *Text Files (.prn; *.txt; *.csv)**.
5. Choose your generated FolderList.txt file and click Open.
6. The Text Import Wizard will launch. Select Delimited, click Next, set your delimiter as a custom character or backslash if parsing path components, and complete the import wizard.
7. Save the imported file as a standard Excel Workbook (.xlsx).

Directory Extraction Methodologies & Performance Benchmarks

Selecting the optimal folder listing workflow depends on technical context, operating environment, and required automation levels. The comparison matrix below details the performance parameters and technical capabilities of each native methodology.

Operational Bottlenecks & System Remedies

System administrators and analysts often encounter operational bottlenecks when extracting folder structures across enterprise infrastructure. Below are common failures, their underlying root causes, and technical remediation steps.

Path Length Exceeded Exception (MAX_PATH 260 Limit)

• Root Cause: Windows API limitations historically restrict total file path strings to 260 characters. Deeply nested subfolders cause Power Query or VBA scripts to throw buffer overflow exceptions or silently skip long paths.
• Actionable Fix: Enable Long Paths in Windows OS by opening the Local Group Policy Editor (gpedit.msc), navigating to Computer Configuration > Administrative Templates > System > Filesystem, enabling Enable Win32 long paths, and restarting Excel. Alternatively, prefix folder paths in VBA scripts with the extended-length path string indicator \\?\ before local or UNC network paths.

Access Denied / Permission Denied Error 70

• Root Cause: The account executing the Excel query or VBA macro lacks Read/Execute security permissions for specific system folders, protected network directories, or hidden administrator shares within the targeted directory tree.
• Actionable Fix: Apply Error Handling within your extraction scripts. In VBA, wrap child folder access logic inside On Error Resume Next blocks to gracefully skip restricted subfolders while continuing the iteration process. In Power Query, filter out hidden or system folder attributes prior to expanding folder properties.

Unresponsive Excel UI During Large Directory Iterations

• Root Cause: High memory consumption and thread starvation caused by Excel continuously rendering worksheet cell changes and redrawing graphic elements while looping through thousands of filesystem objects.
• Actionable Fix: Optimize execution code by disabling background calculation and screen updating prior to loop execution. Execute Application.ScreenUpdating = False, Application.Calculation = xlCalculationManual, and Application.EnableEvents = False before processing the directory structure, then reinstate these settings to their original state upon workflow completion.

Frequently Asked Questions

Can Power Query extract only subfolder names without listing individual files?

Yes. When loading a directory into Power Query, you can filter the file list based on attributes. Expand the Attributes column, select Kind, and set the text filter to keep rows where Kind equals Folder. Alternatively, apply a filter on the extension column to isolate directory containers, or remove all file data columns and apply a Remove Duplicates transformation to the parent directory paths.

How do I include created and modified dates alongside the folder names?

Using Power Query, expand the Attributes record column in the editor grid and check the boxes for DateCreated and DateModified. If using VBA, query the .DateCreated and .DateLastModified properties of the FileSystemObject Folder class object and write those variable values into adjacent worksheet columns during your directory traversal loop.

Will these methods work on SharePoint or OneDrive cloud folders?

Yes, provided the cloud folders are actively synchronized to your local file system via the OneDrive Sync Client, allowing standard file pathing (e.g., C:\Users\Username\OneDrive - Company). For cloud-only storage that is not synced locally, use the From SharePoint Folder connector in Power Query instead of the local file system connector, supplying your tenant's base SharePoint site URL.

How can I list nested folders deeper than two sub-levels?

Power Query handles nested folders by default when reading directories; it processes all sub-directories within the root path regardless of depth. To display hierarchy levels in separate columns within your Excel table, select the full folder path column in Power Query, choose Split Column, select By Delimiter, set the delimiter to a backslash (\), and split at Each occurrence of the delimiter.

Streamline Your Data Architecture Today

Cataloging file system directories directly into Microsoft Excel simplifies file management tasks, accelerates audit schedules, and optimizes data governance processes. Master these powerful native data transformation workflows today to bring complete organization, visibility, and control to your enterprise storage environment.