May 8, 2022

Search Data in Multiple Workbooks Automatically

How to search for multiple occurrences of data in multiple workbooks with multiple worksheets automatically using a macro. Watch the video below before studying the macro code:

Search Data in Multiple Workbooks Automatically

Option Explicit

Sub SearchAllFolders()
Dim strSearch As String
Dim strPath As String
Dim strFile As String
Dim wksReport As Worksheet
Dim wbk As Workbook
Dim wks As Worksheet
Dim startROW As Long
Dim rngFound As Range
Dim strFirstAddress As String

‘Makes our macro code run faster
Application.ScreenUpdating = False

‘Path to our folder which contains the workbooks
strPath = “C:\MyExcelData”

‘Ask the user for the search term and offer a method also to cancel the search
strSearch = Application.InputBox(“Enter the text to search”, “My choice of text”, “Enter Search Text”, vbOKCancel)
If strSearch = “False” Then Exit Sub

‘Add a new worksheet
Set wksReport = Worksheets.Add
startROW = 1
With wksReport
‘Add the headers for the extracted data
.Cells(startROW, 1) = “Workbook”
.Cells(startROW, 2) = “Worksheet”
.Cells(startROW, 3) = “Cell Address”
.Cells(startROW, 4) = “Text Found”

'Loop through all the files in the folder
strFile = Dir(strPath & "\*.xls*")
Do While strFile <> ""
'Check for each new file
Set wbk = Workbooks.Open(Filename:=strPath & "\" & strFile)
    'Loop through each worksheet in the workbook
    For Each wks In wbk.Worksheets
        Set rngFound = wks.UsedRange.Find(strSearch)
        If Not rngFound Is Nothing Then
            strFirstAddress = rngFound.Address
        End If
        Do
        'Extract multiple occurrences of the same data
            If rngFound Is Nothing Then
                Exit Do
            Else
                startROW = startROW + 1
                .Cells(startROW, 1) = wbk.Name
                .Cells(startROW, 2) = wks.Name
                .Cells(startROW, 3) = rngFound.Address
                .Cells(startROW, 4) = rngFound.Value
            End If

            Set rngFound = wks.Cells.FindNext(after:=rngFound)

        Loop While strFirstAddress <> rngFound.Address
    Next
    'Close the opened workbook without saving it
wbk.Close (False)
strFile = Dir
Loop
'Make all data in the cells readable
.Columns("A:D").EntireColumn.AutoFit

End With
‘Delete the headers and the worksheet if no data found
If wksReport.Cells(2, 1) = “” Then
MsgBox “All Excel files in folder searched!” & vbCrLf & “No data found!”
Cells(startROW, 1) = “”
Cells(startROW, 2) = “”
Cells(startROW, 3) = “”
Cells(startROW, 4) = “”
On Error Resume Next
Application.DisplayAlerts = False
wksReport.Delete
Else
MsgBox “All Excel files in folder searched.” & vbCrLf & “Data extracted.”
End If
‘Clean up the memory
Set wksReport = Nothing
Set wks = Nothing
Set wbk = Nothing

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

Search Data in Multiple Workbooks and Worksheets Automatically

Download a sample file

Search for multiple occurrences of data in multiple worksheets in workbook

Further Reading:

Range.FindNext method (Excel)

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.