List all files in a folder

I am writing a macro to get a list of subfolders and files in a folder and link the cells to the corresponding files. Here is the macro


Sub ShowAllList(folderspec)
Dim fs, f, f1, f2, f3, fc, fd, fe
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)
Set fc = f.Files
Set fd = f.subfolders
i = 1
For Each f1 In fc
Cells(i, 1) = f1.Name
ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 1), Address:=f1
i = i + 1
Next
For Each f2 In fd
Cells(i, 1) = f2.Name
i = i + 1
Set fe = f2.Files
For Each f3 In fe
Cells(i, 1) = f2.Name
Cells(i, 2) = f3.Name
ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 2), Address:=f3
i = i + 1
Next
Next
End Sub

Sub listallfiles()
ShowAllList ("h:kcy")
End Sub


It works if in there is only one level of subfolders (which is usually not the case). I want to rewrite it so that it can handle arbitray numbers of levels of subfolders. Can anybody help? Thank you.

[Dave Hawley]

Hi

Have you tried the FileSearch Object?

Example of what dave suggested;

Sub List_Files()
Dim cDir as string
cDir = "C:Downloads"

With Application.FileSearch
.NewSearch
.FileType = msoFileTypeAllFiles
.LookIn = cDir
.SearchSubFolders = True
.Execute
For i = 1 To .FoundFiles.Count
Cells(i, 1) = .FoundFiles(i)
Next

End sub


Ivan

Yes

[Ivan F Moala]

Should have logged in 1st
for the last 2 posts

[Bruno]

Nice code Ivan
There's only a END WITH missing before END SUB
Let's build the code a bit more...
Be sure there is a Userform1 with a Listbox1

Sub List_Files()

Dim cDir As String

cDir = "C:Downloads"



UserForm1.ListBox1.Clear

With Application.FileSearch

    .NewSearch

    .FileType = msoFileTypeAllFiles

    .LookIn = cDir

    .SearchSubFolders = True

    .Execute

    For i = 1 To .FoundFiles.Count

        Cells(i, 1) = .FoundFiles(i)

        UserForm1.ListBox1.AddItem .FoundFiles(i)

    Next

End With

'show the list

UserForm1.Show

' as an example open the selected file

Workbooks.Open FileName:=UserForm1.ListBox1.Value

End Sub

_________________

Bruno

[ This Message was edited by: Bruno on 2002-03-06 23:14 ]

On 2002-03-06 22:33, Anonymous wrote:
Example of what dave suggested;

Sub List_Files()
Dim cDir as string
cDir = "C:Downloads"

With Application.FileSearch
.NewSearch
.FileType = msoFileTypeAllFiles
.LookIn = cDir
.SearchSubFolders = True
.Execute
For i = 1 To .FoundFiles.Count
Cells(i, 1) = .FoundFiles(i)
Next

End sub


Ivan

Thank you.
But can I link the file names to the files, list details such as file sizes etc.

I tried the following (I don't understand FileSearch well):

Sub ShowFileList(folderspec)
Dim fs, f, f1, s, sf
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)
Set sf = f.Files
i = ActiveCell.Row
For Each f1 In sf
Cells(i, 1) = f1.Path
Cells(i, 2) = f1.Size
Cells(i, 3) = f1.DateLastModified
ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 1), Address:=f1
i = i + 1
Next
Cells(i, 1).Select
End Sub

Sub Showdd(folderspec)
Dim fs, f, f1, s, sf
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)
ShowFileList (f)
Set sf = f.SubFolders
For Each f1 In sf
Showdd (f1)
Next
End Sub


Sub ShowAll()
Cells(1, 1).Select
Showdd ("h:kcy")
End Sub