Vba - Check String/Text From Multiple Workbook Without Opening All Files

[DocAElstein]

Its never late here, Pascal, - furthering the subject at any time is what we’re about,
I actually wanted to try at a solution like yours, but I am not so well clued up generally on formulas, and I am not so sure which ones work on closed workbooks. Some don’t for some reason I don’t know yet.
But I expected a solution like you did might be a bit more efficient as you just use a temporary cell rather than a temporary range as mine did.
(You got a small typo I think, .Formula2R1C1 should be .FormulaR1C1)

( I always use .Value when I put a formula into a cell with coding. Its just a personal preference based on a (very likely dodgy) “Alan theory” that .Value applied to a cell in coding is seen as if you type stuff in manually in the spreadsheet, and the thing telling Excel it's a formula is the _"="_, just as it would be if you typed it in manually in the spreadsheet
.FormulaR1C1 is probably the more better and correct way, but I like to indulge in my fantasy theories, )


Alan

'  Pascal    https://excelfox.com/forum/showthread.php/2780-Vba-Check-String-Text-From-Multiple-Workbook-Without-Opening-All-Files?p=16356&viewfull=1#post16356
Sub blah()
Dim Fs As Object, Pth As String, F As Variant, Wb As Object, Msg As String
Set Fs = CreateObject("Scripting.FileSystemObject")
 Let Pth = ThisWorkbook.Path '"D:\Target"
Set F = Fs.GetFolder(Pth)
    For Each Wb In F.Files
      If InStr(1, Wb.Type, "Excel", vbTextCompare) > 0 Then
        With Range("C4") 'use a cell where it doesn't matter
         '.FormulaR1C1 = "=SUMPRODUCT(--ISTEXT('" & Pth & "\[" & Wb.Name & "]Sheet1'!R6C1:R11C15))"
        .Value = "=SUMPRODUCT(--ISTEXT('" & Pth & "\[" & Wb.Name & "]Sheet1'!R6C1:R11C15))"
            If .Value > 0 Then If Len(Msg) = 0 Then Msg = Wb.Name Else Msg = Msg & vbLf & Wb.Name
         .Clear
        End With
      End If
    Next Wb
    If Len(Msg) > 0 Then
      MsgBox "Workbooks containing any string in range B6:O11 are:" & vbLf & Msg
    Else
      MsgBox "None found"
    End If
End Sub