This Excel VBA tutorial explains how to search text in multiple Workbooks in a folder and subfolders, and display the result in a summary page, including which workbook, which worksheet, and which Cell contains the text.
You may also want to read:
Excel VBA INSTR Function
Excel loop workbooks in folders and subfolders with FSO
Excel VBA search text in multiple Workbooks in folder
Suppose you have multiple workbooks in a folder, and you want to know if any workbook contains a text you want to look for. The easiest way to do this is to press Ctrl + F in the folder you want to search for the text, then the search result will display.
However this method does not always work for different reasons. If it doesn’t work, then you need to look for an alternate approach. In this post, I will demonstrate how to use Excel VBA to search text in multiple workbooks in a folder and subfolders, and display the result in a summary page, including which workbook, which worksheet, and which Cell contains the text.
VBA Code – search text in multiple Workbooks in folder
Create a new workbook, press ALT+F11 and insert the below code in a Module. Do not save this workbook in the folder which you want to search the text.
Public Sub searchText()
Dim FSO As Object
Dim folder As Object, subfolder As Object
Dim wb As Object
Dim ws As Worksheet
searchList = Array("orange", "apple", "pear") 'define the list of text you want to search, case insensitive
Set FSO = CreateObject("Scripting.FileSystemObject")
folderPath = "C:\test" 'define the path of the folder that contains the workbooks
Set folder = FSO.GetFolder(folderPath)
Dim thisWbWs, newWS As Worksheet
'Create summary worksheet if not exist
For Each thisWbWs In ActiveWorkbook.Worksheets
If wsExists("summary") Then
counter = 1
End If
Next thisWbWs
If counter = 0 Then
Set newWS = ThisWorkbook.Worksheets.Add(After:=Worksheets(Worksheets.Count))
With newWS
.Name = "summary"
.Range("A1").Value = "Target Keyword"
.Range("B1").Value = "Workbook"
.Range("C1").Value = "Worksheet"
.Range("D1").Value = "Address"
.Range("E1").Value = "Cell Value"
End With
End If
With Application
.DisplayAlerts = False
.ScreenUpdating = False
.EnableEvents = False
.AskToUpdateLinks = False
End With
'Check each workbook in main folder
For Each wb In folder.Files
If Right(wb.Name, 3) = "xls" Or Right(wb.Name, 4) = "xlsx" Or Right(wb.Name, 4) = "xlsm" Then
Set masterWB = Workbooks.Open(wb)
For Each ws In masterWB.Worksheets
For Each Rng In ws.UsedRange
For Each i In searchList
If InStr(1, Rng.Value, i, vbTextCompare) > 0 Then 'vbTextCompare means case insensitive.
nextRow = ThisWorkbook.Sheets("summary").Range("A" & Rows.Count).End(xlUp).Row + 1
With ThisWorkbook.Sheets("summary")
.Range("A" & nextRow).Value = i
.Range("B" & nextRow).Value = Application.ActiveWorkbook.FullName
.Range("C" & nextRow).Value = ws.Name
.Range("D" & nextRow).Value = Rng.Address
.Range("E" & nextRow).Value = Rng.Value
End With
End If
Next i
Next Rng
Next ws
ActiveWorkbook.Close True
End If
Next
'Check each workbook in sub folders
For Each subfolder In folder.SubFolders
For Each wb In subfolder.Files
If Right(wb.Name, 3) = "xls" Or Right(wb.Name, 4) = "xlsx" Or Right(wb.Name, 4) = "xlsm" Then
Set masterWB = Workbooks.Open(wb)
For Each ws In masterWB.Worksheets
For Each Rng In ws.UsedRange
For Each i In searchList
If InStr(1, Rng.Value, i, vbTextCompare) > 0 Then
nextRow = ThisWorkbook.Sheets("summary").Range("A" & Rows.Count).End(xlUp).Row + 1
With ThisWorkbook.Sheets("summary")
.Range("A" & nextRow).Value = i
.Range("B" & nextRow).Value = Application.ActiveWorkbook.FullName
.Range("C" & nextRow).Value = ws.Name
.Range("D" & nextRow).Value = Rng.Address
.Range("E" & nextRow).Value = Rng.Value
End With
End If
Next i
Next Rng
Next ws
ActiveWorkbook.Close True
End If
Next
Next
With Application
.DisplayAlerts = True
.ScreenUpdating = True
.EnableEvents = True
.AskToUpdateLinks = True
End With
ThisWorkbook.Sheets("summary").Cells.Select
ThisWorkbook.Sheets("summary").Cells.EntireColumn.AutoFit
ThisWorkbook.Sheets("summary").Range("A1").Select
End Sub
Function wsExists(wksName As String) As Boolean
On Error Resume Next
wsExists = CBool(Len(Worksheets(wksName).Name) > 0)
On Error GoTo 0
End Function
Enable Microsoft Scripting Runtime
FileSystemObject (FSO) provides an API to access the Windows filesystem such as accessing Drive, TextStram, Folder, File.
You should be able to run FSO in Excel 2013. If you fail to run FSO Object, open VBE (ALT+F11) > Tools > References > Check the box Microsoft Scripting Runtine
Example – search text in multiple Workbooks in folder
Suppose I want to search text “orange”, “apple”, “pear” in all workbooks under c:\test\, plus workbooks in one level down subfolders under c:\test\
I want to return search result even though the search text is a partial text in a Cell.
For example, if Cell A1 value of a workbook is “Orange Juice”, I still want to return the result because it contains “orange”.
Then create a new workbook that contains the VBA code, change the parameters that highlighted in red. Run the Procedure.
A summary worksheet is created, all the workbooks that contains “apple”, “orange” and “pear” will appear in the summary.
