• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 447
  • Last Modified:

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

0
MrAutomate
Asked:
MrAutomate
  • 2
  • 2
1 Solution
 
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
 
Saurabh Singh TeotiaCommented:
Ahh change this line...
For Each objFile In objSubFolder.Files

to this..
 For Each objFile In objFolder.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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now