Retrieve File Names in Directories

[sharonm]

Can anyone tell me how or if it is possible to start at a particular
directory and drill down to all subdirectories and get the names of files and
the associated paths of each? I was thinking you can do this with the File
System Object within Excel VBA?? If anyone could point me to good web site
that covers this, it would be appreciated.

I basically want to start at one directory and follow all paths until no
more directories. So drill both down and accross. Hopefully I am explaining
correctly.

 

[dan dungan]

Hi SharonM,

What do you want to do with them after you get them?

Dan

 

[sharonm]

At a minimum get a list in an Excel worksheet with the File name and Path.

But I also might need to rename one or more of the files if possible.

Thanks!

 

[JLatham]

Recursion and the FSO, courtesy of Chip Pearson:
http://www.cpearson.com/EXCEL/RecursionAndFSO.htm

I also have full working code that pretty much does what your asking for. I
think it's based on Chip's code, but I'm not certain (I didn't write down the
original source when I stole it <g> before modifying it). If you'd like a
sample workbook with my code in it, get in touch via email to (remove spaces)
HelpFrom @ JLatham Site. com

 

[Chip Pearson]

Here's some code I posted a while ago. Change the values marked with
'<<< to meet your needs and then run the StartHere procedure.
StartFolderName is the fully qualified name of the folder whose
contents you want to list. Indent indicates whether subdirectories and
file should be indented from their parent folder. ListFiles indicates
whether to list files in addition to folders. R is the range address
at which the listing is to begin. See also
http://www.cpearson.com/Excel/FolderTree.aspx and
http://www.cpearson.com/Excel/FolderTreeView.aspx and
http://www.cpearson.com/Excel/RecursionAndFSO.htm


Sub StartHere()
Dim FSO As Scripting.FileSystemObject
Dim StartFolderName As String
Dim StartFolder As Scripting.Folder
Dim F As Scripting.File
Dim SubF As Scripting.Folder
Dim R As Range
Dim Indent As Boolean
Dim ListFiles As Boolean
Set FSO = New Scripting.FileSystemObject

StartFolderName = "C:\Utilica" ' <<< Start Folder
Indent = True '<<< Indent listing
ListFiles = False '<<< List file names
Set R = Range("A1") '<<< List start cell

Set StartFolder = FSO.GetFolder(StartFolderName)
ListSubFoldersAndFiles FSO, StartFolder, R, Indent, ListFiles

End Sub

Sub ListSubFoldersAndFiles(FSO As Scripting.FileSystemObject, _
FF As Scripting.Folder, _
R As Range, _
Indent As Boolean, ListFiles As Boolean)

Dim SubF As Scripting.Folder
Dim F As Scripting.File
R.Value = FF.Path
For Each SubF In FF.SubFolders
Set R = R(2, 1)
If Indent = True Then
Set R = R(1, 2)
End If
ListSubFoldersAndFiles FSO, SubF, _
R, Indent, ListFiles
If Indent = True Then
Set R = R(1, 0)
End If
Next SubF

If ListFiles = True Then
If Indent = True Then
Set R = R(1, 2)
End If
For Each F In FF.Files
Set R = R(2, 1)
R.Value = F.Name
Next F
If Indent = True Then
Set R = R(1, 0)
End If
End If
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com