Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I do the equivalent of "filesearch" in Office 2007?
I have several macros written for excel which use the application.filesearch
method to retrieve files within a directory. It appears that Office 2007 no longer supports this. ("File=Search is no longer in MS Office with the 2007 edition. The Office team has relinquished responsibilities for search back to the Windows team.") Any suggestions on how to replace this code -------------------------------------------------------------------------------------- Application.DisplayAlerts = False path = ActiveSheet.Parent.path Set FileS = Application.FileSearch With FileS .NewSearch .Filename = "" .LookIn = path .SearchSubFolders = True .Execute End With For Each F In Application.FileSearch.FoundFiles Workbooks.Open Filename:=F ' ' do stuff ' Workbooks(2).Close Next F Application.DisplayAlerts = True --------------------------------------------------------- One suggestion is to use Use Windows Script Host's FileSystemObject. "It's not quite the same, and if the OP is taking advantage of XL11 and priors' ability to index .xls files, there isn't comparable OS functionality in Wndows XP, so unlikely in any previous Windows version." Would anyone have a sample of the code to do this? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I do the equivalent of "filesearch" in Office 2007?
Sub ProcessFiles() Dim sFolder As String Dim Folder As Object Dim file As Object Dim this As Workbook Dim FSO As Object Set FSO = CreateObject("Scripting.FileSystemObject") Set this = ActiveWorkbook sFolder = ActiveSheet.Parent.Path If sFolder < "" Then Set Folder = FSO.GetFolder(sFolder) For Each file In Folder.Files If file.Type = "Microsoft Excel Worksheet" Then Workbooks.Open Filename:=file.Path ' ' do stuff ' ActiveWorkbook.Close End If Next file End If ' sFolder < "" End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "suzupis" wrote in message ... I have several macros written for excel which use the application.filesearch method to retrieve files within a directory. It appears that Office 2007 no longer supports this. ("File=Search is no longer in MS Office with the 2007 edition. The Office team has relinquished responsibilities for search back to the Windows team.") Any suggestions on how to replace this code? -------------------------------------------------------------------------- ------------ Application.DisplayAlerts = False path = ActiveSheet.Parent.path Set FileS = Application.FileSearch With FileS .NewSearch .Filename = "" .LookIn = path .SearchSubFolders = True .Execute End With For Each F In Application.FileSearch.FoundFiles Workbooks.Open Filename:=F ' ' do stuff ' Workbooks(2).Close Next F Application.DisplayAlerts = True --------------------------------------------------------- One suggestion is to use Use Windows Script Host's FileSystemObject. "It's not quite the same, and if the OP is taking advantage of XL11 and priors' ability to index .xls files, there isn't comparable OS functionality in Wndows XP, so unlikely in any previous Windows version." Would anyone have a sample of the code to do this? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I do the equivalent of "filesearch" in Office 2007?
Hi,
That will only look in the folder. What changes should be made in order to also look into subfolders? Regards Pedro "Bob Phillips" wrote: Sub ProcessFiles() Dim sFolder As String Dim Folder As Object Dim file As Object Dim this As Workbook Dim FSO As Object Set FSO = CreateObject("Scripting.FileSystemObject") Set this = ActiveWorkbook sFolder = ActiveSheet.Parent.Path If sFolder < "" Then Set Folder = FSO.GetFolder(sFolder) For Each file In Folder.Files If file.Type = "Microsoft Excel Worksheet" Then Workbooks.Open Filename:=file.Path ' ' do stuff ' ActiveWorkbook.Close End If Next file End If ' sFolder < "" End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "suzupis" wrote in message ... I have several macros written for excel which use the application.filesearch method to retrieve files within a directory. It appears that Office 2007 no longer supports this. ("File=Search is no longer in MS Office with the 2007 edition. The Office team has relinquished responsibilities for search back to the Windows team.") Any suggestions on how to replace this code? -------------------------------------------------------------------------- ------------ Application.DisplayAlerts = False path = ActiveSheet.Parent.path Set FileS = Application.FileSearch With FileS .NewSearch .Filename = "" .LookIn = path .SearchSubFolders = True .Execute End With For Each F In Application.FileSearch.FoundFiles Workbooks.Open Filename:=F ' ' do stuff ' Workbooks(2).Close Next F Application.DisplayAlerts = True --------------------------------------------------------- One suggestion is to use Use Windows Script Host's FileSystemObject. "It's not quite the same, and if the OP is taking advantage of XL11 and priors' ability to index .xls files, there isn't comparable OS functionality in Wndows XP, so unlikely in any previous Windows version." Would anyone have a sample of the code to do this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002 to 2007 Macro containing "Filesearch" | Setting up and Configuration of Excel | |||
Problems with "Send to email recipient" in Office 2007 | Excel Discussion (Misc queries) | |||
how to change "move selection after enter" in office 2007 | Excel Discussion (Misc queries) | |||
How to change text color in a cell "comment" in Office 2007? | Excel Worksheet Functions | |||
where can I find the "Calculated Field" in Office 2007 version? | Excel Worksheet Functions |