MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Analyze directory contents from Excel macro

Posted by Jerry Weinstein on October 10, 2001 7:05 AM

I would like to use Excel VBA and filesearch to produce an exhaustive directory listing that includes all subfolders and their contents. So far, I am able to list contents (file names) for a given directory but need a hint as to how to find any subfolders in that directory and recurse through them as well:

Here is what I have so far:

Sub NewGet()
Dim a, f As String
a = "c:\foundfiles.txt"
Open a For Output As #1
Set fs = Application.FileSearch
With fs
.LookIn = "c:\my documents"
.FileName = "*.*"
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
f = .FoundFiles(i)
Print #1, f
Next i
MsgBox "There were no files found."
End If

End With
Close #1
End Sub

Posted by Jerry Weinstein on October 10, 2001 9:12 AM

Found my own answer.

.SearchSubFolders = True
before the IF test ...