In this project we will create a macro that will greatly extend the power of the standard 'Search and Replace' menu Item.
- Create a comma delimited list of words and save it as 'words.dat' as a 'Text Only with Line Break' file type, in the 'C:\Program Files\Microsoft Office\Templates\' folder. (This will ensure it will ALWAYS be found)
- Copy the following code into a new module in 'normal.dot'. ( You can delete it later! )
- NOTE:
- Some of the code is not necessary, but demonstrates other capabilities of the objects used.
- This 'chunk' of code would be split up in to smaller procedures in a real project, but is left in one piece so you can follow the flow.
Sub replaceWords()
Dim wordlist() As String
Set fs = Application.FileSearch
With fs
.LookIn = "C:\Program Files\Microsoft Office\Templates"
.FileName = "words.dat"
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) > 0 Then
listno = 0
Open "C:\Program Files\Microsoft Office\Templates\words.dat" For Input As #1
Do While Not EOF(1)
listno = listno + 1
Input #1, dummyvalue
Loop
Close #1
ReDim wordlist(listno)
listno = 0
Open "C:\Program Files\Microsoft Office\Templates\words.dat" For Input As #1
Do While Not EOF(1)
listno = listno + 1
Input #1, dummyvalue
wordlist(listno) = dummyvalue
Loop
Close #1
Application.ScreenUpdating = False
Selection.Find.Replacement.Highlight = True
Count = 0
TempColor = Selection.Find.Replacement.Font.ColorIndex
Do While Count < listno
Count = Count + 1
Application.StatusBar = "Searching for " & wordlist(Count) & "!"
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
Selection.Find.Replacement.Font.ColorIndex = wdRed
With Selection.Find
.Text = wordlist(Count)
.Replacement.Text = wordlist(Count)
.Forward = True
.Wrap = wdFindContinue
.Format = True
.MatchCase = False
.MatchWholeWord = True
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
Loop
Selection.Find.Replacement.Font.ColorIndex = TempColor
With Selection.Find
.Text = ""
.Replacement.Text = ""
.MatchWholeWord = False
End With
Application.ScreenUpdating = True
Application.StatusBar = "Finished search and highlight!"
Else
MsgBox "The file 'words.dat' was not found in the current directory!"
End If
End With
Set fs = Nothing
'release the resources allocated to the fs object
End Sub
EXPLANATION:
- Using the 'Application.FileSearch' object, the procedure first checks to see if the data file exists.
If it is found,
SortOrder:=msoSortOrderAscending) > 0
the procedure continues. If it is not found, the procedure exits with the message ...
MsgBox "The file 'words.dat' was not found in the current directory!"
- The second task is to create a dynamic array to contain the search words. This is achieved by incrementing a counter, 'listno', by reading each element in the external data file, 'words.dat', until the end of the file is reached, 'EOF'. After the last element is read, 'wordlist()' is redimentioned by the 'ReDim' statement:
ReDim wordlist(listno)
- Next, the dynamic array, 'wordlist()', is filled by reading then assigning each element in the 'words.dat' file into it:
Input #1, dummyvalue
wordlist(listno) = dummyvalue
- Before making any changes, the speed of the procedure is increased by setting the value of 'Application.ScreenUpdating' to 'False'. To update the screen at the end of the loop, to show any changes made, 'Application.ScreenUpdating' is set to 'True'.
- The next part of the code is to loop through each word in the 'wordlist()' array using the counter 'Count'. Using the 'Find.Replacement.' object, the selection is searched for each occurrence of the word by setting the value of '.MatchWholeWord' to 'True'. Each occurance of 'wordlist(Count)' in the selection is replaced by 'Selection.Find.Replacement.Font.ColorIndex = wdRed'. That is, each occurance of 'wordlist(Count)' is highlighted by having its font color set to red.
- NOTE:
- Before the code finishes, the values of the 'Find.Replacement.' properties such as '.MatchWholeWord' are returned to their default values. This is a good practice to follow in all coding.
- Feedback to the user about what is happening is provided by setting the value of the 'Application.StatusBar' object.
- FINALLY, the best way to fully understand the code is to alter it. Change the name of some variables. Change some of the 'Find.Replacement.' properties values. Change the location of the 'word.dat' file to see what happens.
- THATS IT!
YOU HAVE NOW LEARNT HOW EASY IT IS TO USE DATA FROM AN EXTERNAL TEXT FILE. ( AND HOPEFULLY HAD SOME FUN AS WELL! )
|