Skip to main content

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

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?

Best Office Productivity Tools

Supports Office/Excel 2007-2021 and 365  |  Available in 44 Languages  |  Easy to Uninstall Completely

Popular Features: Find/Highlight/Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns   |   Move Columns   |   Unhide Columns   |   Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need Is Just A Click Away...

Supercharge Your Excel Skills: Experience Efficiency Like Never Before with Kutools for Excel  (Full-Featured 30-Day Free Trial)

kte tab 201905

60-Day Unconditional Money-Back GuaranteeRead More... Free Download... Purchase... 

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! (Full-Featured 30-Day Free Trial)
60-Day Unconditional Money-Back GuaranteeRead More... Free Download... Purchase... 
 
Comments (18)
No ratings yet. Be the first to rate!
altaee1984
altaee1984
This comment was minimized by the moderator on the site
Great work... It's very helpful for me, thanks very much
Reply
Report
0
0
Tim_Cooprider
Tim_Cooprider
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.
Reply
Report
0
0
Ezel K.
Ezel K.
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 ?
Reply
0
0
D Rodrigues
D Rodrigues
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.
Reply
0
0
José
José
This comment was minimized by the moderator on the site
Thank you so much!, very useful.
Reply
0
0
David
David
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
Reply
0
0
jose
jose
This comment was minimized by the moderator on the site
Great Tool! After long research found this accurate toy :)
Reply
0
0
lloyd
lloyd
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.
Reply
0
0
tom vincent
tom vincent
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
Reply
0
0
AL
AL     tom vincent
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?
Reply
0
0
jimbosmiles
jimbosmiles
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?
Reply
0
0
Paul J
Paul J
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.
Reply
0
0
Caralyn
Caralyn
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
Reply
0
0
Robert Poole
Robert Poole     Caralyn
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.
Reply
0
0
Robert Poole
Robert Poole
This comment was minimized by the moderator on the site
This code truly saved the day. Thank you for sharing it.
Reply
0
0
MMB
MMB
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
Reply
0
0
Robert Poole
Robert Poole     MMB
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.
Reply
0
0
Dahamou ayman
Dahamou ayman
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 :)
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations