Searching a Word document with multiple words from a list (over 1,000 terms) and BOLD all occurrence

Nicknamednick · #1 09-27-2022, 12:19 PM

Thank you, Macropod for the tips. I just want to make sure my instincts are correct:

Quote:

Originally Posted by macropod

For example:
...
StrWkBkNm = "C:\Users" & Environ("Username") & "\Documents\WordList.xlsx"
StrWkSht = "Sheet1"

[I assume, enter my own local path to the .xlsx between the " ". And also, if the workbook I'm using has a sheet called "Table1 (2)" with the word list, I should swap out for "Sheet1"]]

...

Quote:

Originally Posted by macropod

' Find the last-used row in column A.

iDataRow = .Cells(.Rows.Count, 1).End(-4162).Row ' -4162 = xlUp

[My column is entitled "Column1" so I assume I should change out "column A".]

I'll try this code next. Thank you again!
--David

macropod · #2 09-27-2022, 04:32 PM

Quote:

Originally Posted by Nicknamednick

I assume, enter my own local path to the .xlsx between the " ".

As coded, the macro looks for a workbook named 'WordList.xlsx' in your Documents folder. You could have it look in whatever folder your document is by changing:
StrWkBkNm = "C:\Users" & Environ("Username") & "\Documents\WordList.xlsx"
to:
StrWkBkNm = ActiveDocument.Path & "\WordList.xlsx"
Otherwise, you could supply the fill path & filename.

Quote:

Originally Posted by Nicknamednick

if the workbook I'm using has a sheet called "Table1 (2)" with the word list, I should swap out for "Sheet1"

Yes.

Quote:

Originally Posted by Nicknamednick

My column is entitled "Column1" so I assume I should change out "column A".

No, that's just a text comment. Regardless of what you change that to, the code will still look in column A. If you want to search a different column, change the column reference in the .Range("A" & i) references.

macropod · #3 09-27-2022, 04:38 PM

If you're confident it will always be there, you can delete/comment-out the 'If SheetExists(xlWkBk, StrWkSht) = True Then' test:

Code:

    'Ensure the worksheet exists
'    If SheetExists(xlWkBk, StrWkSht) = True Then
      With .Worksheets(StrWkSht)
        ' Find the last-used row in column A.
        iDataRow = .Cells(.Rows.Count, 1).End(-4162).Row ' -4162 = xlUp
        ' Capture the F/R data.
        For i = 1 To iDataRow
          ' Skip over empty fields to preserve the underlying cell contents.
          If Trim(.Range("A" & i)) <> vbNullString Then
            xlFList = xlFList & "|" & Trim(.Range("A" & i))
          End If
        Next
      End With
'    Else
'      MsgBox "Cannot find the designated worksheet: " & StrWkSht, vbExclamation
'    End If

Nicknamednick · #4 09-28-2022, 02:33 PM

@Macropod, your revised code worked! I tried it on a list I moved over into another word docx. It made every phrase bold, but they were also in the exact order as the excel list being used. I had also gone back and taken out most of the terms with commas.

However, when I ran the macro on a regular document, it had less than desirable effects. Many of the words were bolded, but some were only partially bolded. It also made many words bold that I would not have intended.

I think this is pretty good, though, and I will just have to play around with changing the list to remove words and phrases that are causing trouble, repetitive words in phrases and such.

Thank you both so much for your help. I've learned a great deal and I think I'm close to having a tool that will help me with productivity in the future.

With gratitude,
--David

macropod · #5 09-28-2022, 05:41 PM

Quote:

Originally Posted by Nicknamednick

However, when I ran the macro on a regular document, it had less than desirable effects. Many of the words were bolded, but some were only partially bolded.

Words that are partially bolded indicate that your Find terms didn't include the whole of those words. This is a problem that arises from having both single words and phrases in the same list, as one can't use:
.MatchWholeWord = True
when phrases are present. One way around that would be to test each term for the presence of one or more spaces. For example:

Code:

  'Process each string from the List
  For i = 1 To UBound(Split(xlFList, "|"))
    If UBound(Split(Split(xlFList, "|")(i), " ")) = 0 Then
      .MatchWholeWord = True
    Else
      .MatchWholeWord = False
    End If
    .Text = Split(xlFList, "|")(i)
    .Replacement.Text = "^&"
    .Execute Replace:=wdReplaceAll
  Next

Quote:

Originally Posted by Nicknamednick

It also made many words bold that I would not have intended.

I can't see how the macro code could cause that. Perhaps your list is more expansive than you thought.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Wildcards: searching for multiple words / expressions close to each other	ballpoint	Word VBA	7	11-09-2017 03:30 PM
Best Practice for Indexing Multiple Word Terms and Sub-Terms	jhy001	Word	4	11-06-2017 02:08 PM
All words in document appear bold but are not!	Ezra	Word	4	07-31-2017 06:53 AM
Making Multiple Words Bold	mtk989	Word	2	06-25-2011 11:27 AM
How to make a list of all the bold words in a document?	galaxy_110	Word	1	12-31-2010 08:23 AM