Application.FileSearch error

[kmiles]

The following code works fine in Excel 2003 but returns the following error in Excel 2007:

Run time error '445'
Object does not support this action


CODE:
Sub List_Files()

Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook

Set wbCodeBook = ThisWorkbook

With Application.FileSearch
.NewSearch
.LookIn = "C:\Departments\Inventory\"
.FileType = msoFileTypeAllFiles
.Filename = "20*"
If .Execute > 0 Then 'Workbooks in folder
For lCount = 1 To .FoundFiles.Count 'Loop through all.
'Open Workbook x and Set a Workbook variable to it
Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
Application.StatusBar = Str(lCount) & " files found - Now working on " & ActiveWorkbook.FullName
rteval = MsgBox("Now working on " & ActiveWorkbook.FullName)

wbResults.Close SaveChanges:=True

Next lCount
End If
End With
End Sub

 

[Von Pookie]

As I just discovered myself, Application.FileSearch was dropped from Office 2007

Looks like we'll have to use something else to get the job done. Sigh. I'm off to attempt to learn more about the Dir function now, myself...

 

[Norie]

They dropped FileSearch from Excel 2007? Why?

As far as I can see that's another reason not to upgrade.

Perhaps using Dir is the answer, but what about the FileSystemObject is the way to go.

 

[Von Pookie]

They dropped FileSearch from Excel 2007? Why?

From what I saw in my brief searching, it was buggy and they didn't want to bother to fix it, apparently.

Edit: Removal confirmed by Jon Peltier here:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=450752&SiteID=1

It *is* still shown in examples if you search for it in Help, though. I did a search for "filesearch" in Excel 2007 offline help and the first thing it returns is "filesearch.LookIn Property" with sample code using Application.FileSearch.

It's ridiculous.

 

[Jon Peltier]

They also didn't seem to think it was used all that much, given the more recent introduction of FSO. Never mind that once written, code stays around for a long time.

The online help is more up to date. I just hunted for FileSearch, and got no mention of it at all (not even a helpful note that it had been deprecated and a link to an alternative).

 

[kmiles]

this works

Thanks for the help !

The DIR command works fine.

Sub get_files()
Dim fName

fName = Dir("*.xlsm")
Do While Len(fName) > 0
Debug.Print fName
fName = Dir
Loop

End Sub

 

[Richard Schollar]

They also didn't seem to think it was used all that much, given the more recent introduction of FSO. Never mind that once written, code stays around for a long time.

That's so true Jon: should we ever upgrade here at work, there's a few files that will need to be modified to account for just this change. I wonder how many other VBA mods have been made? I liked the FileSearch - it was quick and simple to use...

 

[Jon Peltier]

Richard -

I don't think much else has changed in VBA, aside from glitches. However, I've run into some issues in charting which are more than glitches. In fact they are enough that I just can't upgrade until they're fixed.

 

[Norie]

I realise FileSearch could be buggy but I certainly used it.

Can't quite remember why though.

But I think it seemed to give more options than Dir(). eg searching subdirectories, searching for text in files

 

[Jon Peltier]

You would use Dir recursively to search subdirectories. I wonder if FSO can find text within files....