Excel 2007 VBA FileSearch missing

Question

Someting happened with FileSearch object in Excel 2007. In documentation it is said that FileSearch is hiden. Does it mean that there is no way to search for files using Excel VBA anymore?

Answer 1

You can still search using the Dir function - lots of examples on the web if you search for 'excel 2007 vba filesearch', for example this post.

Answer 2

I would add a reference to the Microsoft Scripting Runtime library. That gives you access to the FileSystemObject, which lets you pretty easily test for the existence of a file and/or iterate through directories, doing your own search.

Answer 3

If you want to search on custom document properties, there's currently no way to do it. You have to narrow the search with Dir and open each one individually.

Answer 4

As Steve said you can use the FSO object to search for files using a given root path and filter expression. There is good documentation available on fitlering by extension (remember to include the extensions you want or filter by .xls) and using the objects in the FSO object model.

For an explanation (by msft) on the depreciation of FileSearch method. see - http://support.microsoft.com/kb/920229

For a basic pattern to using the FSO for searching a set of directories recursively with the FSO see - http://support.microsoft.com/kb/185601

You can still use the Application.GetOpenFilename if you want to get back the path to a workbook.