List all files in a folder

[Guest]

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?

 

[Guest]

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

 

[Guest]

Yes

 

[Ivan F Moala]

Should have logged in 1st
for the last 2 posts

 

[Bruno_x]

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

This message was edited by Bruno on 2002-03-06 23:14

 

[Guest]

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