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 IndustryTest Road, 51, 150001 VBA, USAAttention: Mr. MacroTelephone: +1 666 666 666Email: email@example.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):
Test Road, 51, 150001 VBA, USA
Attention: Mr. Macro
Telephone: +1 666 666 666
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.