| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open files, skip a folder
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Open files, skip a folder
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" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Open files, skip a folder
Thanks Bob!
"Bob Phillips" wrote in message ... 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" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Open files, skip a folder
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). -- HTH... Jim Thomlinson "Bob Phillips" wrote: 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" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Open files, skip a folder
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" wrote in message ... 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). -- HTH... Jim Thomlinson "Bob Phillips" wrote: 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" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Open files, skip a folder
Bob is slipping. I think that is the second thing he missed this year and it
is only July. How the mighty have fallen... ;-) -- HTH... Jim Thomlinson "Steph" wrote: 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" wrote in message ... 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). -- HTH... Jim Thomlinson "Bob Phillips" wrote: 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" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Open files, skip a folder
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" wrote in message ... Bob is slipping. I think that is the second thing he missed this year and it is only July. How the mighty have fallen... ;-) -- HTH... Jim Thomlinson "Steph" wrote: 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" wrote in message ... 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). -- HTH... Jim Thomlinson "Bob Phillips" wrote: 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" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Open files, skip a folder
Almost! You guys are awesome. Thanks again fr your help.
"Jim Thomlinson" wrote in message ... It's almost like you're human... -- HTH... Jim Thomlinson "Bob Phillips" wrote: 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" wrote in message ... Bob is slipping. I think that is the second thing he missed this year and it is only July. How the mighty have fallen... ;-) -- HTH... Jim Thomlinson "Steph" wrote: 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" wrote in message ... 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). -- HTH... Jim Thomlinson "Bob Phillips" wrote: 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" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can't Open Certain Excel Files in Folder | Excel Discussion (Misc queries) | |||
| Open files in folder - skip if already open | Excel Programming | |||
| Open all files in a folder | Excel Programming | |||
| Open Excel files in a folder | Excel Programming | |||
| open all files in a folder and ... | Excel Programming | |||
![Steph[_3_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
