Excel VBA search text in multiple Workbooks in folder

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

loop_through_workbook

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.

 

Leave a Reply

Your email address will not be published.