Application.FileSearch in Excel 2007

There is an issue which I am facing after I installed Office 2007. I had written a small VBA which would search for a file and if found will rename the file.

I used Application.FileSearch then. Now I am tying to do it again but unfortunately there is an error prompted stating that 'the object does not exist'

Set fSearch = Application.FileSearch

defPath = "H:\SourceSafe_1_Feb-28_Feb_2006\SecondSet"

Set rg = Range("MyFiles")
fSearch.LookIn = defPath

I have been frantically looking for changes in the VBA object model especially for Excel but cant seem to find them.

Microsoft ISV Community Center Forums1

Hi,

I only have 2003 so this is just advice rather than an absolute answer. In the VBA Editor use the Object Browser and do a search (or browse) for the Application object. See if the FileSearch object is there.

If that doesn't help can you find a feature on the GUI to do the File Search. If you can record a macro and that will give you an idea of what legacy code to use.

I hope that helps.

I am having this same problem as well doing a very similar thing but in Excell. The code is as follows

Set fs = Application.FileSearch

With fs
'retrieve all .doc files in DB directory

.lookin = [Internal Functions].dirName
.fileName = "*.doc"
.Execute
fC = .foundfiles.Count
If fC = 0 Then Exit Sub

etc. etc. Its easy to see what its doing.

If anyone has a workaround for this or knows whether they replaced this function with a different name please post it! I have looked for hours and been unable to figure out a reciprocal command, nor why they would take it out of the language. Thanks so much!

John

Hi

I went back to using VBScript to get the job done

here is a snippet

Function ReportFileStatus(filespec)
  Dim fso, msg
  Set fso = CreateObject("Scripting.FileSystemObject")
  If (fso.FileExists(filespec)) Then
   msg = filespec & " exists."
  Else
   msg = filespec & " doesn't exist."
  End If
  ReportFileStatus = msg
End Function

this should do the job for you... ofcourse with a bit of modification. 
Make sure that you save the file in XLS format rather than the new XLSX format.

Glax

Thought I'd just put a quick reply to this as I was having trouble with this file searching process. I used it in Access and then tried to use it when writing an Outlook macro, with no joy. I think it is specific to Access, because I managed to change my code as follows and it now works. Know this post is old, but if anyone has the same problem as us, could save hours of frustration. (You need to add the 'Microsoft Access 9.0 Object Library' to your references).

Sub processFiles()

Dim app02 As New Access.Application
Dim filename
Dim NumFiles
NumFiles = 0

line1:
With Access.Application.FileSearch
.NewSearch
.LookIn = "C:\Temp\Dataprep\"
.SearchSubFolders = False
.MatchTextExactly = True
.filename = "*.*"
.Execute

End With

If FileSearch.FoundFiles.Count > 0 Then
filename = FileSearch.FoundFiles.Item(1)

'Put your code that processes files here

Name filename As Left(filename, 17) & "processed\" & Mid(filename, 18, 60)
NumFiles = NumFiles + 1
GoTo line1
Else

End If
MsgBox NumFiles & " File(s) Processed!", vbOKOnly, "Finished!"
End Sub

All this code does is loop through the dataprep folder and place all the files it finds in a 'processed' sub folder.

This is placed in an excel module and seems to do the trick. I know the code's probably badly written but hope it will be of use to someone.

Have a quick look at my response to the original question. Hope that helps mate.

Thanks! Brilliant!

DieZeL wrote:

Hi

I went back to using VBScript to get the job done

here is a snippet

Function ReportFileStatus(filespec)
  Dim fso, msg
  Set fso = CreateObject("Scripting.FileSystemObject")
  If (fso.FileExists(filespec)) Then
   msg = filespec & " exists."
  Else
   msg = filespec & " doesn't exist."
  End If
  ReportFileStatus = msg
End Function

this should do the job for you... ofcourse with a bit of modification. 
Make sure that you save the file in XLS format rather than the new XLSX format.

Glax

[quote user="DieZeL"]

Function ReportFileStatus(filespec)
  Dim fso, msg
  Set fso = CreateObject("Scripting.FileSystemObject")
  If (fso.FileExists(filespec)) Then
   msg = filespec & " exists."
  Else
   msg = filespec & " doesn't exist."
  End If
  ReportFileStatus = msg
End Function

Glax