How to list all folders and subfolders in Excel?
Have you ever suffered with this problem that list all folders and subfolders from a specified directory into a worksheet? In Excel, there is no a quick and handy way to get the name of all the folders in a specific directory at once. To deal with the task, this article may help you.
List all folders and subfolders with VBA code
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
List all folders and subfolders with VBA code

If you want to get all the folder names from a specified directory, the following VBA code may help you, please do as this:
1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code into the Module Window.
VBA code: List all folders and subfolder names
Sub FolderNames() 'Update 20141027 Application.ScreenUpdating = False Dim xPath As String Dim xWs As Worksheet Dim fso As Object, j As Long, folder1 As Object With Application.FileDialog(msoFileDialogFolderPicker) .Title = "Choose the folder" .Show End With On Error Resume Next xPath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\" Application.Workbooks.Add Set xWs = Application.ActiveSheet xWs.Cells(1, 1).Value = xPath xWs.Cells(2, 1).Resize(1, 5).Value = Array("Path", "Dir", "Name", "Date Created", "Date Last Modified") Set fso = CreateObject("Scripting.FileSystemObject") Set folder1 = fso.getFolder(xPath) getSubFolder folder1 xWs.Cells(2, 1).Resize(1, 5).Interior.Color = 65535 xWs.Cells(2, 1).Resize(1, 5).EntireColumn.AutoFit Application.ScreenUpdating = True End Sub Sub getSubFolder(ByRef prntfld As Object) Dim SubFolder As Object Dim subfld As Object Dim xRow As Long For Each SubFolder In prntfld.SubFolders xRow = Range("A1").End(xlDown).Row + 1 Cells(xRow, 1).Resize(1, 5).Value = Array(SubFolder.Path, Left(SubFolder.Path, InStrRev(SubFolder.Path, "\")), SubFolder.Name, SubFolder.DateCreated, SubFolder.DateLastModified) Next SubFolder For Each subfld In prntfld.SubFolders getSubFolder subfld Next subfld End Sub
3. Then press F5 key to run this code, and a Choose the folder window will pop out, then you need to select the directory that you want to list the folder and subfolder names, see screenshot:
4. Click OK, and you will get the folder and subfolders path, directory, name, created date and last modified date in a new workbook, see screenshot:
Related article:
How to list files in a directory to worksheet in Excel?
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
