Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-18-2020, 10:59 PM
pushpi004 pushpi004 is offline Find, Replace using Excel List Windows 10 Find, Replace using Excel List Office 2016
Novice
Find, Replace using Excel List
 
Join Date: Feb 2020
Posts: 17
pushpi004 is on a distinguished road
Default Find, Replace using Excel List

The code in https://www.msofficeforums.com/word-...d-replace.html has been really helpful to me. But I am facing difficulty in case of case sensitivity. The word gets replaced only if it matches the case. The lengthier solution to this problem is having the word with all possible cases (e.g. all caps, or all small or capitalising each word) in the worksheet. Can we do any modification to this code, so that the words gets replaced even if their case did not match and the replaced word remains in same case as the word being replace (like when we do it with MS word's replace function)?, if so, please help me with this.
Thanks


Regards
Reply With Quote
  #2  
Old 02-18-2020, 11:02 PM
macropod's Avatar
macropod macropod is offline Find, Replace using Excel List Windows 7 64bit Find, Replace using Excel List Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,433
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

The simple solution is to change:
.MatchCase = True
to:
.MatchCase = False
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #3  
Old 02-19-2020, 12:41 AM
pushpi004 pushpi004 is offline Find, Replace using Excel List Windows 10 Find, Replace using Excel List Office 2016
Novice
Find, Replace using Excel List
 
Join Date: Feb 2020
Posts: 17
pushpi004 is on a distinguished road
Default What about case sensitivity

Thank you Paul. It worked perfectly.
Reply With Quote
  #4  
Old 02-21-2020, 04:57 PM
kingsinger kingsinger is offline Find, Replace using Excel List Windows 10 Find, Replace using Excel List Office 2013
Novice
 
Join Date: Feb 2020
Posts: 6
kingsinger is on a distinguished road
Default

Is there a way to change this macro so that the search and replace terms are in a two column word table instead of a spreadsheet? I ask because I need to do a search and replace involving Word Fields and I'm unsure whether that those can be accurately stored in a spreadsheet. I feel like I found a macro like that a while back and have somehow lost it. But perhaps it was actually this macro.

KS
Reply With Quote
  #5  
Old 02-21-2020, 07:16 PM
macropod's Avatar
macropod macropod is offline Find, Replace using Excel List Windows 7 64bit Find, Replace using Excel List Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,433
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

See, for example: https://www.msofficeforums.com/148925-post2.html
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #6  
Old 02-23-2020, 10:25 PM
pushpi004 pushpi004 is offline Find, Replace using Excel List Windows 10 Find, Replace using Excel List Office 2016
Novice
Find, Replace using Excel List
 
Join Date: Feb 2020
Posts: 17
pushpi004 is on a distinguished road
Default Issue with plurals

I have been using above macro code (post 4) for find and replace related requirements. Thank you for promptly replying to my last query. I have another follow up query. Currently, the code does not find and replace plurals. E.g. it replaces the word 'Syllable' to 'Syl', as defined in database. But if the document has word 'Syllables', it ignores the plural. Ideally, it should replace the word with 'Syls'. The longer solution is to include plural words into the database as well, but that increases the processing time. Is there any way of implementing this feature?

thanks
Reply With Quote
  #7  
Old 02-23-2020, 10:37 PM
macropod's Avatar
macropod macropod is offline Find, Replace using Excel List Windows 7 64bit Find, Replace using Excel List Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,433
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

You could change:
.MatchWholeWord = True
to:
.MatchWholeWord = False
but this might have quite undesirable effects. For example, if the found word also forms part of longer, unrelated, words, those might get updated too. You might get better results inserting:
.MatchAllWordforms = True
after:
.Replacement.ClearFormatting
but that, too can have undesirable results. Test both on a document, with:
.Replacement.Highlight = True
inserted after:
.Replacement.ClearFormatting
and set your preferred highlight colour before running the macro. If you don't get the desired results, you'll need to insert an additional F/R expression for each plural.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #8  
Old 02-24-2020, 09:20 PM
pushpi004 pushpi004 is offline Find, Replace using Excel List Windows 10 Find, Replace using Excel List Office 2016
Novice
Find, Replace using Excel List
 
Join Date: Feb 2020
Posts: 17
pushpi004 is on a distinguished road
Default Issue with plurals

Hi paul,

as suggested by you, I have tried incorporating the new code. Clearly, MatchWholeWord=False doesn't give desired results.

Moving on to .MatchAllWordForms=True, in spite of all types of changes in my test file, database or even some lines of code, it gives a run-time error code 5610, stating that "the find what text for find all word forms can search only alphabetic letters".

My test file has following line, "The school technical library issues Mathematics, Physics and chemistry books to the students and teachers".

The database has following words to be replaced with :-
School to Sch, Library to lib, Technical to Tech, Mathematics to Maths, Physics to Phy, Chemistry to Chem, student to stdnt and teachers to tchr.

