List All SubFolders In A Folder Excel 2013

[PY_]

I am having the hardest time searching for a macro that will list all folders and subfolders using VB. Does anyone have one that will work with Excel 2013? If i had a choice, i would like all folders to be listed in column A of Sheet1. So sub folders would not slide to the next column, they will just remain under column A.

[jaslake]

Hi PV

This works in Excel 2010...don't have 2013

Please Login or Register  to view this content.

[PY_]

That is close! I will see if i can modify it to fit my needs. Do you know how to modify it so it only lists Folder names? If it can remove the folder paths that would be even better.

[jaslake]

Hi PV

Try changing this line of Code

Please Login or Register  to view this content.

[PY_]

No that is still listing the files and multiple lines per folder.

I'm looking for a result similar to this if i ran it for C:\

All of this will be in a single column A:
Program Files
Sub Folder(s) of Program Files
Sub Folder(s) of each Sub Folder Inside Program Files
Sub Folder(s) of each Sub Folder of......etc
Windows
Sub Folder(s) of Windows
Sub Folder(s) of each Sub Folder Inside Windows
Sub Folder(s) of each Sub Folder of......etc
Etc...etc...

[Kenneth Hobson]

Please Login or Register  to view this content.

[PY_]

That works!
Is it possible to NOT show the subfolder information to the left so it only shows the folder name and not the path?

I was able to display just the folder name using this code in B1 copied down but if it can be done using vba, that would be less coding on the worksheet.

=TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",LEN(A1))),LEN(A1)))

[Trebor76]

Hi PY_,

Try this:

Please Login or Register  to view this content.

HTH

Robert

[PY_]

Robert, that worked great. I did tweak it a little because for me, i did not need the paths in A, just the names from B. Here is my final code and thanks again!

Please Login or Register  to view this content.

[Kenneth Hobson]

PY. Your Clear.Cells should be Cells.Clear. I am not sure why you deleted Column B. I think that you meant column A.

Please note that this DIR method may translate characters. e.g. The Daily Dose of Excel website page when saved in the browser creates a folder name with some characters like >>. These are translated by this method into an upperscore character. While you may not ever see that, I wanted to note it for others.

It appears that folders like this:
x:\a
x:\a\b
x:\a\b\a
would become
a
b
a.

If that is what you wanted, the last subfolder name, I would parse the last subfolder name into in an array. Normally, I would use an FSO method but that method fails for the scenario noted.

Please Login or Register  to view this content.

I recently helped a poster do something similar though they just want the folders in a parent folder. I put those folder names into a Data Validation dropdown list. For that scenario, fso, or DIR() methods suffice.