MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Extract List of SubDirectories or list of files.


Posted by JAF on July 07, 2000 4:21 AM

This is a "2-questions-in-one" kind of query.

What I would like to do is:
1) to be able to generate a list in Excel of all the sub-directories contained in a specified directory.

2) to be able to generate a list in Excel of all the files contained in a specified directory.

I haven't got a clue as to how to go about this (or even if it's possible). Any suggestions?


JAF


Posted by Ryan on July 07, 0100 5:53 AM

JAF,
This can be done. Go to:
http://edc.bizhosting.com/english/index.htm
From here go to VBA Part2 on the left hand side and then click on files/folders. There is a hyperlink here that will take you to the code. It's going to take a little manipulation but it's not bad. Hope this helps.

Ryan

Posted by Ryan on July 12, 0100 11:41 AM

This will list the names of the sheets down column A on the worksheet you are running the macro from.

Sub ListSheets
Dim x as Integer
Dim Sheet as Worksheet

x = 0
For Each Sheet in Worksheets
x = x+1
Cells(x,1).Value = Sheet.Name
Next Sheet
End Sub

Ryan

Posted by david on July 11, 0100 9:59 PM

How about finding all the sheet names in the workbooks too