Dim FSO As Object, iFolder As Object, iFile As Object, FD As FileDialog, ExtArray() As Variant
Dim iPath As String, firstAddress As String, iPathName As String, Recursion As Boolean
Dim iSht As Worksheet, ws As Worksheet, iTempWB As Workbook, ExcelVersion As Byte
Dim TextToFind, iFoundRng As Range, iLastRow As Long, FoundAny As Boolean, iTotalFiles As Long
Sub SearchInAllFilesAndFolders()
Set ws = ThisWorkbook.ActiveSheet
Recursion = False: iPathName = "": FoundAny = False
TextToFind = Application.InputBox("Enter the text for search:", "Search")
If TextToFind = "" Or TextToFind = False Then Exit Sub
TextToFind = Trim(TextToFind)
Set FD = Application.FileDialog(msoFileDialogFolderPicker)
With FD
.AllowMultiSelect = False
.Title = "Specify the necessary directory"
.ButtonName = "OK"
If .Show = False Then Exit Sub Else iPath = .SelectedItems(1) & Application.PathSeparator
End With
Set FD = Nothing
If MsgBox("To look through the enclosed folders?", vbQuestion + vbYesNo, "Recursion") = vbYes Then Recursion = True
ExtArray = Array("xls", "xlsx", "xlsm", "xlsb") 'Here you can specify which extension will process
Set FSO = CreateObject("Scripting.FileSystemObject")
ChooseFoldersSubfoldersFSO (iPath)
Set iFolder = Nothing
Set FSO = Nothing
ws.Cells(2, 1).Select
If FoundAny = False Then
MsgBox "Text none of the files in the folder:" & Chr(10) & iPath & Chr(10) & " was not found!", 48, "Report"
Exit Sub
End If
MsgBox "Search completed!" & Chr(10) & "Total processed: " & iTotalFiles & " files", 64, "Search"
End Sub
Function ChooseFoldersSubfoldersFSO(ByVal sPath As String)
Set iFolder = FSO.GetFolder(sPath)
For Each iFile In iFolder.Files
If Not IsError(Application.Match(FSO.GetExtensionName(iFile), ExtArray(), 0)) Then
If iFile.Name <> ThisWorkbook.Name Then
Set iTempWB = Workbooks.Open(Filename:=sPath & iFile.Name, UpdateLinks:=False, ReadOnly:=True)
iTotalFiles = iTotalFiles + 1
For Each iSht In iTempWB.Worksheets
If iSht.FilterMode = True Then iSht.ShowAllData
Set iFoundRng = iSht.Cells.Find(What:=TextToFind, LookIn:=xlFormulas, LookAt:=xlWhole)
If Not iFoundRng Is Nothing Then
FoundAny = True
firstAddress = iFoundRng.Address
With ws
Do
iLastRow = .UsedRange.Rows.Count + .UsedRange.Row
If iPathName <> sPath Then 'If a new file
iPathName = sPath
With .Cells(iLastRow + 2, 1)
.Value = "Directory: " & sPath
.Font.Bold = True
'.Font.ColorIndex = 5
End With
.Hyperlinks.Add Anchor:=.Cells(iLastRow + 3, 1), Address:=sPath & iTempWB.Name, ScreenTip:="Book: " & iTempWB.Name & ", Sheet: " & iSht.Name, TextToDisplay:="Book: " & iTempWB.Name & ", Sheet: " & iSht.Name
Else
.Hyperlinks.Add Anchor:=.Cells(iLastRow + 1, 1), Address:=sPath & iTempWB.Name, ScreenTip:="Book: " & iTempWB.Name & ", Sheet: " & iSht.Name, TextToDisplay:="Book: " & iTempWB.Name & ", Sheet: " & iSht.Name
End If
iFoundRng.EntireRow.Copy 'copy the entire row
.Cells(.UsedRange.Rows.Count + .UsedRange.Row, "A").PasteSpecial xlPasteValues 'paste values ??only
Set iFoundRng = iSht.Cells.FindNext(iFoundRng)
Loop While iFoundRng.Address <> firstAddress
End With
End If
Next
Application.CutCopyMode = False
iTempWB.Close SaveChanges:=False
End If
End If
Next
If Recursion Then 'recursion
For Each iFolder In iFolder.SubFolders
ChooseFoldersSubfoldersFSO iFolder.Path & Application.PathSeparator
Next
End If
End Function
Bookmarks