Microsoft Office Forums - View Single Post

Jessica0732 · 05-15-2023, 07:49 AM

Hello all, I have found the following macro (wrote by Gmayor) for highlightling specific words according to an Excel List in a Microsoft Word Document. It is very useful.

May I ask if it can also highlight words like "[1-9]xxx"? For example, the column A cell A2 typed "[2-199] years and cell C2 put "T" which means using wildcards. then the macro can highlight "2 years", "198 years" etc but not typing the years from 2 years to 199 years one by one.

Another example is "advisor" and "Advisor". If using wildcards ([Aa])(dvisors), it can highlight both "advisor" and "Advisor" but not typing "advisor" and "Advisor" in two lines and save one line. (see attached excel list)

Is there any ways to do so?

Code:

Sub Highlight_Words_From_Excel_NamedRange()
'Graham Mayor - https://www.gmayor.com - Last updated - 20 Mar 2020
Const strWorkbook As String = "C:\Path\Word List.xlsx" 'The workbook path
Const strRange As String = "WordList" 'The named Excel range
Dim arr() As Variant
Dim lngRows As Long
Dim oRng As Range
Dim strFind As String
    arr = xlFillArray(strWorkbook, strRange)
    For lngRows = 0 To UBound(arr, 2)
        strFind = arr(0, lngRows)
        Set oRng = ActiveDocument.Range
        With oRng.Find
            Do While .Execute(findText:=strFind)
                oRng.HighlightColorIndex = wdYellow
                oRng.Collapse 0
            Loop
        End With
    Next lngRows
lbl_Exit:
    Exit Sub
End Sub

Private Function xlFillArray(strWorkbook As String, _
                             strRange As String) As Variant
'Graham Mayor - http://www.gmayor.com - 24/09/2016
Dim RS As Object
Dim CN As Object
Dim iRows As Long

    strRange = strRange & "]"
    Set CN = CreateObject("ADODB.Connection")

    CN.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
                              "Data Source=" & strWorkbook & ";" & _
                              "Extended Properties=""Excel 12.0 Xml;HDR=NO;IMEX=1"""

    Set RS = CreateObject("ADODB.Recordset")
    RS.Open "SELECT * FROM [" & strRange, CN, 2, 1

    With RS
        .MoveLast
        iRows = .RecordCount
        .MoveFirst
    End With
    xlFillArray = RS.GetRows(iRows)
    If RS.State = 1 Then RS.Close
    Set RS = Nothing
    If CN.State = 1 Then CN.Close
    Set CN = Nothing
lbl_Exit:
    Exit Function
End Function