Solved: Search engine

[mdmackillop]

Hi Airborne,
I've been playing around with an Index solution and come up with the following
This code has to be inserted in each of your workbooks
[VBA] Option Explicit
Option Base 1

Sub GetBoxText()
Dim MyWS As Worksheet

Dim BoxText()
Dim i!, j!

'If no Data sheet then exit sub
Sheets("Data").Select
If Err <> 0 Then Exit Sub

'Create FindWord sheet if it does not exist
On Error Resume Next
Sheets("FindWord").Select
If Err <> 0 Then
'error occured so clear it
Err.Clear
Sheets.Add.Name = "MyIndex"
Sheets("MyIndex").Move After:=Sheets(Sheets.Count)
End If

Set MyWS = ActiveWorkbook.Sheets("Data")

ReDim BoxText(MyWS.Shapes.Count)
For i = 1 To MyWS.Shapes.Count
If Left(MyWS.Shapes(i).Name, 5) = "Text " Then
j = j + 1
BoxText(j) = MyWS.Shapes(i).TextFrame.Characters.Text
End If
Next i
ReDim Preserve BoxText(j)
Sheets("MyIndex").UsedRange.ClearContents
For i = 1 To j
Sheets("MyIndex").Cells(i, 1) = BoxText(i)
Next
End Sub

[/VBA]
and also the following which calls the code, to keep things up to date
[VBA]
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
GetBoxText
End Sub

[/VBA]

You can then run a slightly modified version of DRJs FinalAll programme as attached.

I'm sure with your experience now, you can iron out any version bugs!

MD

[Airborne]

Thanks Ken, have a good weekend.

[Airborne]

Thanks MD, I'll try out your suggestion.


Regards.

[Airborne]

Hi Ken and MD, I've been playing around a bit with the index code. But if I understand correctly, should I put it in my workbooks from now on? We have about 5000 files, containing two sheets, stored. I can't search in them. Maybe I'm wrong.

I'd rather use the following idea.....
Windows search doesn't open the files but just shows the files in the search window containing the hits. You can open them yourself. Is it not possible that Ken's routine not opens the files but just shows them in an extra page of my Data.xlt workbook. As you know it contains the "data" and "stock" sheet and I can also add a "search" sheet. The workbook contains Ken's routine and other routines (one to copy/paste the two sheets in a new workbook giving it the name Datadd-mm-yyyy.xls (containing no macro's) storing it in year\etc. We then close the Data workbook again.

I realy want to stick to Ken's setup because apart from the IT pc problem it works fine.


Thanks and regards.

[mdmackillop]

I would not advocate putting my code into 5,000 sheets unless it was going to rain very heavily all day for a week. That said, I think that having created an index sheet containing the data from all files, having code in each new workbook to keep the index updated is still a possibility. Another consideration is whether you ever change the text in the textboxes of existing workbooks.
Unfortunately, Ken's code won't run on my PC, maybe its time to upgrade!
MD

[Airborne]

Hi MD, "Another consideration is whether you ever change the text in the textboxes of existing workbooks." Yes we sometimes change text in the last current workbooks. I have a routine for that. I got it a while ago from DRJ. It opens the workbook of a selected date from the workbook with all the routines.

I hope you don't blame me for needing a upgrade .

Thanks and regards.

[Airborne]

Hi, talked to the IT guys. Excel was damaged on three IT pc's. After a repair.....everything worked. A steady screen and a working bar . As soon as I have a reply from Ken I will call this thread solved.

Thanks all for the help.

Regards.

[Ken Puls]

Hi Airborne,

Hi, talked to the IT guys. Excel was damaged on three IT pc's. After a repair.....everything worked. A steady screen and a working bar .

Excellent! One issue down, at least!

Windows search doesn't open the files but just shows the files in the search window containing the hits. You can open them yourself. Is it not possible that Ken's routine not opens the files but just shows them in an extra page of my Data.xlt workbook. As you know it contains the "data" and "stock" sheet and I can also add a "search" sheet. The workbook contains Ken's routine and other routines (one to copy/paste the two sheets in a new workbook giving it the name Datadd-mm-yyyy.xls (containing no macro's) storing it in year\etc. We then close the Data workbook again.

Hmmm... The problem that I see here is that with the data stored in textboxes, I can't find a way to access the various files without opening them. If you were to throw a Workbook_BeforeSave event in the template (that creates all of your data files) to copy all the textbox info to cells on a separate sheet ("search" as referred to above) I think that we could adapt Joseph Rubin's idea here. Even that would be some work, though, as the methodology for this would be to create a link to a sheet in your main workbook from each cell in the search sheet of every file in the directory. You're still talking about setting up a loop to talk to each file in the directory though. Having never used Mr. Rubin's method before, I can't guarantee that it would even be any faster...

