How to replace office 2003 application.filesearch function

I was using the script below.  Microsoft aparantly no longer supports this function.  What function can replace the below in office 2007, remaining compatible with office 2003.  I have seen some pretty scary looking scripts that are huge, and hope someone has a faiilry simple replacement :)
I would like to have the same code for excel and access 2007, but if not, access is the more important of the 2.

Thanks in advance
' Counts files in the directory
With Application.FileSearch
       .LookIn = "\\xxxxx\Share\Daily\" & Range("Q3").Value
      .FileType = msoFileTypeExcelWorkbooks
     .Execute
   Dim found As String
  found = .FoundFiles.Count
   End With
        
  If found <> 9 Then
   
MsgBox ("WARNING!  There are only " & " " & found & " files present.  You need to review prior to the rollup!")
Call openDIR
Else
MsgBox ("There are" & " " & found & " files present.  You can proceed to script rollup!")
End If

Open in new window

MrAutomateAsked:
Who is Participating?
 
Saurabh Singh TeotiaConnect With a Mentor Commented:
Ahh change this line...
For Each objFile In objSubFolder.Files

to this..
 For Each objFile In objFolder.Files
0
 
Saurabh Singh TeotiaCommented:
You can use the following code to do what you are looking for, To run this code you need to select microsoft scripting runtime dictionary in you library, To do that, In vb editor go to tools-->references-->microsoft scripting runtime.
Saurabh...

Dim objFileSys As New FileSystemObject
Dim objFolder As Folder
Dim objFile As File, found As Boolean
 
Set objFolder = objFileSys.GetFolder("\\xxxxx\Share\Daily")
 
For Each objFile In objSubFolder.Files
 
    If InStr(1, objFile.Name, Range("Q3").Value, vbTextCompare) > 0 Then found = True
Next objFile
 
If found = False Then
 MsgBox ("WARNING!  There are only " & " " & found & " files present.  You need to review prior to the rollup!")
        Call openDIR
    Else
        MsgBox ("There are" & " " & found & " files present.  You can proceed to script rollup!")
    End If

Open in new window

0
 
MrAutomateAuthor Commented:
saurabh726:
Thanks for the fast response! I did enable the scripting runtime....

I am receiving an object required error  msg on line:

For Each objFile In objSubFolder.Files
0
 
MrAutomateAuthor Commented:
Worked like a charm.  Thanks!  This office 2007 transition is becoming intertesting with applciation that are several years in production.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.