The required result must be "The sch tech lib issues Maths, Phy and chem books to the stdnts and tchrs"

I have read online that this function only works with english language and that it wont work along with .MatchWholeWord=True or .MatchCase=True. But even including these changes has not helped.
Reply With Quote
  #9  
Old 02-24-2020, 09:40 PM
macropod's Avatar
macropod macropod is offline Find, Replace using Excel List Windows 7 64bit Find, Replace using Excel List Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,433
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

As previously suggested, you might need to process singular & plural forms alike:
Code:
Sub BulkFindReplace()
Application.ScreenUpdating = False
Dim FList As String, RList As String, j As Long
FList = "School,Library,Technical,Mathematics,Physics,Chemistry,students,student,teachers,teacher"
RList = "Sch,lib,Tech,Maths,Phys,Chem,stdnts,stdnt,tchrs,tchr"
With ActiveDocument.Range.Find
  .ClearFormatting
  .Replacement.ClearFormatting
  .Format = False
  .Forward = True
  .MatchCase = False
  .MatchWholeWord = True
   'Process each word from the Find/Replace Lists
  For j = 0 To UBound(Split(FList, ","))
    .Text = Split(FList, ",")(j)
    .Replacement.Text = Split(RList, ",")(j)
    .Execute Replace:=wdReplaceAll
  Next
End With
Application.ScreenUpdating = True
End Sub
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #10  
Old 02-24-2020, 09:43 PM
pushpi004 pushpi004 is offline Find, Replace using Excel List Windows 10 Find, Replace using Excel List Office 2016
Novice
Find, Replace using Excel List
 
Join Date: Feb 2020
Posts: 17
pushpi004 is on a distinguished road
Default Issue with plurals

Alright Paul,

I guess that is the only solution then.

thanks for your help.

Cheers!
Reply With Quote
  #11  
Old 02-25-2020, 05:37 AM
pushpi004 pushpi004 is offline Find, Replace using Excel List Windows 10 Find, Replace using Excel List Office 2016
Novice
Find, Replace using Excel List
 
Join Date: Feb 2020
Posts: 17
pushpi004 is on a distinguished road
Default Issue with plurals

Hi Paul,

I have found the solution for my problem. The issue with .MatchAllWordForms is that it does not work if the word/string consists of more than a word. So, using instr(.text, " "), i checked if the .text from xlFlist has space or not. If yes, then, I used .MatchWholeWord=True, else, I used MatchAllWordForms=true. The results of this simple if else construct gave the answer. thus, there is no requirement of creating a separate F/R list of plurals.

THE CODE I USED IS COPIED BELOW :-

Code:
With wdDoc
      With .Range.Find
        .ClearFormatting
        .Replacement.ClearFormatting
        .Replacement.Highlight = True      
        .MatchCase = False
        .Wrap = wdFindContinue
        For i = 1 To UBound(Split(xlFList, "|"))
          .Text = Split(xlFList, "|")(i)           
          'checking if checked word has spaces i.e. it comprises of multiple words
          If InStr(.Text, " ") = 0 Then           
          .MatchWholeWord = False
          .MatchAllWordForms = True           
          .Replacement.Text = Split(xlRList, "|")(i)          
          .Execute Replace:=wdReplaceAll
          Else
           .MatchAllWordForms = False
           .MatchWholeWord = True          
          .Replacement.Text = Split(xlRList, "|")(i)          
          .Execute Replace:=wdReplaceAll
          End If
        Next
      End With
Thanks again for your help.

Regards

Last edited by macropod; 02-26-2020 at 04:10 AM. Reason: Added code tags
Reply With Quote
  #12  
Old 02-26-2020, 04:12 AM
macropod's Avatar
macropod macropod is offline Find, Replace using Excel List Windows 7 64bit Find, Replace using Excel List Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,433
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

Quote:
Originally Posted by pushpi004 View Post
I have found the solution for my problem. The issue with .MatchAllWordForms is that it does not work if the word/string consists of more than a word.
Well, yes, but you never said the Find strings might be more than one word. You only ever mentioned an issue with plurals...
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #13  
Old 03-04-2020, 02:26 PM
pushpi004 pushpi004 is offline Find, Replace using Excel List Windows 10 Find, Replace using Excel List Office 2016
Novice
Find, Replace using Excel List
 
Join Date: Feb 2020
Posts: 17
pushpi004 is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
Well, yes, but you never said the Find strings might be more than one word. You only ever mentioned an issue with plurals...
Yes Paul. I forgot to mention that..please excuse me for that.
Reply With Quote
  #14  
Old 03-25-2020, 08:03 PM
pushpi004 pushpi004 is offline Find, Replace using Excel List Windows 10 Find, Replace using Excel List Office 2016
Novice
Find, Replace using Excel List
 
