1

I am trying to replace a text in a Word file with the content of an Excel cell which includes line breaks. Find part has to be case sensitive. I am trying 2 different methods but they are not working in full.

In Option 1, the "Find" part is case sensitive, but when replacing, the line breaks are replaced with a square.

In Option 2, the replacement completes correctly and keep the line breaks that are included in the cell in Excel, but it is not case sensitive the part of "Find".

I have tried to combined both with no success. Any idea? I am stuck with this several days.

VBA Code Option 1:

Set ObjWord = CreateObject("word.Application")
ObjWord.documents.Open "C:\Example.docx"
ObjWord.Visible = True
With ObjWord.ActiveDocument.Content.Find
            .ClearFormatting
            .Replacement.ClearFormatting
            .Text = "Insert Address"
            .Replacement.Text = Sheet1.Range("C2")
            .Forward = True
            .Wrap = 1           
            .Format = False
            .MatchCase = True
            .MatchWholeWord = True
            .MatchWildcards = False
            .MatchSoundsLike = False
            .MatchAllWordForms = False

            .Execute Replace:=2     
    End With

Result Option 1: (Problem is that Line breaks are replaced with squares).

Testing Industry[]Test Road, 51, 150001 VBA, USA[]Attention: Mr. Macro[]Telephone: +1 666 666 666[]Email: macro@vba.com

VBA Code Option 2:

Set ObjWord = CreateObject("word.Application")
ObjWord.documents.Open "C:\Example.docx"
ObjWord.Visible = True
FindText = "Insert Address"
ReplaceText = Sheet1.Range("C2")
ObjWord.Selection.Move 6, -1 'moverse al principio del documento
ObjWord.Selection.Find.Execute FindText:=FindText
ObjWord.Selection.Text = ReplaceText
ObjWord.Selection.Find.Execute FindText:=FindText

Result Option 2 (Problem is that Find is not case sensitive):

Testing Industry

Test Road, 51, 150001 VBA, USA

Attention: Mr. Macro

Telephone: +1 666 666 666

Email: macro@vba.com

Finally, I found the way to make replace case sensitive, keeping line breaks of the cells, but now I have realized that macro stop with first find, and I need to replace all. Problem is that I cannot use "While" because Word document should remain in Track changes, so using While creates an endless loop. Any option?.

ObjWord.Selection.Move 6, -1 'moverse al principio del documento
ObjWord.Selection.Find.Execute FindText:=FindText, MatchCase:=True
ObjWord.Selection.Text = ReplaceText
ObjWord.Selection.Find.Execute FindText:=FindText, MatchCase:=True

Thanks for the help.

  • 1
    can you use search instead of find perhaps ? – PeterH Nov 13 '19 at 11:48
  • Thanks Peter. I found the way to make my option 2 case sensitive keeping line breaks when replacing. Problem now is that only replace the first item it finds, but it does not do a “replace all”. I cannot use the command “while” because Word document is in track changes. – Fernando Nov 14 '19 at 17:44
  • 1
    Please write your solution into an answer and self-accept it. – CaldeiraG Nov 20 '19 at 15:44
  • Done, thanks for the advice. – Fernando Jan 17 at 13:40
0

Here is the solution:

Objword.ActiveDocument.ShowRevisions = False
ObjWord.Selection.Move 6, -1 'move to the beginning of document
ObjWord.Selection.Find.Execute FindText:=FindText, MatchCase:=True
While objword.Selection.Find.Found = True
ObjWord.Selection.Text = ReplaceText
objword.Selection.Move 6, -1
ObjWord.Selection.Find.Execute FindText:=FindText, MatchCase:=True
Wend
objword.ActiveDocument.ShowRevisions = True
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.