Macro: make list of all files in all folder with sub folders

  • Thread starter Thread starter Snoopy
  • Start date Start date
S

Snoopy

Hey guys
You have kindly helped me before, and I hope for a little bit more
My macro (below) makes a filename-list of all FILES in one certain
(SUB)FOLDER, but I need to redesign it to make a list of ALL FILES in
FOLDER + ALL FILES in every SUBFOLDER (on every sublevel) in this
Folder-structure.

I think you guys know the trick - will you share it with me?
Best regards
Snoopy



My macro goes like this:
(How can I modify it?)

Sub ListAllFilesInFolderWithDir()
' Delete existing list in sheet FILES/column B to prepare for new
input
On Error Resume Next
Sheets("FILES").Select
Columns("B:B").Select
Selection.ClearContents

' Search FOLDERS named in celle D1, and make a folder list (Listbox
used for pick one of these for the next move)
Dim WorkFile As String
Dim i As Integer

Path = Range("D1").Value
i = 1
WorkFile = Dir(Path & "*.*")
Do While WorkFile <> ""
Cells(i, 2).Value = WorkFile
WorkFile = Dir()
i = i + 1
Loop

' When picked a subfolder in listbox: make a filename-list og files in
this subfolder (named in celle D2)
If Range("D2").Value <> "" Then
Range("D2").Copy
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End If

' Finally copy/paste the list into the rihgt position
Range("A5").Select
ActiveSheet.Paste
ActiveCell.Cells.Select
Sheets("TEMP").Select
Range("A1:B1").Select
Selection.Copy
Set tbl = ActiveCell.CurrentRegion
tbl.Offset(1, 1).Resize(tbl.Rows.Count - 1, tbl.Columns.Count -
1).Select
Selection.PasteSpecial Paste:=xlPasteFormulas,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
With Selection.Font
End With
Range("A:B").Activate
ActiveCell.Cells.EntireColumn.AutoFit
Range("A1").Select
End Sub
 
Chip Pearson shows how this can be done:

http://www.cpearson.com/excel/FolderTreeView.aspx

Perhaps you can get some tips from there (or even download his
example).

Hope this helps.

Pete










– Vis sitert tekst –
Click to expand...

Sorry Pete
I did not quite help me
I manage to list filenames in one-level subfolder, but nott ALL files
in sub- and sub-sub-folders and so on as well - e.g. all FILES
together in one folder with all underlying sub folders.

Best regards
Snoopy
(still howling at the moon....)
 
Below is some recursive code that should get you going. It lists every
directory, then files in that directory, followed by all the
subdirectories, their files and folders, and so on as deep as the
nested file structure goes. The code is based on a technique called
"recursion" in which a function calls itself, perhaps repeatedly. This
is used to loop through all the subfolders and their subfolders, and
so on. DoOneFolder deals with a single folder and then calls itself of
each subfolder, which is handled by DoOneFolder, which will in turn
call itself for all the subfolder of the current folder. The code
doesn't have all the bells and whistles of the Folder Tree add-in I
have at http://www.cpearson.com/excel/FolderTree.aspx but it should
get you going in the right direction.


Sub FolderListing()
' requires reference to Microsoft Scripting Runtime
Dim StartFolderName As String
Dim FSO As Scripting.FileSystemObject
Dim StartF As Scripting.Folder
Dim F As Scripting.File
Dim FF As Scripting.Folder
Dim R As Range

Set R = Worksheets("Sheet1").Range("A1")
StartFolderName = "C:\AlerterNET2" '<<<< CHANGE
Set FSO = New Scripting.FileSystemObject
Set StartF = FSO.GetFolder(StartFolderName)
R.Value = StartF.Path

Set R = R(2, 1)
For Each F In StartF.Files
R.Value = F.Name
Set R = R(2, 1)
Next F
For Each FF In StartF.SubFolders
DoOneFolder FF, R, FSO
Next FF
End Sub

Sub DoOneFolder(FF As Scripting.Folder, R As Range, FSO As
Scripting.FileSystemObject)
Dim F As Scripting.File
Dim SubF As Scripting.Folder
R.Value = FF.Path
Set R = R(2, 1)
For Each F In FF.Files
R(1, 2).Value = F.Name
Set R = R(2, 1)
Next F
For Each SubF In FF.SubFolders
DoOneFolder SubF, R, FSO
Next SubF
End Sub

Change the line marked with "<<<<" to the name of the folder in which
the listing should start.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]
 
Back
Top