Listing folders with VBA

[mgkyle]

I have found a lot of posts which show me how to list a directory tree in an Excel file, usually using a macro. I'm just uninitiated with VBA / VBS and simply don't have the time to figure it out now (it really isn't part of my job description... I'm rather doing a favor here). So, this seemed like a good forum where I could beg some help.

I need to be able to re-generate an excel-based list of clients at will. The client names are subdirectories of different case types. There aren't tons of case types, right now maybe 20 but I need to be able to add case types without breaking the script.

\case_types\last,_first_name

Anyway, what I would like to see for output is the names in the first column and the case type directory where the name is located in the second column.

last,_first_name | case_type

I don't want any of the file names or subdirectories of the last,_first_name directory displayed, and I don't want filenames that happen to be in the root directory displayed.

I appreciate any help... I'm using Excel 2003.

Thanks,

--Matthew

[stanleydgromjr]

mgkyle,

The below code will have you navigate to the folder you want to report on, and in the active worksheet, column A will be the filenames, column B will be the full file paths.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Press and hold down the 'ALT' key, and press the 'F11' key.

Insert a Module in your VBAProject, Microsoft Excel Objects

Copy the below code, and paste it into the Module1.

Please Login or Register  to view this content.

Then run the "ListFilesFromFolderAndSubFolders" macro.


Have a great day,
Stan

[royUK]

Do you mean Visual Basic for Applications (VBA) or Visual Basic Scripting (VBS)?

[mgkyle]

Roy:

VBA or VBS would be fine, I think since I am looking to end with an Excel Spreadsheet VBS would be appropriate.

Stan:

Thanks for the start. Unfortunately, what I want listed are directories and subdirectories (and not files), but that's a bit of an idea.

It isn't too easy to explain directory structures without an image... so I made one. Maybe it will help.

dir.gif

If my directory structure looked like the picture, my Excel file should look something like the following.

Please Login or Register  to view this content.

If the Client_Name directories have files or subdirectories I do not want those listed.

Some code that does part of what I want is the following (from http://www.excelforum.com/excel-misc...s-to-file.html):

Please Login or Register  to view this content.

The trouble with that is that I don't want the directory structure, and I don't want third level subdirectories.

[mgkyle]

I just came up with an idea. If someone could suggest how to strip the leading directory structure from that (remove C:\Firm\ from each field), all that would be left are the two items I want:

Case_type\Client_name

and the Case_type directories without a '\'


Then all that would be left is to move Case_type\ from the first column and put it in the second column without the '\', and deleting the Case_type items without any associated client name.

Appreciate any help. This is probably something I can find... but I'm a bit burnt out on searching for script

Thanks all,

--Matthew

[mgkyle]

Well, after opening another post ( http://www.excelforum.com/excel-prog...xt-column.html ) to work out the sorting of the data and figuring out how to change the width of the columns to fit the contents, I'm set!

Just in case someone trying to figure this out happens to want to do something like this, I'll post my final code. Thanks to everyone who helped, once again.

Please Login or Register  to view this content.

Thanks again, everyone!

[edwincastellino]

Hi,

I have used the following code provided but it does not give me the details of the sub folders within the main folder. I would need the details of the sub folders too. Please help

Sub ListFilesFromFolderAndSubFolders()
'
' http://vbaexpress.com/forum/showthre...t=10829&page=2
'
Dim f As Object, FSO As Object, flder As Object
Dim folder As String
Dim wb As Workbook, ws As Worksheet
Set wb = ActiveWorkbook
Set ws = ActiveSheet
Set FSO = CreateObject("Scripting.FileSystemObject")
With Application.FileDialog(msoFileDialogFolderPicker)
.Show
If .SelectedItems.Count = 0 Then
MsgBox "Cancel Selected"
End
End If
folder = .SelectedItems(1)
End With
For Each flder In FSO.GetFolder(folder).SubFolders
For Each f In FSO.GetFolder(flder.Path).Files
ws.Range("A" & ws.Rows.Count).End(xlUp).Offset(1, 0) = f.Name
ws.Range("B" & ws.Rows.Count).End(xlUp).Offset(1, 0) = f.Path
Next
Next
End Sub

[protonLeah]

@edwincastellino:
Welcome to the Forum, unfortunately:

1. Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
==============================================================================================
2. Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

3. Post your workbook directly to the forum:
To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'.
To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.


On this page, below the message box, you will find a button labelled 'Manage Attachments'.
Clicking this button will open a new window for uploading attachments.


You can upload an attachment either from your computer or from another URL by using the appropriate box on this page.
Alternatively you can click the Attachment Icon to open this page.


To upload a file from your computer, click the 'Browse' button and locate the file.


To upload a file from another URL, enter the full URL for the file in the second box on this page.
Once you have completed one of the boxes, click 'Upload'.


Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.