How can I do the equivalent of "filesearch" in Office 2007?

[suzupis]

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?

 

[Bob Phillips]

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)

 

[Pedro]

Hi,

That will only look in the folder.
What changes should be made in order to also look into subfolders?
Regards
Pedro