Hello, i would need some help to make that code search specific text in excel files(not in the files name but in the workbook) in folders and sub-folders. I finded that code on a website. Everything work exept it dont search excel files in sub folders. Only search excel file in direct folder even if it had sub-folders with other excel files in it. I tried a lot of code to put in and try to search in sub-folders but nothing work. Can someone help me on that?
Its not to search specific text in excel files name but in the files itself.
Thx in advance.
Here the original code without modification:
Dim fso As Object
Dim fld As Object
Dim strSearch As String
Dim strPath As String
Dim strFile As String
Dim wOut As Worksheet
Dim wbk As Workbook
Dim wks As Worksheet
Dim lRow As Long
Dim rFound As Range
Dim strFirstAddress As String
On Error GoTo ErrHandler
Application.ScreenUpdating = False
‘Change as desired
strPath = “c:MyFolder”
strSearch = “Specific text”
Set wOut = Worksheets.Add
lRow = 1
.Cells(lRow, 1) = “Workbook”
.Cells(lRow, 2) = “Worksheet”
.Cells(lRow, 3) = “Cell”
.Cells(lRow, 4) = “Text in Cell”
Set fso = CreateObject(“Scripting.FileSystemObject”)
Set fld = fso.GetFolder(strPath)
strFile = Dir(strPath & “*.xls*”)
Do While strFile <> “”
Set wbk = Workbooks.Open _
(Filename:=strPath & “” & strFile, _
For Each wks In wbk.Worksheets
Set rFound = wks.UsedRange.Find(strSearch)
If Not rFound Is Nothing Then
strFirstAddress = rFound.Address
If rFound Is Nothing Then
lRow = lRow + 1
.Cells(lRow, 1) = wbk.Name
.Cells(lRow, 2) = wks.Name
.Cells(lRow, 3) = rFound.Address
.Cells(lRow, 4) = rFound.Value
Set rFound = wks.Cells.FindNext(After:=rFound)
Loop While strFirstAddress <> rFound.Address
strFile = Dir
Set wOut = Nothing
Set wks = Nothing
Set wbk = Nothing
Set fld = Nothing
Set fso = Nothing
Application.ScreenUpdating = True
MsgBox Err.Description, vbExclamation
I searched throught the web and i find nothing to help me, everything i tried dont work and im pretty new to excel.
Its to search in hundred of sub folder, i cant take all sub-folder and make one folder with all excel files because we are a lot of people using these sub-folders.
I need to search specific Persone name in all workbook, not in the files name but in all sheet that the files contain. The code work well but dont search through sub-folders.
thx for your time and help.