Excel "search engine"

[Josh Craig]

Hi,

I was just wondering if anyone was aware of any existing code I could use to
make an excel search engine. My plan is to have a list in one sheet and then
in another sheet a cell to enter a search into. I want to use just excel and
not access or anything else.

I want people to be able to type in a name/word and have all the results
from the list which match the search appear below. In other words, I want it
to work just like a google-style search page.

Any ideas? Anyone seen it done before?

Any help greatly appreciated!

Thanks,
Josh

 

[Joel]

I wrote the code below for somebody a few months ago. I opened an internet
explorer application from excel to get the results. I made the internet
explorer application visible but I could make it invisible. This code only
gets the results from the 1st page.




Public Sub GoogleSearch1()
'Use and input box for typing in the search words
Dim szSearchWords As String
Dim szResults As String
With Sheets("Sheet1")
szSearchWords = .Range("B2").Value
End With
If Not Len(szSearchWords) > 0 Then Exit Sub

'Get keywords and validate by adding + for spaces between
szSearchWords = Replace$(szSearchWords, " ", "+")

Dim ie As Object 'InternetExplorer
Set ie = CreateObject("InternetExplorer.Application")
ie.Navigate "http://www.google.com/search?hl=en&q=" & _
szSearchWords & "&meta="

'Loop until the page is fully loaded
Const READYSTATE_COMPLETE = 4
Do Until ie.ReadyState = READYSTATE_COMPLETE
With ie
.Visible = True
End With
Loop

Set Results = ie.document.getelementsbytagname("P")
For Each itm In Results
If InStr(UCase(itm.innertext), "RESULTS") Then
MsgBox (itm.innertext)
Exit For
End If
Next itm

With Sheets("Sheet2")
RowCount = 1
For Each itm In ie.document.all
.Range("A" & RowCount) = itm.tagname
.Range("B" & RowCount) = itm.classname
.Range("C" & RowCount) = Left(itm.innertext, 1024)

RowCount = RowCount + 1
Next itm
.Cells.VerticalAlignment = xlTop
End With

Set Results = ie.document.getelementsbytagname("LI")
With Sheets("Sheet3")
RowCount = 1
For Each itm In Results
.Range("A" & RowCount) = itm.innertext
RowCount = RowCount + 1
Next itm
.Cells.VerticalAlignment = xlTop
End With
'Explicitly clear memory
Set ie = Nothing
End Sub

 

[L. Howard Kittle]

Hi Josh

Check out this Add In by Ron De Druin. Pretty darn niffty!

http://www.rondebruin.nl/Google.htm


HTH
Regards,
Howard

 

[XR8 Sprintless]

There's an addin called Flexfind that was mentioned on Ron de Bruin's
site that searches across all sheets in a workbook for text.

http://www.jkp-ads.com/OfficeMarketPlaceFF-EN.asp