Retrieving file list with Excel 2007 VBA

[phudgens]

Far be it from me to question Bill Gates reasons for eliminating Application.FileSearch from Office 2007 VBA. But he has, and now I have to re-write code for Excel 2007 VBA to retrieve a list of files from a user specified folder, and search all sub-folders. I came up with the following:

Sub ShowFolderInfo(ByRef folderspec As String)
Dim fs, f, s, fc, fc1, F1
Dim CDWorkBook As String, CDWorkSheet As String
Dim WriteCell As Object

CDWorkBook = ThisWorkbook.Name
CDWorkSheet = "CD File Summary"

With Workbooks(CDWorkBook).Worksheets(CDWorkSheet)
Set WriteCell = .Range("P25000").End(xlUp)
End With

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)
Set fc1 = f.Files
For Each F1 In fc1
WriteCell.Offset(iFileCnt, 0).Value = folderspec & "\" & F1.Name
Next F1
Set fc = f.SubFolders
For Each F1 In fc
ShowFolderInfo folderspec & "\" & F1.Name
Next F1
Set fc1 = Nothing
Set fs = Nothing
Set f = Nothing

End Sub

This code works, but I was wondering if anyone could take a look at this and verify that it will work as it is supposed to, and suggest anything that could be done better.

Thanks,
Paul Hudgens

 

[PHV]

I'd increment iFileCnt in the loop.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[phudgens]

Oops - I do increment iFileCnt in the actual code. I accidentally deleted that line in preparing this for submission.

So there really is no way in Excel 2007 VBA to retrieve a list of files in a folder, including all sub-folders, except via a recursive function?

Thanks,
Paul H.