asked on

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

Saurabh Singh Teotia

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

MrAutomate

ASKER

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

ASKER CERTIFIED SOLUTION

Saurabh Singh Teotia

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MrAutomate

ASKER

Worked like a charm. Thanks! This office 2007 transition is becoming intertesting with applciation that are several years in production.