Join Date: Feb 2020
Posts: 17
pushpi004 is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
Code:
Sub BulkFindReplace()
Application.ScreenUpdating = False
Dim strFolder As String, strFile As String, strDocNm As String, wdDoc As Document
Dim xlApp As Object, xlWkBk As Object, StrWkBkNm As String, StrWkSht As String
Dim iDataRow As Long, xlFList As String, xlRList As String, i As Long
StrWkBkNm = "C:\Users\" & Environ("Username") & "\Documents\BulkFindReplace.xlsx"
StrWkSht = "Sheet1": strDocNm = ActiveDocument.FullName
If Dir(StrWkBkNm) = "" Then
  MsgBox "Cannot find the designated workbook: " & StrWkBkNm, vbExclamation
  Exit Sub
End If
On Error Resume Next
'Start Excel
Set xlApp = CreateObject("Excel.Application")
If xlApp Is Nothing Then
  MsgBox "Can't start Excel.", vbExclamation
  Exit Sub
End If
On Error GoTo 0
With xlApp
  'Hide our Excel session
  .Visible = False
  ' The file is available, so open it.
  Set xlWkBk = .Workbooks.Open(StrWkBkNm, False, True)
  If xlWkBk Is Nothing Then
    MsgBox "Cannot open:" & vbCr & StrWkBkNm, vbExclamation
    .Quit: Set xlApp = Nothing: Exit Sub
  End If
  ' Process the workbook.
  With xlWkBk
    '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))
            xlRList = xlRList & "|" & Trim(.Range("B" & i))
          End If
        Next
      End With
    Else
      MsgBox "Cannot find the designated worksheet: " & StrWkSht, vbExclamation
    End If
  .Close False
  End With
  .Quit
End With
' Release Excel object memory
Set xlWkBk = Nothing: Set xlApp = Nothing
'Exit if there are no data
If xlFList = "" Then Exit Sub
'Get the folder to process
strFolder = GetFolder
If strFolder = "" Then Exit Sub
strFile = Dir(strFolder & "\*.doc", vbNormal)
'Process each document in the folder
While strFile <> ""
  If strFolder & "\" & strFile <> strDocNm Then
    Set wdDoc = Documents.Open(FileName:=strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
    'Process each word from the F/R List
    With wdDoc
      With .Range.Find
        .ClearFormatting
        .Replacement.ClearFormatting
        .MatchWholeWord = True
        .MatchCase = True
        .Wrap = wdFindContinue
        For i = 1 To UBound(Split(xlFList, "|"))
          .Text = Split(xlFList, "|")(i)
          .Replacement.Text = Split(xlRList, "|")(i)
          .Execute Replace:=wdReplaceAll
        Next
      End With
      'Close the document
      .Close SaveChanges:=True
    End With
  End If
  'Get the next document
  strFile = Dir()
Wend
Set wdDoc = Nothing
Application.ScreenUpdating = True
End Sub
 
Function GetFolder() As String
Dim oFolder As Object
GetFolder = ""
Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
Set oFolder = Nothing
End Function
 
Function SheetExists(xlWkBk As Object, SheetName As String) As Boolean
Dim i As Long: SheetExists = False
With xlWkBk
  For i = 1 To .Sheets.Count
    If .Sheets(i).Name = SheetName Then
      SheetExists = True:   Exit For
    End If
  Next
End With
End Function
Hi Paul..I have been using above code in my VBA UserForm for find and replace. I am using . replacement.highlight =true before.matchcase = true. But I facing an issue here. If I have disabled highlight option in any of MS Word file, the code does not highlight the replaced words. Only when I manually select the highlight option, the code then highlights the replaced words. Is there any way to set this highlight property before running the code for find and replace? Thanks
Reply With Quote
  #15  
Old 03-25-2020, 08:19 PM
macropod's Avatar
macropod macropod is offline Find, Replace using Excel List Windows 7 64bit Find, Replace using Excel List Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,433
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

After:
i As Long
insert:
, Hlite As Long
on the same line.

After:
strFile = Dir(strFolder & "\*.doc", vbNormal)
insert new lines with:
'Set the preferred highlight colour
Hlite = Options.DefaultHighlightColorIndex
Options.DefaultHighlightColorIndex = wdBrightGreen

After:
.Replacement.ClearFormatting
insert new lines with:
.Replacement.Highlight = True
.Format = True

After:
Set wdDoc = Nothing
insert new lines with:
'Restore the original highlight setting
Options.DefaultHighlightColorIndex = Hlite
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Find and replace multiple values, according to table value - excel formula EtanM Excel Programming 3 04-11-2016 01:43 AM
Find, Replace using Excel List Find and Replace different in Excel 2010? admyers Excel 1 09-16-2015 10:28 AM
Find/Replace is not working - Excel 2010 fieldhaven Excel 2 02-19-2014 08:30 AM
Find - Replace Macro using a table list mdw Word 0 08-01-2013 04:36 PM
Find, Replace using Excel List Find and Replace using Excel range dmarie123 Word VBA 15 04-02-2013 07:54 AM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 03:29 PM.


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