Excel 2003 help? Application.Filsearch no longer working?

This brings back some bad memories... I myself had several macros using Filesearch object that stopped working since Excel 2007 (no Filesearch AND ribbons all at once... what a nightmare).

I don't think there is a direct replacement for Filesearch so the solution would depend on what you wanted to do with Filesearch.

The DIR function only returns filespecs matching a given filespec. I find this only useful to confirm if a file exists but, although it can be done, is too cumbersome to use to list files.

The substitute I currently use is file system objects. Here is an example of some code that will display each Excel file in a particular folder:

Sub test()

Dim myFolder, myFile As Object, sMonPath As String

sMonPath = "c:\"

Set myFolder = CreateObject( "Scripting.FileSystemObject"). GetFolder(sMonPath)

For Each myFile In myFolder.Files

If InStr(1, myFile.Type, "Microsoft Office Excel") Then MsgBox (myFile.Name)

Next myFile

Set myFolder = Nothing

End Sub

There are a few disadvantages (cannot prefilter filetypes and subfolders are a bit more work) but at least it works for 2007+ and also, to my knowledge, with older versions (eg 2003). As I mentioned, I remember the pain of having to recode a lot of macros because of this so let me know if you need additional help. Good luck.