List all folders and files
 

Good afternoon everybody,

I have a memory stick with numerous folders and files.
What I would like to do is list in a spreadsheet all the folders and
files within those folders please.

Thanks in advance,
Paul

List all folders and files
 

I have an add-in that will do exactly that, and more. See
http://www.cpearson.com/excel/FolderTree.aspx for details. If you to
just have code, the following code will create a list. Copy this code,
then in VBA go to the Tools menu, choose References, and scroll down
to and check "Microsoft Scripting Runtime". Change the lines of code
marked with <<< to your desired needs. StartFolderName is the name of
the folder from which to start the list. Indent indicates whether to
indent the listing in a tree-like structure. ListFiles indicates
whether to list file names in addition to folder names. R is the cell
in which the listing is to begin.


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



On Tue, 23 Feb 2010 08:46:45 -0800 (PST), Paul Black
wrote:

Good afternoon everybody,

I have a memory stick with numerous folders and files.
What I would like to do is list in a spreadsheet all the folders and
files within those folders please.

Thanks in advance,
Paul

List all folders and files
 

On Feb 23, 5:24*pm, Chip Pearson wrote:
I have an add-in that will do exactly that, and more. Seehttp://www.cpearson.com/excel/FolderTree.aspxfor details. If you to
just have code, the following code will create a list. Copy this code,
then in VBA go to the Tools menu, choose References, and scroll down
to and check "Microsoft Scripting Runtime". *Change the lines of code
marked with <<< to your desired needs. StartFolderName is the name of
the folder from which to start the list. Indent indicates whether to
indent the listing in a tree-like structure. ListFiles indicates
whether to list file names in addition to folder names. R is the cell
in which the listing is to begin.

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, LLCwww.cpearson.com

On Tue, 23 Feb 2010 08:46:45 -0800 (PST), Paul Black



wrote:
Good afternoon everybody,

I have a memory stick with numerous folders and files.
What I would like to do is list in a spreadsheet all the folders and
files within those folders please.

Thanks in advance,
Paul- Hide quoted text -

- Show quoted text -

Thanks Chip,

but for some reason I can't get it to work.

Kind regards,
Paul