Macro Help: Losing formatting with replace() (MSWord)

OffTopic

New Member
Joined
Sep 4, 2014
Messages
11
I'm having a little trouble retaining formatting when replacing some text in a Word text box.


When I use this line I lose all the individual character formatting for the text range.


Box.TextFrame.TextRange = Replace(Box.TextFrame.TextRange.Text, "replace this text, "with this text")


Box.TextFrame.TextRange is a text box that has been formatted character by character. Picking up attributes like bold, italic, size, font, etc. from a text box inside CorelDraw. After all this is done certain words within the textbox have to be replaced. Is there another way to do this?


Thanks for any help. (I know this isn't an Excel question but the problem would be similar if I were going from Excel to Word instead of CorelDraw to Word.)
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,066
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Which version of Office?
 

Macropod

Retired Moderator
Joined
Aug 27, 2007
Messages
3,511
It really doesn't matter which Office version. The issue is that when you replace formatted content with a plain text string, the formatting is always lost. If you want to retain the character formatting, you'll have to replace the characters one at a time. Of course, that's only going to work properly if both strings are the same length. If they are the same length, you could use code like:
Code:
Sub Demo()
Application.ScreenUpdating = False
Dim i As Long, j As Long
Dim StrFnd As String, StrRep As String
StrFnd = "replace this text": StrRep = "with this text"
j = Len(StrFnd)
If Len(StrFnd) > Len(StrRep) Then j = Len(StrRep)
With ActiveDocument.Shapes(1).TextFrame.TextRange
  With .Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Format = False
    .Text = StrFnd
    .Replacement.Text = StrRep
    .Execute
  End With
  If .Find.Found Then
    With .Duplicate
      For i = 1 To j
        .Characters(i) = Mid(StrRep, i, 1)
      Next
    End With
  End If
End With
Application.ScreenUpdating = True
End Sub
To cater for different-length strings you could add code like the following after the 'Next':
Code:
      If Len(StrFnd) > Len(StrRep) Then
        .Start = .Start + Len(StrRep)
        .Delete
      End If
      If Len(StrRep) > Len(StrFnd) Then
        .Start = .Start + Len(StrFnd)
        .InsertAfter Right(StrRep, Len(StrRep) - Len(StrFnd))
      End If
 

OffTopic

New Member
Joined
Sep 4, 2014
Messages
11
Macropod,
Cross-posting, noted, thank you.

As for your response, thank you again. I probably wasn't specific enough. I'm only replacing a short phrase with a single word. The short phrase will always be formatted uniformly and so the single word will only have one set of formatting attributes. I was able to get by with the following:

Code:
With Box.TextFrame.TextRange.Find
           .Text = "Replace this text"
           .Replacement.Text = "With this text"
           .Wrap = wdFindContinue
           .Execute Replace:=wdReplaceAll
          End With

Your example was incredibly enlightening. (In addition to .Find, Mid() and ScreenUpdating were new to me)

Do you (Macropod or anyone else) know how to set the MatchFuzzy options? Would this property compensate for slight misspellings in either the .Text or the .Replacement.Text strings? I appreciate any and all help.
 

Macropod

Retired Moderator
Joined
Aug 27, 2007
Messages
3,511

ADVERTISEMENT

AFAIK Word's MatchFuzzy Find/Replace settings are only for Japanese text. Is that what you're working with?
 

OffTopic

New Member
Joined
Sep 4, 2014
Messages
11
No, I'm not working with Japanese text, just my own tendency to be inaccurate. Are there any VBA string functions that will compensate for a misplaced space or letter?
 

Macropod

Retired Moderator
Joined
Aug 27, 2007
Messages
3,511
Not really. Although Word's Find/Replace can use wildcards, doing so presupposes that you know at least both which characters might be variable (perhaps even including their case). And, of course, you'd need to avoid the possibility of false matches. Even then, there's no way for it to 'correct' an errant replacement string.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,464
Messages
5,596,288
Members
414,052
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top