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)

"suzupis" <suzupis@discussions.microsoft.com> wrote in message
news:B394D134-214D-43BE-AB71-EAD42EA09E94@microsoft.com...
> 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?

[jdvmanen]

You're wonderfull! i can just still use my search-sheet, wich searches for all kind of files in folders declared in a list on a subsheet, and this searchfunction wil create hyperlinks to it!

Thank you!! ( and yes, i'm an office 2007 user, and i love it )