List all folders and files

[Paul Black]

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

 

[Chip Pearson]

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

 

[Paul Black]

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





- Show quoted text -

Thanks Chip,

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

Kind regards,
Paul