Chapter 37: Searching Multiple Sheets and Workbooks


Overview

Excel already has a built-in search facility that you access by selecting Edit Find from the spreadsheet menu, but this method will only search the current worksheet. You can select a group of worksheets and search across those, but you do have to make the selection first, and you cannot search across all workbooks. Using the standard search facility can be cumbersome, but you can write code to produce your own search facility that will work across several spreadsheets or workbooks without having to first select them.

Because of the flexibility of the Excel object model, all the search features are already there. All you need to do is to make it work across worksheets and workbooks.

The first thing that you need is a UserForm for the user interface. Because you want to build in the same functionality as the existing search facility, the UserForm should be fairly close to the existing search form, as shown in Figure 37-1.


Figure 37-1: Designing the UserForm for the Search facility

There are four command buttons , one each for Find Next , Close, Replace, and Replace All. There is a text box for both the find text and the replacement text along with labels to describe what they each mean.

There are two combo boxes for selection of the type of search and two labels to give the description. Finally, there are two check boxes for matching the case and finding entire cells .

The controls are dragged from the toolbox onto the form. If you are uncertain about which control is which in the toolbox, use the tooltip text on each control in the toolbox to tell you.

The form caption can be changed by clicking the caption bar to highlight the form itself and then editing the caption property in the properties window.

You need to set up a global variable canc in a module to hold the user's actions based on which command button they click. This is placed in the declarations section of a module.

 Global canc As Integer 

Because there are four command buttons, canc will now have to hold a greater range of values than you have seen in previous examples.

You now need to add the following code to the UserForm module for it to operate . Double-click any control on the form to be taken into the code window for the form. The code is as follows :

 Private Sub CommandButton1_Click() 
FindDialog.Hide
canc = 0

End Sub

Private Sub CommandButton2_Click()
FindDialog.Hide
canc = 1
End Sub

Private Sub CommandButton3_Click()
FindDialog.Hide
canc = 2
End Sub