It may be possible to just launch windows search utility right from Excel (I'm not sure what to call to do this though), but does it search textboxes within the file? I'm not sure...

PS: MD, I think that the only lines of code that aren't 2000 compliant are the lines calling .ShowWindowsInTaskbar (That one was definately added in 2003). I think that the rest of the code should be backwards compatible.

[Airborne]

Hi Ken, I hope you don't think by adding this in your reply

Originally Posted by Airborne
Windows search doesn't open the files but just shows the files in the search window containing the hits. You can open them yourself. Is it not possible that Ken's routine not opens the files but just shows them in an extra page of my Data.xlt workbook. As you know it contains the "data" and "stock" sheet and I can also add a "search" sheet. The workbook contains Ken's routine and other routines (one to copy/paste the two sheets in a new workbook giving it the name Datadd-mm-yyyy.xls (containing no macro's) storing it in year\etc. We then close the Data workbook again.

That your module doesn't work because it does work and it works great! I have a search engine that works and a status bar that looks cooler than the one WindowsXP shows. I meant that the search engine in WindowsXP doesn't show opening files when you look at the screen. Your module doesn't show opening files either. The only thing is that after te search the sheets with the hits are all showing in the task bar and after WindowsXP search the files with the hits are shown as hyperlinks in the search window. With a lot of files open the last case seems slightly better.

It's not really a problem though. It's more nice to have . I have heaps of other questions about VBA that I can't solve at the moment so how about calling this thread solved so you can help me with my other questions

Regards.

[Ken Puls]

Hi Airborne,

I have a search engine that works and a status bar that looks cooler than the one WindowsXP shows.

That's some pretty high praise!

Sorry, I didn't follow you earlier. So basically it's just a matter of cleaning up the taskbar at the bottom of the screen then? We can certainly close the workbook when a "hit" is found, and add a hyperlink to it in a sheet if that's what you're after...

Does that sound right?

You'd just need to capture the file name/directory when you add the hyperlink objects. Look up Hyperlinks in the KB to get yourself started with a few different ways of using them.

HTH,

[Airborne]

Ken. After the search is finished and I have, for instance, found 5 files with hits, they are all crowded together in the taskbar. You can click on them and see the sheet with the searched word(s). Now 5 is a crowd but if you find 10 sheets. So the idea was not showing the open files in the taskbar but as hyperlinks on a extra sheet in the workbook with the search engine. I can add a sheet called Found (it will be hidden and only open when the sheet has hyperlinks on it after a search).

As I told before, it's nice to have(but again cool:cool ).

You'd just need to capture the file name/directory when you add the hyperlink objects

It's not JUST for me ...... .

Regards.

[Ken Puls]

Hi Airborne,

You're actually closer than you know... In the findall routine, the following line inside the file loop gives you the full path right to the workbook:

