Note: The other languages of the website are Google-translated. Back to English
English English

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

Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
  • 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.

arrow blue right bubble 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:

doc-list-folder-names-1

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:

doc-list-folder-names-1


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.
kte tab 201905

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!
officetab bottom
Comments (18)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Thanks a lot.I was using cmd prompt to have a txt with my folders then copy it on excel but now u make things easy for me :)
This comment was minimized by the moderator on the site
What if i need to create the link also for the folder what should i modify in the coding and cant we create button and link the same coding to it, so it will be useful for the users who dont know how to run macros
This comment was minimized by the moderator on the site
I did exactly what your requesting. I placed my companies logo on the whorksheet, then I right-clicked on it and assigned a macro (which is the above code.) You can also insert how-to used user instructions.
This comment was minimized by the moderator on the site
This code truly saved the day. Thank you for sharing it.
This comment was minimized by the moderator on the site
Hello, I just followed your directions but I'm getting errors when I hit F5 to run. The error below highlights "Dim xWs As Worksheet". Is there an updated code I can use? Compile error: User-defined type not defined
This comment was minimized by the moderator on the site
[quote]Hello, I just followed your directions but I'm getting errors when I hit F5 to run. The error below highlights "Dim xWs As Worksheet". Is there an updated code I can use? Compile error: User-defined type not definedBy Caralyn[/quote] Are you using the Kutools add-on or MS Excel VBA editor? Since I am not using the add-on, I am unable to duplicate your error. Using MS VBA Editor works without any errors.
This comment was minimized by the moderator on the site
When I run this code it works but it only shows the first folder in side the folder that I choose. For example, When I run the code I choose "C:\Users\Johnson\Music" (Note: I have 70 Folders inside my Music Folder) When the code runs it only shows the first folder and then list all the folders inside that folder. I would like it to list all the folders inside the Music folder.
This comment was minimized by the moderator on the site
I am with the others - it works up to a point.

For me, that point is it creates the new s/s, details the folder I have shown (in Cells A1), the a yellow highlighted bar in row 2 with the headings followed by nothing else!

The folder I am looking at is empty except for sub folders (i.e. no data files exist) and the sub folders do not appera at all.

Can anyone help me list the sub folders and their files?
This comment was minimized by the moderator on the site
I modified it to add size:



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, 6).Value = Array("Path", "Dir", "Name", "Date Created", "Date Last Modified","Size")
Set fso = CreateObject("Scripting.FileSystemObject")
Set folder1 = fso.getFolder(xPath)
getSubFolder folder1
xWs.Cells(2, 1).Resize(1, 6).Interior.Color = 65535
xWs.Cells(2, 1).Resize(1, 6).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, 6).Value = Array(SubFolder.Path, Left(SubFolder.Path, InStrRev(SubFolder.Path, "\")), SubFolder.Name, SubFolder.DateCreated, SubFolder.DateLastModified, SubFolder.Size)
Next SubFolder
For Each subfld In prntfld.SubFolders
getSubFolder subfld
Next subfld
End Sub
This comment was minimized by the moderator on the site
When you include the SubFolder.Size function the script no longer list all the subfolders, only the first level.
How can I include the size and get all subfolders listed?
This comment was minimized by the moderator on the site
Hello. Can you please please help me on a code which I am struggling to find.

Below are the requirements for the code.



1. The VBA should go through all the folders and sub-folders
and check each and every type of file. The user should only give the path for
the top folder. The code should then check all the folders and sub folders
within the top folder.



2. After checking the files, the code should zip all files
which have not been accessed for more than 3 months. The accessed period is
something which I should be able to change in future if required. It should
allow me to change it to 1 month or 5 months if required.



3. After zipping the files, the code should delete the
original files which were zipped.



4. The zipped file should be saved in the same path as the
original file.
This comment was minimized by the moderator on the site
Great Tool! After long research found this accurate toy :)
This comment was minimized by the moderator on the site
Just what I needed, and perfectly clear instructions on how to make it work. Thanks a lot
This comment was minimized by the moderator on the site
Thank you so much!, very useful.
This comment was minimized by the moderator on the site
This worked and how... Thank you so much. Just one addition-- at Step 3, I had to click on Step Into and then only did the F5 key work for choosing the folder.
This comment was minimized by the moderator on the site
Thank you very much for this usefull code. is that possible save result in same workbook not to new one ?
This comment was minimized by the moderator on the site
Thank you, this is exactly what we needed to record the folders for our clients.
This comment was minimized by the moderator on the site
Great work... It's very helpful for me, thanks very much
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations