File.Search Alternative for Excel 2007

[Anne Troy]

I'm trying to use the code in this KB article:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=305

It searches for a string in a folder/subfolders full of workbooks.

Unfortunately, the File.Search object does not support this action in Excel 2007. I've seen tons of discussions and supposed workarounds on the web, but cannot find anything I can work with since I'm not a coder.

I would be eternally grateful if someone could help me with this issue.

A bonus would be to have it look in S:\Directory\Directory and all its subfolders so I don't have to (get permission to) put the coded workbook on the S drive.

I am unable to access this forum from work, so will only be able to check for resolutions later. I do so appreciate your time because as it stands, I must search for this data through individual files, one by one, and the data could exist in as many as 8 files. I cannot believe it has been done this way for so long at a major company. I just started working there 5 weeks ago. Help!

[Anne Troy]

Here's the code:

'<< We're using "FileSearch" to open all the >>
'<< books in a folder - as an example of what >>
'<< this can be used for, we'll then search thru >>
'<< all the sheets in all the books in the folder >>
Option Explicit

Sub SearchAllBooksInFolder()
Dim Cell As Range, FirstAddress$, i%, N%
Dim LookingFor$, ThisBook As Workbook

Set ThisBook = ThisWorkbook
LookingFor = InputBox("What do you want to find?", "Find What?")
If LookingFor = Empty Then Exit Sub

'clear columns 1 to 4 ready to receive new search results
Sheets(1).Range(Columns(1), Columns(4)).ClearContents
Application.ScreenUpdating = False

'search all the sheets in ThisBook first
For i = 1 To Sheets.Count
With Sheets(i).Range("A1:D500")
Set Cell = .Find(LookingFor, LookIn:=xlValues, searchorder:=xlByRows, _
LookAt:=xlPart, MatchCase:=False)
If Cell Is Nothing Then '<< there's nothing on this sheet
Goto Finish1
Else
FirstAddress = Cell.Address '<< (bookmark)
Do
'add this item to the search results
With ThisBook.Sheets(1)
Range("A65536").End(xlUp).Offset(1, 0) = ActiveWorkbook.Name
Range("B65536").End(xlUp).Offset(1, 0) = Sheets(i).Name
Range("C65536").End(xlUp).Offset(1, 0) = Cell.Address
Range("D65536").End(xlUp).Offset(1, 0) = "(" & Cell.Value & ")"
End With
'look for any others
Set Cell = .FindNext(Cell)
Loop Until Cell Is Nothing Or Cell.Address = FirstAddress
End If
End With
Finish1:
Next i

'now open & search all the other books in the folder
With Application.FileSearch
.LookIn = ActiveWorkbook.path
.Filename = "*.xls" '<< only search workbooks
If .Execute > 0 Then
For N = 1 To .FoundFiles.Count
If .FoundFiles(N) <> ThisWorkbook.FullName Then
Application.Workbooks.Open(.FoundFiles(N)).Activate

'search all the sheets in the current book
For i = 1 To Sheets.Count
With Sheets(i).Range("A1:D500")
Set Cell = .Find(LookingFor, LookIn:=xlValues, searchorder:=xlByRows, _
LookAt:=xlPart, MatchCase:=False)
If Cell Is Nothing Then '<< there's nothing on this sheet
Goto Finish2
Else
FirstAddress = Cell.Address '<< (bookmark)
Do
'add this item to the search results
ThisBook.Sheets(1).Range("A65536").End(xlUp).Offset(1, 0) = ActiveWorkbook.Name
ThisBook.Sheets(1).Range("B65536").End(xlUp).Offset(1, 0) = Sheets(i).Name
ThisBook.Sheets(1).Range("C65536").End(xlUp).Offset(1, 0) = Cell.Address
ThisBook.Sheets(1).Range("D65536").End(xlUp).Offset(1, 0) = "(" & Cell.Value & ")"
'look for any others
Set Cell = .FindNext(Cell)
Loop Until Cell Is Nothing Or Cell.Address = FirstAddress
End If
End With
Finish2:
Next i

ActiveWorkbook.Close savechanges:=False
End If
'search finished in current book, search the next book
Next N
End If
End With

'go back to "ThisBook"
Sheets(1).Activate
Range("B1") = "Search results for " & "''" & LookingFor & "''"
End Sub

[Bob Phillips]

Dim FSO As Object

Set FSO = CreateObject("Scripting.FileSystemobject")
Call ProcessFolder(FSO, "C:\test", LookingFor, ThisBook)

Set FSO = Nothing
End Sub

Private Function ProcessFolder( _
ByRef FSO As Object, _
ByVal Foldername As String, _
ByVal LookingFor As String, _
ByRef ThisBook As Workbook)
Dim fldr As Object
Dim subFldr As Object
Dim file As Object
Dim cell As Range
Dim FirstAddress As String
Dim i As Long

Set fldr = FSO.GetFolder(Foldername)
For Each subFldr In fldr.SubFolders

Call ProcessFolder(FSO, subFldr.Path, LookingFor, ThisBook)
Next subFldr

For Each file In fldr.Files

Application.Workbooks.Open file.Path

'search all the sheets in the current book
For i = 1 To Sheets.Count

With Sheets(i).Range("A1:D500")

Set cell = .Find(LookingFor, LookIn:=xlValues, searchorder:=xlByRows, _
LookAt:=xlPart, MatchCase:=False)
If cell Is Nothing Then '<< there's nothing on this sheet

GoTo Finish2
Else

FirstAddress = cell.Address '<< (bookmark)

Do

'add this item to the search results
ThisBook.Sheets(1).Range("A65536").End(xlUp).Offset(1, 0) = ActiveWorkbook.Name
ThisBook.Sheets(1).Range("B65536").End(xlUp).Offset(1, 0) = Sheets(i).Name
ThisBook.Sheets(1).Range("C65536").End(xlUp).Offset(1, 0) = cell.Address
ThisBook.Sheets(1).Range("D65536").End(xlUp).Offset(1, 0) = "(" & cell.Value & ")"

'look for any others
Set cell = .FindNext(cell)
Loop Until cell Is Nothing Or cell.Address = FirstAddress
End If
End With
Finish2:
Next i

ActiveWorkbook.Close savechanges:=False

'search finished in current book, search the next book
Next file

Set file = Nothing
Set subFldr = Nothing
Set fldr = Nothing
End Function

[Anne Troy]

omfg I love you.
Today should be very slow. Gonna print this out and do it!
THANKS SO MUCH!!! :D

[Anne Troy]

Okay. I've tried this. I get a byref argument type mismatch on the FSO part of this line:
Call ProcessFolder(FSO

I'm also unsure... am I supposed to just give the procedure a name at the top? That's what we did and we're running that.

I no longer see the message box asking us to enter the value to look for, so I'm now suspecting that my assumption to add a proc name is wrong.

I don't mean to be stupid, but I created this site because I don't have the brain to code. Just can't wrap my head around it and never have been able to. I am trying to get help from someone else at work who has the patience and desire to use this macro, but not the knowledge.

[Bob Phillips]

That code was meant to replace your code from the

'now open & search all the other books in the folder

part down. The other part of your is needed as is.

That call looks like it is missing the other parameters.

[Anne Troy]

I don't know what "that call looks like it is missing the other parameters" means, and I get a compile error. :(

[Anne Troy]

I've asked for more assistance over here, and uploaded sample files:
http://www.mrexcel.com/forum/showthread.php?t=511521

[Bob Phillips]

I've asked for more assistance over here, and uploaded sample files:
http://www.mrexcel.com/forum/showthread.php?t=511521

Then I am out of here ...