Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-15-2023, 07:49 AM
Jessica0732 Jessica0732 is offline Use wildcards in word VBA Windows 10 Use wildcards in word VBA Office 2019
Novice
Use wildcards in word VBA
 
Join Date: May 2023
Posts: 2
Jessica0732 is on a distinguished road
Default Use wildcards in word VBA

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
Attached Files
File Type: xlsx WordList.xlsx (9.2 KB, 6 views)
Reply With Quote
  #2  
Old 05-24-2023, 08:28 PM
Guessed's Avatar
Guessed Guessed is offline Use wildcards in word VBA Windows 10 Use wildcards in word VBA Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

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
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #3  
Old 05-26-2023, 11:51 PM
Jessica0732 Jessica0732 is offline Use wildcards in word VBA Windows 10 Use wildcards in word VBA Office 2019
Novice
Use wildcards in word VBA
 
Join Date: May 2023
Posts: 2
Jessica0732 is on a distinguished road
Default

Quote:
Originally Posted by Guessed View Post
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!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Use wildcards in word VBA Wildcards in Word dcmontva Word 3 06-20-2021 02:08 AM
Use wildcards in word VBA 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
Use wildcards in word VBA How to insert Wildcards into Word 2011 Mac such as: \$Filename\ peterspiano Word 4 06-29-2015 05:13 AM
Use wildcards in word VBA word wildcards I need to replace actual text that contains [ and ] larsmula Word 2 02-20-2014 09:18 AM
Use wildcards in word VBA Word 2010: Wildcards Replace tinfanide Word 2 09-10-2011 10:40 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:00 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft