Open files, skip a folder

[Steph]

Hi everyone. I have some code below that opens all files within folders and
subfolders. Is there any way to tell it to ignore files in a folder than
contains the word "rollup". So if a folder under the main folder is named
"2005 rollup", skip that folder. Thanks!


Sub Open_all_files() 'Opens all files in folder AND Subfolders

Dim FSO As Scripting.FileSystemObject
Dim TopFolder As String
Set FSO = New Scripting.FileSystemObject
TopFolder = "C:\testfolder" '<<<<<<<<< CHANGE THIS TO TOP FOLDER
InnerProc FSO.GetFolder(TopFolder), FSO

End Sub

Sub InnerProc(F As Scripting.Folder, FSO As Scripting.FileSystemObject)

Dim SubFolder As Scripting.Folder
Dim OneFile As Scripting.File
Dim WB As Workbook

For Each SubFolder In F.SubFolders
InnerProc SubFolder, FSO
Next SubFolder
For Each OneFile In F.Files
Debug.Print OneFile.Path
If Right(OneFile.Name, 4) = ".xls" Then
Set WB = Workbooks.Open(Filename:=OneFile.Path)
'Do stuff here
End If
Next OneFile

End Sub

 

[Bob Phillips]

Sub InnerProc(F As Scripting.Folder, FSO As Scripting.FileSystemObject)

Dim SubFolder As Scripting.Folder
Dim OneFile As Scripting.File
Dim WB As Workbook

For Each SubFolder In F.SubFolders
If SubFolder.Name Like "*rollup*" Then
' do nothing
Else
InnerProc SubFolder, FSO
End If
Next SubFolder
For Each OneFile In F.Files
Debug.Print OneFile.Path
If Right(OneFile.Name, 4) = ".xls" Then
Set WB = Workbooks.Open(Filename:=OneFile.Path)
'Do stuff here
End If
Next OneFile

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Steph]

Thanks Bob!

Sub InnerProc(F As Scripting.Folder, FSO As Scripting.FileSystemObject)

Dim SubFolder As Scripting.Folder
Dim OneFile As Scripting.File
Dim WB As Workbook

For Each SubFolder In F.SubFolders
If SubFolder.Name Like "*rollup*" Then
' do nothing
Else
InnerProc SubFolder, FSO
End If
Next SubFolder
For Each OneFile In F.Files
Debug.Print OneFile.Path
If Right(OneFile.Name, 4) = ".xls" Then
Set WB = Workbooks.Open(Filename:=OneFile.Path)
'Do stuff here
End If
Next OneFile

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Jim Thomlinson]

Shouldn't it be

If LCase(SubFolder.Name) Like "*rollup*" Then

otherwise it is a case sensitive search? Also none of the subdirectories of
a *rollup* folder will be searched for folders not containing the word
rollup. This may or may not be a problem (probably not but worth noting).

 

[Steph]

Thanks Jim. I just came back on to reply saying that Bob's modification did
not work. I was wondering if it would be case sensitive. Thanks.

And as for the not searching subfolders under a folder named *rollup*, that
is what I was hoping would happen, so I'm ok there. Thanks again.

 

[Jim Thomlinson]

Bob is slipping. I think that is the second thing he missed this year and it
is only July. How the mighty have fallen... ;-)

 

[Bob Phillips]

Do you know, I answered a question today where the guy used the text "Pre",
and I wondered about case there, but I must admit I didn't even consider it
here :-(

Bob

 

[Jim Thomlinson]

It's almost like you're human...

 

[Steph]

Almost! You guys are awesome. Thanks again fr your help.