Use excel as a searcher

[ims]

Dear all,

I have a database, I want to use excel as a searcher.
By inputting a key word excel will search the whole database and display all
the records containing this key word.
What's the best and simplest way to do this?
Is there any sample program on the net for my easy reference.
Thanks.

ims

 

[Tom Ogilvy]

Database isn't definitive. but look at Mr. Erlandsen's site on using ADO

http://www.erlandsendata.no/english/vba/adodao/index.php

 

[ims]

Dear Tom,

What's the meaning of "Database isn't definitive"?
I find the path you given me can't help me a lot.

regards,
ims

 

[gocush]

You could try the following code in a vba module:

First name 2 ranges
"Database" as the cells in you DB
"KeyWord" as a single cell ABOVE your db or on another sheet.


Option Explicit

Sub SearchDB()
Dim oCell As Range
Dim r As Range
Dim Found As Boolean
Dim KWord As String
Dim firstAddress As String

KWord = Range("KeyWord")

For Each r In Range("Database").Rows
Found = False
With r
Set oCell = .Find(KWord, LookIn:=xlValues)
If Not oCell Is Nothing Then
firstAddress = oCell.Address
Do
Found = True
Set oCell = .FindNext(oCell)
Loop While Not oCell Is Nothing And oCell.Address <
firstAddress
End If
If Not Found Then r.EntireRow.Hidden = True

End With
Next


End Sub


TO SHOW YOUR DATABASE again, RightClick all rows in the DB>>Unhide

 

[Tom Ogilvy]

Saying I have a database really says nothing very useful.

Where is the database. Gocush has assumed it is in an excel spreadsheet. I
have assumed it is external to excel - but even assuming that, where is it -
access, sql server, oracle, something else?

Maybe I should have said the term database is not self defining.

 

[ims]

dear gocush,

it all the time hides the whole database, don't why....

ims