[vba]Set WB = Workbooks.Open(FileName:=SearchPath & "\" & FileName, ReadOnly:=True) _
', Password:="Uncomment and put password here if required"[/vba]
The WB variable can be used to set the target address of the hyperlink as it then holds the full path including file name. What we'll need to do is change the loop to close the workbook regardless of match or not, but add the hyperlink if it matches. It should be a pretty minor change overall. I'll take a look at it later tonight, if you don't solve it first.

Cheers,

[Ken Puls]

Ooops! To clarify the above, SearchPath & "\" & FileName gives you the full path right to the workbook.

At any rate... Name a sheet "Found" in the workbook that runs the code, and replace the FindAll routine with this:

[vba]Option Compare Text
Option Explicit
Sub FindAll(SearchPath As String, SearchText As String, ToProcess As Integer)
'Macro purpose: Loop through all Excel Workbooks and search each of them for the
'specified criteria
Dim WB As Workbook
Dim WS As Worksheet
Dim FileName As String
Dim TermFound As Boolean
Dim i As Integer
Dim Processed As Integer
Dim BoxText As String
Dim Problem As Boolean

'Turn off screen flashing & taskbar updates
With Application
.ScreenUpdating = False
.ShowWindowsInTaskbar = False
End With

'Clear the list of hyperlinks from the "Found" sheet
With ThisWorkbook.Worksheets("Found")
.Range("A2:A" & .Range("A65536").End(xlUp).Row).ClearContents
.Range("A1").Value = "Location of workbooks holding the term: " & SearchText
End With

'Assign first filename in directory to FileName variable
FileName = Dir(SearchPath & "\*.xls", vbNormal)

'Expand ufSearch to show progress bar
With ufSearch
.Height = 174
.Repaint
End With

Do
'Set problem to false then attempt to open workbook and set WB & WS variables
'(If an error results, set the Problem variable to true)
Problem = False
On Error Resume Next
Set WB = Workbooks.Open(FileName:=SearchPath & "\" & FileName, ReadOnly:=True) _
', Password:="Uncomment and put password here if required"
If Err.Number <> 0 Then Problem = True
On Error Resume Next
If Problem = False Then
TermFound = False
For Each WS In WB.Worksheets
'If no error, check all textboxes in the file for search term
For i = 1 To WS.TextBoxes.Count
BoxText = WS.TextBoxes(i).Text
If InStr(1, BoxText, SearchText) > 0 Then
'If the search term is found, set TermFound to true,
'and stop checking further (exit the loop)
TermFound = True
Exit For
End If
Next i
If TermFound = True Then
'If the search term was found add it to the list of hyperlinks
With ThisWorkbook.Worksheets("Found")
.Hyperlinks.Add _
Anchor:=.Range("A65536").End(xlUp).Offset(1, 0), _
Address:=SearchPath & "\" & FileName, _
TextToDisplay:=SearchPath & "\" & FileName
End With
Exit For
End If
Next WS
End If

'Close the workbook
'(On error resume next in case WB never opened at all)
On Error Resume Next
WB.Close False
On Error GoTo 0

'Inform the user how many files have been processed and update the progress bar
Processed = Processed + 1
Call UpdateProgress(Processed / ToProcess)

'Release WB & WS variables and set FileName to next file
Set WB = Nothing
Set WS = Nothing
FileName = Dir() 'sets FileName to next file in directory
Loop Until FileName = ""

'Restore screen updating, show open files on taskbar and clear statusbar
With Application
.ScreenUpdating = True
.ShowWindowsInTaskbar = True
End With

'Hide the progress indicator
With ufSearch
.Hide
End With

'Inform the user how many files were opened (number of opened files)
MsgBox WorksheetFunction.CountA(ThisWorkbook.Worksheets("Found").Range("A2:A" & _
ThisWorkbook.Worksheets("Found").Range("A65536").End(xlUp).Row)) _
& " files were found which matched your search term!", _
vbOKOnly + vbInformation, x & " Files Found!"
ThisWorkbook.Worksheets("Found").Activate
End Sub[/vba]

I think that should do what you're after... (FYI, it will overwrite the list every time you run it as well, so no need to clear it out.)

Let me know!

[Airborne]

Well Ken, that's it! Your project is not only working great but it's :cool cool too. Thanks very much.

Maybe it's a dumb question but....[VBA] .Range("A2:A" & .Range("A65536").End(xlUp).Row).ClearContents
.Range("A1").Value = "Location of workbooks holding the term: " & SearchText [/VBA] .....what is meant by ("A65536")? How can I put the hyperlinks in cells B3, B4, etc?

Thanks again for all your time and trouble .

Regards.

[Ken Puls]

Well Ken, that's it! Your project is not only working great but it's :cool cool too. Thanks very much.

.....what is meant by ("A65536")? How can I put the hyperlinks in cells B3, B4, etc?

Thanks again for all your time and trouble .

Regards.

You're very welcome. I've had some fun with it!

As far as "A65536", it's the last cell in column A (until Microsoft evenutually gives us more rows , 65536 is the last one in any workbook) Tacking the end(xlup) on it sends it up to the last used cell in Column A, be it A65 or A897, etc...

To change the hyperlink list to column B, just change the following lines:

[vba]Anchor:=.Range("A65536").End(xlUp).Offset(1, 0), _[/vba]
To
[vba]Anchor:=.Range("B65536").End(xlUp).Offset(1, 0), _[/vba]

And

[vba].Range("A2:A" & .Range("B65536").End(xlUp).Row).ClearContents [/vba]
To
[vba].Range("B2:B" & .Range("B65536").End(xlUp).Row).ClearContents [/vba]

This leaves the title in column A, so you may want to update that line to column B as well. (This one: .Range("A1").Value = "Location of workbooks holding the term: " & SearchText)

Cheers,

[Airborne]

Thanks Ken for my last lesson in this thread. I will call this thread solved.


Take care and regards.

[XLGibbs]

Just a point of interest on this cool thing...with excel 2007 coming,may be prudent to change the references to be more like:

Anchor:=.cells(.rows.count).End(xlUp).Offset(1, 0), _
to account for the additional rows (1.1 million or so total) that will be available.

[Ken Puls]

Agreed, Pete. Bob converted me to the .rows.count method after I worked on this with Airborne.