vba 2007 .filesearch (Office 2007)

Topic

New Reply

Greetings to the lounge

Not all in vba for 2007 is backward compatible.

The application.filesearch has been hidden.

I had a routine that populated a listbox with filename returned with the application.filesearch & foundfiles

‘With Application.FileSearch
‘ .LookIn = strFileLocation
‘ .Execute (msoSortByFileName)
‘ For i = 1 To .FoundFiles.Count
‘ If Right(.FoundFiles(i), 4) = “.doc” Then
‘ ListBox1.AddItem Mid(.FoundFiles(i), Len(strFileLocation) + 1)
‘ End If
‘ Next i
‘End With

This fails with Office 2007
My work around follows.
A reference to the microsoft scripting runtime is required

Dim fso As New FileSystemObject
Dim oFldr As Folder
Dim oFl As File
dim strFileLocation as string

strFileLocation = “somepath”

‘ Use this snippet for office 2007
Set oFldr = fso.GetFolder(strFileLocation)
For Each oFl In oFldr.Files
If Right(oFl.Name, 4) = “.doc” Then
ListBox1.AddItem oFl.Name
End If
Next

etc

Hope someone finds this useful.

Geof

Reply | Quote

Replies

Thanks, this is useful to know for anyone writing macros in Office 2007.

More info about changes in Word 2007 VBA can be found in What’s New, in particular elements that are now hidden in Object Model Changes Since Microsoft Office 2003.

On the one hand, FileSearch had its share of problems, on the other hand, it made searching a folder and its subfolders very easy. You have to do more work if you want to search subfolders using FileSystemObject or Dir.

Reply | Quote