Excel Search in subfolders

Question

Using the following code that I pulled from the web, I'm able to do a search in a single directory for excel files containing a string in a certain row. How would I allow this to be recursive in all the subfolders as well? I've found a few answers but I just don't understand how I would implement them in my code. I only started messing with VBScript yesterday and I'm pretty confused about how to make this work.

strComputer = "CAA-W74109188"

Set objExcel = CreateObject("Excel.Application", strComputer)

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")

Set FileList = objWMIService.ExecQuery _
("ASSOCIATORS OF {Win32_Directory.Name='c:\TDRS'} Where " _
    & "ResultClass = CIM_DataFile")


 For Each objFile In FileList
  If (objFile.Extension = "xlsm" or  objFile.Extension = "xls") Then
    Set objWorkbook = objExcel.Workbooks.Open(objFile.Name)
    Set objWorksheet = objWorkbook.Worksheets(1)
    If objExcel.Cells(3,10) = "Complete" or objExcel.Cells(3,9) = "Released" Then
        Wscript.Echo objFile.FileName
    End If

objExcel.DisplayAlerts = False
objworkbook.Saved = False
    objWorkbook.Close False
End If
Next

objExcel.Quit

possible duplicate of [How to recursively access subfolder files inside a folder in vbscript?](http://stackoverflow.com/questions/14950475/how-to-recursively-access-subfolder-files-inside-a-folder-in-vbscript) — Ansgar Wiechers, Mar 19 '14 at 18:30

score 0 · Accepted Answer · answered Mar 19 '14 at 14:53

Here is an script that I used to delete files with, which I have modified for your needs. A recursive function is what you need to get the job done and I have always found them to be interesting and kind of hard to wrap my head around.

Dim Shell : Set Shell = WScript.CreateObject( "WScript.Shell" )
Dim oFSO : Set oFSO = CreateObject("Scripting.FileSystemObject")
Dim objExcel : Set objExcel = CreateObject("Excel.Application")

Dim Paths(0)
Paths(0) = "c:\temp"

For Each Path in Paths
   FolderScan(Path)
Next

Sub FolderScan(Folder) 
 Set base = oFSO.GetFolder(Folder) 
    If base.SubFolders.Count Then
       For Each folder in Base.SubFolders
         FolderScan(folder.Path)
       Next
    End If
 Set files = base.Files
    If files.Count Then
       For Each File in files
          If LCase(oFSO.GetExtensionName(File.Path) = "xlsm") or _
            LCase(oFSO.GetExtensionName(File.Path) = "xls") Then

              Dim objWorkbook : Set objWorkbook = objExcel.Workbooks.Open(File.Path)
              Dim objWorkSheet : Set objWorkSheet = objWorkbook.Worksheets(1)

                If (objExcel.Cells(3,10) = "Complete" or _
                  objExcel.Cells(3,9) = "Released") Then
                    Wscript.echo File.Path
                End if
                objExcel.DisplayAlerts = False
                objExcel.Quit
          End If
       Next
    End If
 End Sub

Hey Dan, do you happen to know of a way that I can supress macros from running when I the code opens and closes the excel sheet? There's a "Do you want to Save" macro that runs on close and it breaks the code. — mc91, Mar 19 '14 at 18:47
Try adding this line right after the `Dim objExcel` line `objExcel.EnableEvents = False` — Dan K, Mar 19 '14 at 18:59

score 0 · Answer 2 · answered Mar 19 '14 at 15:29

Here's a generic, recursive function that iterates all files and subfolders of a given folder object.

Dim FileSystem
Set FileSystem = CreateObject("Scripting.FileSystemObject")

DoFolder FileSystem.GetFolder("c:\somefolder")

Sub DoFolder(Folder)

    Dim SubFolder
    For Each SubFolder In Folder.SubFolders
        DoFolder SubFolder
    Next

    Dim File
    For Each File In Folder.Files
        ' Operate on each file
    Next

End Sub

Excel Search in subfolders

2 Answers2