Open particular file , search fo text & retrive results

If you want to search particular text in any wrokbook & want to retrive all the results

on  active sheet then use below code

change file name & path as per your requirement

Sub Mtest()
Dim found As Range
Dim wbk1 As Workbook, wbk2 As Workbook
Dim output As Range
Dim sht As Worksheet
Dim broker As String
Dim start As String
Set wbk1 = ThisWorkbook
broker = InputBox("Enter the string to search for")
Set output = wbk1.Worksheets("Sheet1").Range("A1")
'change your file name and path here
Set wbk2 = Application.Workbooks.Open("C:\Users\MAHESH\Desktop\searchfile.xls")
With wbk2
For Each sht In wbk2.Worksheets
Set found = sht.Cells.Find(what:=broker, after:=ActiveCell, LookIn:=xlFormulas, Lookat:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not found Is Nothing Then
start = found.Address
Do
Set found = sht.Cells.FindNext(found)
output.Value = found.Value
Set output = output.Offset(1, 0)
Loop While Not found Is Nothing And found.Address <> start
End If
Next sht
End With