Excel VBA: Search folder and sub-directories for files matching partial string

[terriertrip]

I have a sub that's part of a larger routine to search folders and subfolders. I want to integrate a script that once a subfolder is found, to look within the subfolder and find files that contain the letters *dq* within it. I've attempted to try to use a couple approaches and have come up with this as my best option, although when I run it, no errors are thrown and no output occurs even though there are numerous files within the main directory meeting the criteria. My objective is when a file is found within the subfolder to open and search for a string and if true then list the file name and location in the sheet, then proceed to the next file within the subfolder.

Sub ListFiles(ByRef Folder As Object)
    Dim fName as String
        If Not Folder Like "*dq*" Then Exit Sub

        Set sht = ThisWorkbook.Worksheets("Sheet1")
        r = sht.Cells(sht.Rows.count, "A").End(xlUp).Row + 1
        
        With ActiveSheet
        
            On Error Resume Next
                For Each File In Folder.Files
                    fName = File.Name
                    With File
                         If fName Like "*dq*" Then
                            'open the file and call the string search
                            'if string search then
                                .Cells(r, 1).Value = File.ParentFolder
                                .Cells(r, 2).Value = File.ShortName
                                .Cells(r, 3).Value = File.ShortPath
                                .Cells(r, 4).Value = File.Type
                                
                                r = r + 1
                        End If
                    End With
                Next File
        
        End With

End Sub

EDIT: after looking at the code, I realized I had a redundant search. I revised to look at each File within the loop, however it still does not find any existing files within the directory

[Norie]

Application.FileSearch isn't available any more, I think it was stopped in Excel 2000.

Can you post the code you are calling this code from?

[terriertrip]

Thanks, Norie. I just read about that and I also revised the code. Here is the initial sub:

Sub ListFilesInFolders()

    Dim strPath As String
    Dim sht As Worksheet
    Dim LastRow As Long
    
    
        Range("A:C").ClearContents
        Range("A1").Value = "Folder Name"
        Range("B1").Value = "File Name"
        Range("C1").Value = "File Short Path"
        Range("D1").Value = "File Type"
        Range("A1").Select
        
        strPath = GetFolder
        
        Dim OBJ As Object, Folder As Object, File As Object
        
        Set OBJ = CreateObject("Scripting.FileSystemObject")
        Set Folder = OBJ.GetFolder(strPath)
        
        Call ListFiles(Folder)
        
        Dim SubFolder As Object
        
        For Each SubFolder In Folder.SubFolders
            Call ListFiles(SubFolder)
            Call GetSubFolders(SubFolder)
        Next SubFolder

End Sub

The relative functions:

Sub GetSubFolders(ByRef SubFolder As Object)

Dim FolderItem As Object
On Error Resume Next
For Each FolderItem In SubFolder.SubFolders
    Call ListFiles(FolderItem)
    Call GetSubFolders(FolderItem)
Next FolderItem

End Sub

Function GetFolder() As String
    Dim fldr As FileDialog
    Dim sItem As String
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .Title = "Select a Folder"
        .AllowMultiSelect = False
        .InitialFileName = Application.DefaultFilePath
        If .Show <> -1 Then GoTo NextCode
        sItem = .SelectedItems(1)
    End With
NextCode:
    GetFolder = sItem
    Set fldr = Nothing
End Function

[Norie]

Two things.

1 You need to look at the Name property of File.

2 To search using wildcards you need to use Like.

[terriertrip]

I've revised the OP code and removed the unnecessary inner loop. When it finds the file, it starts the procedure to carry the value to the worksheet, but then exits to the GetSubFolders func and resumes the next search. Even if I change it to Folder.Path it still jumps back into the GetSubFolders func.

[terriertrip]

I've attached the workbook with code.

[Norie]

How many levels of subfolders are there?

[terriertrip]

In this example, there are 3-4. For the final project, there could be several deep.

[terriertrip]

I think I'm going to use the Recursive Folder Search from Ammara Digital Image Solutions. That seems to be producing the output I need. I think I can retrofit it to do what I need it to do.

[hrlngrv]

Tangent: this is something the OS generally does better than VBA even using Windows Script Host objects.

For what it's worth, the following batch file produces CSV output with the desired information.

@echo off
@setlocal enableextensions enabledelayedexpansion

if "%~1" == "" (echo %0: missing starting directory & exit /B 1)
if "%~2" == "" (echo %0: missing search pattern & exit /B 2)

for /F "usebackq delims=" %%a in (`findstr /S /M /R "%~2" "%~1"\*`) do call :PROC "%%~a"

exit /B

:PROC
if not "%~x1" == "" (
  for /F "usebackq tokens=2 delims==" %%b in (`assoc %~x1`) do (
    for /F "usebackq skip=2 tokens=1,2,*" %%c in (`reg query HKCR\%%b /ve`) do set t=%%e
  )
) else (
  set t=no file association
)
  
echo "%~dp1","%~fs1","%~dps1","%t%"

My point in mentioning this is that you could keep batch files in ranges in very hidden worksheets, write them to disk and run them as needed, redirecting output to files when running them, opening or importing the files created, and finally deleting those ad hoc files.

Going on a different tangent, you could include a VBA project reference to the Windows Script Host (WSH), namely, scrrun.dll, to make its classes' properties and methods available in the VBA Editor as if they were built-in classes. Then you could use

Dim FSO As New FileSystemObject

as well as using the actual object types for all the WSH classes.

[terriertrip]

Fascinating. I have a very rudimentary understanding of batch files. I only use them strictly for backing up my directories. haha. I've seen some examples on SO and I have much to learn.