Private Sub CommandButton3_MouseMove(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
ComboBox2.Visible = False
Label3.Visible = False
End Sub

Private Sub CommandButton4_Click()
FindDialog.Hide
canc = 4
End Sub

Private Sub CommandButton4_MouseMove(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
ComboBox2.Visible = False
Label3.Visible = False
End Sub


Private Sub TextBox2_MouseMove(ByVal Button As Integer, ByVal Shift _
As Integer, ByVal X As Single, ByVal Y As Single)
ComboBox2.Visible = False
Label3.Visible = False
End Sub


Private Sub UserForm_Activate()

ComboBox1.Clear

ComboBox1.AddItem "ByRows"
ComboBox1.AddItem "ByColumns"

ComboBox2.Clear

ComboBox2.AddItem "Formulas"
ComboBox2.AddItem "Values"
ComboBox2.AddItem "Comments"
End Sub


Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As _
Integer, ByVal X As Single, ByVal Y As Single)
ComboBox2.Visible = True
Label3.Visible = True
End Sub

Private Sub UserForm_Terminate()
canc = 1
End Sub

This is a more complicated UserForm than previous examples in this book because there are a lot of events being used. Each procedure will be dealt with in turn .

Command Button1 represents the Find Next button. When this is clicked, the UserForm (FindDialog) is hidden and execution is transferred back to the procedure that displayed the form. The global variable canc is set to 0.

Command Button2 represents the Close button. When this is clicked, the UserForm (FindDialog) is hidden and execution is transferred back to the procedure that displayed the form. The global variable canc is set to 1.

Command Button3 represents the Replace button. When this is clicked, the UserForm (FindDialog) is hidden and execution is transferred back to the procedure that displayed the form. The global variable canc is set to 2.

Command Button4 represents the Replace All button. When this is clicked, the UserForm (FindDialog) is hidden and execution is transferred back to the procedure that displayed the form. The global variable canc is set to 3.

For Command Button3 , the Mouse Move event on that button is also utilized because when the Replace option is used, the LookIn parameters (shown on Figure 37-1) are not required, as defined by ComboBox2 and Label3 . This code sets the visible properties of ComboBox2 and Label3 to False so that they cannot be seen on the form at that point. The same thing happens for the Mouse Move event on Command Button4 , which represents Replace All.

Also, if the user enters text in TextBox2, the Mouse Move event makes ComboBox2 and Label3 disappear from the form. This is the ComboBox and Label that define the LookIn parameters and they are not required for a Replace.

When the UserForm is activated, the combo boxes need to have values in their lists for the user to select. This is done in the UserForm_Activate event, and the AddItem method adds the items into the combo box lists. The boxes are cleared first using the Clear method.

Where the mouse Move events were used on command buttons and text boxes to make certain controls vanish , the mouse Move event on the form makes them visible again by setting the Visible property to True.

Finally, if the form is closed, the UserForm_Terminate event is called; this sets canc to 1, which means the Close button was clicked and no further action is to be taken.

You next need to insert the following code into the same module where you inserted the other global variables :

 Sub findsheet() 
flag = 0
sflag = 1
temp = ""
Dim a As Range, s As Worksheet, w As Workbook

FindDialog.TextBox1.Text = ActiveCell.Value
Set a = ActiveCell

For Each w In Workbooks
Set a = w.Sheets(1).Range("iv65535")

For n = 1 To w.Worksheets.Count

loopa:

If sflag = 1 Then FindDialog.Show

sflag = 0

If canc = 1 Then FindDialog.Hide: canc = 0: Exit Sub

On Error Resume Next

sstr = FindDialog.TextBox1.Text

rep = FindDialog.TextBox2.Text
If canc = 2 Then
a.Replace sstr, rep
sflag = 1
GoTo loopa
End If

On Error Resume Next

If FindDialog.ComboBox2.Text = "Formulas" Then li = -4123

If FindDialog.ComboBox2.Text = "Values" Then li = -4163

If FindDialog.ComboBox2.Text = "Coments" Then li = -4144

If FindDialog.ComboBox1.Text = "ByRows" Then so = 1

If FindDialog.ComboBox1.Text = "ByColumns" Then so = 2

mc = FindDialog.CheckBox1.Value

la = FindDialog.CheckBox2.Value

If la = True Then lat = 1 Else lat = 2
If canc = 4 Then


p = w.Sheets(n).Range("a1", "iv65535"). _
Replace(sstr rep, lat, so, mc)

End If
If a.Address = "" Then Set a = Sheets(n).Range("iv65535")

Set a = w.Sheets(n).Range("a1", "iv65535"). _
Find(sstr, a, li, lat, so, ,mc)
If a.Address = "" Then sflag = 0 Else sflag = 1

If InStr(temp, w.Name & Sheets(n).Name & a.Address) Or _
a.Address = "" Then sflag = 0: GoTo bypass

w.Activate

Sheets(n).Activate

a.Activate

temp = temp & " " & w.Name & Sheets(n).Name & a.Address

GoTo loopa
bypass:

temp = ""

Next n
Next w
MsgBox "No further occurrences of " & sstr, vbInformation
Exit Sub

End Sub

The first thing this code does is set up variables for flags, temporary strings, and range, workbook, and worksheet.

The text box on the UserForm for ‚“Find what ‚½ is set to the active cell value. This is the cell that currently has the cursor on it so when the UserForm is displayed, it will have the value of that cell in the ‚“Find what ‚½ text box by default. The variable a , which was dimensioned as a range, is set to the active cell.

The code cycles through all the workbooks loaded using a For Each..Next loop. The variable a is set to the very last cell in the first indexed worksheet within the workbook.

The code then uses a For..Next loop to cycle through the worksheets within the workbook. A label is included at this point called loopa for reference purposes so that your code can loop back to it under certain circumstances.

If the variable sflag is 1, the UserForm for searching is displayed. The user can then enter their selection of what they wish to find and whether they want to replace. Depending on which action they take, the global variable canc will be set to one of four values.

The variable sflag is set to zero. This variable determines whether the UserForm is shown. This depends on certain conditions, and initially, you do not want it shown again until the search text is found in the first instance, so sflag is set to 0.

If canc is set to 1, it means the user clicked the Close button, so the UserForm is then hidden, canc is set to 0, and the subroutine is exited with no further action.

Next, two variables are set up to hold the values of ‚“Find what ‚½ and ‚“Replace with ‚½ on the UserForm. The variable sstr holds the value of ‚“Find what, ‚½ and the variable rep holds the value of ‚“Replace with. ‚½

If the user clicked the Replace button on the form, canc will be set to 2. This means that the user has already found an instance of the search string and wishes to replace it with the replacement string. This is done using the Replace method based on the range held in variable a . The variable sflag is set to 1, which means display the UserForm again, and the code loops to the label loopa , which shows the UserForm again.

If the user has not clicked the Replace or the Close button on the form, then other options are likely to involve searching the worksheet. At this point, the other search options from the UserForm are brought into play, using the values of combo boxes and check boxes:

  • Based on the value of ComboBox2 text, which holds the choice of what to look in, the value of a variable li is set. This is set to values used within the Find and Replace methods .

  • Based on the value of ComboBox1 text, which holds the choice of whether the search is made by rows or columns , a variable called so is set.

  • The value of CheckBox1, which dictates whether case should be matched in the search, is placed in a variable called mc .

  • The value of CheckBox2, which dictates whether whole cells only should be found, is placed in a variable called la . If the value of la is true, then a variable called lat is set to 1; otherwise , it is set to 2.

  • If the user has clicked the Replace All button, canc will have the value of 4 and everything within the worksheet will be replaced using the variables holding information taken from the UserForm.

  • If the Range object held in variable a has no address, it is set to the very last cell on the spreadsheet.

  • The Range object a is set to the next available find of the search string on the worksheet using the variables holding information taken from the UserForm. The Find method works by moving on from the last find on that worksheet, so there is no need to update the actual search range.

Eventually, no further occurrences of the search string will be found, and the range object address will be set to Null. When this happens, sflag is set to 0, which means no display of the UserForm; otherwise, it is set to 1 to display the UserForm.

You now use a variable called temp to keep track of all finds of the search string. The problem with the standard Find method is that it goes through one worksheet, finally produces a null address (meaning no further instances can be found), and then starts again at cell A1 on the same worksheet. To make it move on to the following worksheet and start searching there, some extra code is required so that the Find method can keep track of what it has found and move it onto a new sheet where required.

If the cell address in the form of workbook, worksheet, and cell is already in the string variable temp , which means that the Find method has gone back to cell A1 and started again, or the address of the Range object A is set to Null, then sflag is set to 0 (meaning do not display UserForm) and the execution moves to a label called bypass .

If a proper find was made, then the workbook, worksheet, and cell are all activated using the Activate method. This means that the cursor is moved to the next instance of the search text.

The name of the workbook, name of the worksheet, and cell address are all added into the variable temp so that checks can be made to see whether a search is complete on a worksheet. Execution then branches back to loopa , where the UserForm is displayed and the user can then search to the next instance.

The bypass label allows the code to clear the variable temp and to move onto the next available worksheet. It then moves through the worksheet until it comes to an instance of the search string, at which point the UserForm is displayed again to see whether the user wants to replace the instance.

Once all the worksheets and workbooks have been cycled through, a message box is displayed saying ‚“No further instances of the search string. ‚½

Try running the code. This is the subroutine findsheet . Your screen should look like Figure 37-2.


Figure 37-2: Example of the Search All facility

The operation should work exactly the same as the normal Find method from the Excel spreadsheet menu does but with the additional feature of searching through all spreadsheets and workbooks.




Excel VBA Macro Programming
Excel VBA Macro Programming
ISBN: 0072231440
EAN: 2147483647
Year: 2004
Pages: 141

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net