Search a text in excel workbooks and if found return the full path of workbooks

Macro to search a text in all excel workbooks saved in a folder and if text is found return the full path of workbooks

Sub search_text_in_excel_files()
    'search a text in excel workbooks saved in a folder and return the full file path of all workbooks which contains the text
    Dim filenm As String, folderpath As String
    Dim wordtocheck As String
    folderpath = "C:\Users\ADMIN\Desktop\sample files\" ' change folder here
    wordtocheck = "ashish" ' change text to found here
    filenm = Dir(folderpath)
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
        While (filenm <> "")
            If InStr(filenm, ".xls") > 0 Then ' open only excel workbooks
                ' if found display full path in message box
                If check_in_file(folderpath & filenm, wordtocheck) = True Then MsgBox folderpath & filenm
            End If
            filenm = Dir
        Wend
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

Function check_in_file(filname As String, word_to_check As String) As Boolean
    Dim wkb As Workbook
    Dim foundcell As Range
    Dim wks As Worksheet
    Set wkb = Workbooks.Open(filname)

    For Each wks In wkb.Worksheets
        Set foundcell = wks.Cells.Find(What:="*" & word_to_check & "*", After:=wks.Cells(1, 1), _
                LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, MatchCase:=False)
        If Not foundcell Is Nothing Then
            check_in_file = True
            wkb.Close , False
            Exit Function
        End If
    Next
    check_in_file = False
    wkb.Close , False

End Function

Download File