Search for a text string across multiple workbooks

[JJ]

Is there a way to search across multiple spreadsheets in multiple
workbooks? I need to search for a name across many workbooks.

 

[Frank Kabel]

Hi
and what is your expected result after you found one or many occurences
of this string?

 

[Dave Peterson]

In xl2k, you can select the worksheets to seach first (click on the first, and
ctrl click on subsequent).

In xl2002+, you can specify in the Edit|Find dialog that you want to search the
workbook.

But in xl97, these won't work.

But in all versions, you can use Jan Karel Pieterse's FlexFind:
http://www.bmsltd.ie/MVP/


It has some neat options that might make it especially appealing no matter what
version you're running.

 

[Dave Peterson]

I didn't notice multiple workbooks....

How about a macro:

Option Explicit
Sub testme01()

Dim myFiles() As String
Dim fCtr As Long
Dim myFile As String
Dim myPath As String
Dim tempWkbk As Workbook
Dim logWks As Worksheet
Dim wks As Worksheet
Dim oRow As Long
Dim LookFor As Variant
Dim FoundCell As Range
Dim FirstAddress As String

'change to point at the folder to check
myPath = "c:\my documents\excel\test"
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If

myFile = Dir(myPath & "*.xls")
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

Application.ScreenUpdating = False

Set logWks = Workbooks.Add(1).Worksheets(1)
logWks.Range("a1").Resize(1, 4).Value _
= Array("WkbkName", "WkSheetName", "Address", "Text")

'get the list of files
fCtr = 0
Do While myFile <> ""
fCtr = fCtr + 1
ReDim Preserve myFiles(1 To fCtr)
myFiles(fCtr) = myFile
myFile = Dir()
Loop

'change this
LookFor = "whateveryouwant!"

If fCtr > 0 Then
oRow = 1
For fCtr = LBound(myFiles) To UBound(myFiles)
Set tempWkbk = Nothing
On Error Resume Next
Set tempWkbk = Workbooks.Open(Filename:=myPath & myFiles(fCtr))
On Error GoTo 0
If tempWkbk Is Nothing Then
oRow = oRow + 1
logWks.Cells(oRow, "A").Value = "Error Opening: " _
& myFiles(fCtr)
Else
For Each wks In tempWkbk.Worksheets
Application.StatusBar _
= "Processing: " & tempWkbk.FullName & "--" & wks.Name
With wks.UsedRange
Set FoundCell = .Find(What:=LookFor, _
MatchCase:=False, after:=.Cells(.Cells.Count), _
LookIn:=xlValues, LookAt:=xlPart, _
searchorder:=xlByRows, searchdirection:=xlNext)

If FoundCell Is Nothing Then
'do nothing
Else
FirstAddress = FoundCell.Address
Do
oRow = oRow + 1
With logWks.Cells(oRow, "A")
.Value = "'" & tempWkbk.FullName
.Offset(0, 1).Value = "'" & .Parent.Name
.Offset(0, 2).Value = FoundCell.Address
.Offset(0, 3).Value = "'" & FoundCell.Text
End With
Set FoundCell = .FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address <> FirstAddress
End If
End With
Next wks
tempWkbk.Close savechanges:=False
End If
Next fCtr
End If

With logWks.UsedRange
.AutoFilter
.Columns.AutoFit
End With

With Application
.ScreenUpdating = True
.StatusBar = False
End With

End Sub


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm