Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro to list folders only, not files | Excel Worksheet Functions | |||
Using VBScript to list all folders in a directory and files ... | Excel Programming | |||
Map/List of folders, subfolders & files | Excel Programming | |||
Can anyone help me Create Excel list of files in windows folders | Excel Worksheet Functions | |||
Dir() function to return either List of files or folders | Excel Programming |