#1
|
|||
|
|||
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 |
#2
|
||||
|
||||
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 |
#3
|
|||
|
|||
Quote:
|
Thread Tools | |
Display Modes | |
|
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 |