Listing folders and subfolders

Hi guys, I'm after some help.

I basically need to create a list of directories and subdirectories within a specific folder, for instance:

C:\Folder\
C:\Folder\Subfolder1\
C:\Folder\Subfolder2\
C:\Folder\Subfolder1\File1\
C:\Folder\Subfolder1\File2\
C:\Folder\Subfolder2\File1\
C:\Folder\Subfolder2\File2\

While I'm fairly competent with VBA within the confines of a spreadsheet, I don't know anything about folder structure-type programming, but I found something that kind of works (posted by Pike) here:http://www.excelforum.com/excel-prog...m-folders.html

However this lists all files, whereas I don't need to go that deep.

A further problem, which I found while running the above, is that I don't have access to a number of these directories. On an occasion where access is denied, this code stops. But I'd prefer if it just listed something like:

C:\Folder\Subfolder2\File2\FORBIDDEN

And moved onto the next folder.

Can anyone give me something that could work, or at least point me in the direction of a good walkthrough guide for this kind of programming?

Hello brokenbiscuits,

What do you mean by "access"? That the folder is hidden?

When I run in VBA without the If Error Resume Next statement, I get the following error:

Runtime Error '70':

Permission Denied

So what I'm basically wanting is some code I can use instead of the If Error Resume Next , along the lines of If Error then print Folder.Path & "DENIED"

Does that make sense?

Hello brokenbiscuit,

So, you want to list only the subfolders of a folder and not subfolders of those subfolders and flag any subfolder that you don't have access to, correct?

I want to list as many layers of subfolders (subfolders of subfolders of subfolders...) that I have access to, and flag any folders or subfolders that have restricted access.

So your original macro works perfectly for the first part.

Hello brokenbiscuits,

I have been looking through my files. I have a macro to list all files in a folder and its subfolders. So, I will make some changes to make list and check just the folders.

Hello brokenbiscuits,

Try this macro. It works for the all the folders, but I can't test the "Forbidden" part. I have access to everything on my system. This will list the folders on the ActiveSheet starting in cell "A1".

Please Login or Register  to view this content.

Calling the Macro

Please Login or Register  to view this content.

Hello brokenbiscuits,

Okay, I tested this some more and it has problems. If you are getting errors, it isn't your fault.

Hello brokenbiscuits,

I rewrote the macro from scratch. This version is more flexible. It allows to specify the output cell in the macro call.

Recursively List Folders and Subfolders in a Directory

Please Login or Register  to view this content.

Macro Example
List the folders on "Sheet1" starting in cell "A1".

Please Login or Register  to view this content.

Please Login or Register  to view this content.

The code only lists foldernames.

As you can see you don't have to use the commandprompt, because the code will do that for you.

Did you put the code in any macromodule in an Excel Workbook and run it ?
The result of the code will be a workbook containing all foldernames on drive C.

You may also need to adapt the path & name of the file in which the result will be put.
You can run the code step by step alt_F8 step into...