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:
Dim a, f As String
a = "c:\foundfiles.txt"
Open a For Output As #1
Set fs = Application.FileSearch
.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
MsgBox "There were no files found."