Prev: Macro to copy and paste all rows of data in between two words
Next: Keep text box visible
From: hervinder on 22 Apr 2010 06:49
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

From: Dave Peterson on 22 Apr 2010 08:33
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.


hervinder wrote:
>
> 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
 | 
Pages: 1
Prev: Macro to copy and paste all rows of data in between two words
Next: Keep text box visible