VBA to check the value in all the files in directory

[vinod9111]

Hi All,

Looking for VBA code which helps in finding a value in list of files in the directory. It should fetch the filename, sheet name and address of the cell where the value holds.

For instance if I want to search "December 2019" in directory which has files name say file1, file2, file3, upto file10.

IF the value December 2019 is present in file4 and file7, the output in macro file should give me

Filename sheetname address of the cell
file4 sheet1 c4
File7 sheet3 d5

Any help will be appreciated.

regards,

Vinod

 

[JLGWhiz]

This assumes the directory of interest is the same as for the workbook running the code. If it is not, then change the fPath = line to use correct path.

Dim fPath As String, fName As String, txt As String, fn As Range, wb As Workbook, sh As Worksheet, adr As String
fPath = ThisWorkbook.Path & "\"
fName = Dir(fPath & "*.xl*")
txt = InputBox("Enter search string.", "STRING TO SEARCH")
    Do While fName <> ""
        If fName <> ThisWorkbook.Name Then
            Set wb = Workbooks.Open(fPath & fName)
                For Each sh In wb.Sheets
                    Set fn = sh.Cells.Find(txt, , xlValues, xlPart)
                        If Not fn Is Nothing Then
                            adr = fn.Address
                            Do
                                ThisWorkbook.Sheets(1).Cells(Rows.Count, 1).End(xlUp)(2) = wb.Name _
                                & " " & sh.Name & " " & fn.Address
                                Set fn = sh.Cells.FindNext(fn)
                            Loop While fn.Address <> adr
                        End If
                Next
                wb.Close False
        End If
        fName = Dir
    Loop
End Sub