Need "grep" like function to search multiple workbooks

[John Keith]

I have a directory with a large number of excel workbooks. I'd like to
be able to search each workbook and extract a complete row if that row
contains the search term I'm looking for. Ideally the row extracted
would be placed into a new worksheet along with the name of the file
that row came from. The unix grep command is the fucntionality I'd
like to emulate. Is there a way to do that with a macro or are there
other tools I could use on a PC to accomplish my goal?


John Keith
(e-mail address removed)

 

[Joel]

You need to run a macro from excel because the files are not text like files
that can be run from grep. You need to open each file using filesearch and
then search each file using find. I listed below the VBA help examples for
each of these instructions

Set fs = Application.FileSearch
With fs
.LookIn = "C:\My Documents"
.FileName = "*.xls"
If .Execute > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If
End With

-----------------------------------------------------

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With