This requires Microsoft Scripting Runtime to be set. In the VBA Editor, click Tools...References....Microsoft Scripting Runtime. Run the ListFileNames macro. The result will be returned to "Sheet1" of the workbook with the macro.
Sub ListFileNames()
Const sRoot As String = "C:\Test\"
Dim oFSO As Scripting.FileSystemObject
Application.ScreenUpdating = False
Set oFSO = New Scripting.FileSystemObject
RecurseFolder oFSO, sRoot, True
End Sub
Sub RecurseFolder(oFSO As FileSystemObject, sDir As String, IncludeSubFolders As Boolean)
Dim oFil As File
Dim oFld As Folder
Dim oSub As Folder
Dim Val As Range, desSH As Worksheet
Set desSH = ThisWorkbook.Sheets("Sheet1")
desSH.Range("A1:B1") = Array("Path", "Sheet Name")
Set oFld = oFSO.GetFolder(sDir)
For Each oFil In oFld.Files
If oFil.Name Like "*.xls" Or oFil.Name Like "*.xlsm" Then
Set wb = Workbooks.Open(Filename:=oFil)
For Each ws In Sheets
Set Val = ws.UsedRange.Find("ACDC", LookIn:=xlValues, lookat:=xlWhole)
If Not Val Is Nothing Then
With desSH
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = wb.Path
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0) = wb.Name
End With
Exit For
End If
Next ws
wb.Close False
End If
Next oFil
If IncludeSubFolders Then
For Each oSub In oFld.SubFolders
RecurseFolder oFSO, oSub.Path, True
Next oSub
End If
End Sub
Bookmarks