Exclude one folder in a filesearch

[hervinder]

I Have this simple macro that will list all the files in a folder including
all the subfolders

Sub file_list()
With Application.FileSearch
..NewSearch
..LookIn = "C:\My Documents\"
..SearchSubFolders = True
..Filename = "*.*"
..FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
For I = 1 To .FoundFiles.Count
Cells(I, 1) = .FoundFiles(I)
Next I
Else
Cells(I, 1) = "No files Found"
End If
End With
End Sub


The folder i am looking in being, in this case being C:\My Documents\ has
many sub folders. I want it to list the files in all but two of them, is it
possible to modify the code to exlcude two specified subfolders. Something
along the lines of

If .subfolder ="sub1" then goto next

thanks in advance

Hervinder

 

[Dave Peterson]

You could use something like:

Option Explicit
Sub file_list()
Dim i As Long
Dim sCtr As Long
Dim SubFoldersToAvoid As Variant
Dim myPath As String
Dim myFolderName As String
Dim SkipIt As Boolean

myPath = "C:\my documents"
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If

'no back slashes at the end!
SubFoldersToAvoid = Array("excel", "Word")

With Application.FileSearch
.NewSearch
.LookIn = myPath
.SearchSubFolders = True
.Filename = "*.*"
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
SkipIt = False
For sCtr = LBound(SubFoldersToAvoid) _
To UBound(SubFoldersToAvoid)
myFolderName = myPath & SubFoldersToAvoid(sCtr) & "\"
If UCase(Left(.FoundFiles(i), Len(myFolderName))) _
= UCase(myFolderName) Then
SkipIt = True
Exit For
End If
Next sCtr
If SkipIt = True Then
'skip it
Else
Cells(i, 1) = .FoundFiles(i)
End If
Next i
Else
Cells(i, 1) = "No files Found"
End If
End With
End Sub

======
Just a warning...

xl2007 doesn't support application.filesearch.