List of Files in SubFolders (VBA/Excel/XP)

[MacroAlan]

My problem is to get a list of folders, subfolders and filenames.

I have many, many subfolders. First I want to obtain the list of files and their subfolder paths to a spreadsheet. Later I will be writing something that opens them based on naming convention criteria to make changes.

I am programming in XP but my customers could be in 97, XP, or 2003.

[Kevin]

You can write a little script (VB or J or use COM objects from VBA) that uses recursion to get what you want.
Attached is my version of same. (rename .txt to .vbs)

[pieterse]

You could use this (in a normal module):

Public lCount As Long
Public sFilelist() As String

Sub Example()
dim lLocalCount as Long
FindFiles("C:WINDOWS","*.xls")
MsgBox "Found: " & lCount & " files"
For lLocalCount=1 to lCount
msgbox sFiles(lLocalcount)
Next
End Sub

Sub FindFiles(sLocalPath As String, sSpec As String)
Dim lLocalCount As Long
Application.StatusBar = "Scanning " & sLocalPath & " for " & sSpec
With Application.FileSearch
.NewSearch
.LookIn = sLocalPath
.SearchSubFolders = True
.Filename = sSpec
.MatchTextExactly = False
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
For lLocalCount = 1 To .FoundFiles.Count
ReDim Preserve sFilelist(lCount + 1)
lCount = lCount + 1
sFilelist(lCount) = .FoundFiles(lLocalCount)
Next
End If
End With
Application.StatusBar = False
End Sub

[MacroAlan]

Turned out that I already had a cool piece of code. It had a fatal error (I killed the path stream in an intermediate step).

Thanks to everyone that tried. BTW: I could not get my user to turn on Scripting; against company policy.