Use wildcards in word VBA

Jessica0732 · #1 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

Guessed · #2 05-24-2023, 08:28 PM

Looks like a couple of problems there.

Firstly, if you want the Excel list to include more than one column of info then you need to set the named range to include the other columns.

Secondly, your code is not including the wildcard search parameter. You can choose to turn it on for all searches or get the script to include it only if you have a T in the third column. This code change includes it as a variable. Note that I've added a MatchCase = False so a regular search won't be case sensitive and you won't need wildcards turned on.

Code:

Sub Highlight_Words_From_Excel_NamedRange()
'Graham Mayor - https://www.gmayor.com - Last updated - 20 Mar 2020
Const strWorkbook As String = "C:\Users\locktonai\Documents\aaa\WordList.xlsx" 'The workbook path
Const strRange As String = "WordList" 'The named Excel range
Dim arr() As Variant, lngRows As Long, oRng As Range, strFind As String, bWC As Boolean
  arr = xlFillArray(strWorkbook, strRange)
  For lngRows = 0 To UBound(arr, 2)
    strFind = arr(0, lngRows)
    Set oRng = ActiveDocument.Range
    With oRng.Find
      .MatchCase = False
      bWC = arr(2, lngRows) = "T"   'assumes named range includes at least 3 columns with no empty cells
      .MatchWildcards = bWC
      Do While .Execute(findText:=strFind)
        oRng.HighlightColorIndex = wdYellow
        oRng.Collapse 0
      Loop
    End With
  Next lngRows
lbl_Exit:
  Exit Sub
End Sub

Jessica0732 · #3 05-26-2023, 11:51 PM

Quote:

Originally Posted by Guessed

Looks like a couple of problems there.

Firstly, if you want the Excel list to include more than one column of info then you need to set the named range to include the other columns.

Secondly, your code is not including the wildcard search parameter. You can choose to turn it on for all searches or get the script to include it only if you have a T in the third column. This code change includes it as a variable. Note that I've added a MatchCase = False so a regular search won't be case sensitive and you won't need wildcards turned on.

Code:

Sub Highlight_Words_From_Excel_NamedRange()
'Graham Mayor - https://www.gmayor.com - Last updated - 20 Mar 2020
Const strWorkbook As String = "C:\Users\locktonai\Documents\aaa\WordList.xlsx" 'The workbook path
Const strRange As String = "WordList" 'The named Excel range
Dim arr() As Variant, lngRows As Long, oRng As Range, strFind As String, bWC As Boolean
  arr = xlFillArray(strWorkbook, strRange)
  For lngRows = 0 To UBound(arr, 2)
    strFind = arr(0, lngRows)
    Set oRng = ActiveDocument.Range
    With oRng.Find
      .MatchCase = False
      bWC = arr(2, lngRows) = "T"   'assumes named range includes at least 3 columns with no empty cells
      .MatchWildcards = bWC
      Do While .Execute(findText:=strFind)
        oRng.HighlightColorIndex = wdYellow
        oRng.Collapse 0
      Loop
    End With
  Next lngRows
lbl_Exit:
  Exit Sub
End Sub

Thanks for your help! It works well!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Wildcards in Word	dcmontva	Word	3	06-20-2021 02:08 AM
How to return the full string (entire word) when searching with wildcards in word vba	scienceguy	Word VBA	13	03-23-2019 03:27 AM
How to insert Wildcards into Word 2011 Mac such as: \$Filename\	peterspiano	Word	4	06-29-2015 05:13 AM
word wildcards I need to replace actual text that contains [ and ]	larsmula	Word	2	02-20-2014 09:18 AM
Word 2010: Wildcards Replace	tinfanide	Word	2	09-10-2011 10:40 AM