Replacing Merge Field in Word Removes Spaces

jessebh2003

Board Regular
Joined
Feb 28, 2020
Messages
71
Office Version
  1. 365
Platform
  1. Windows
My Excel macro opens a Word template and replaces a specified merge field with the text from an identified cell. This all works great except that when the merge field is replaced, either the preceding or following space is being removed too.

For instance...

VBA Code:
    Dim wrdApp As Word.Application
    Dim wrdDoc As Word.Document
    Set wrdApp = CreateObject("Word.Application")
    wrdApp.Visible = True
    Set wrdDoc = wrdApp.Documents.Open("SomeTemplateName.dotx")
    
    Dim Fld As Word.Field
    
    With wrdDoc
        'Populates Level 1 Text
        Set Fld = GetField(wrdDoc, "OverallMeanScore")
        If Not Fld Is Nothing Then
            Fld.Select
            .Application.Selection.Delete
            .Application.Selection.Text = Range("B44").Text
            .Application.Selection.Collapse wdCollapseEnd
        End If
                
        Set Fld = GetField(wrdDoc, "OverallStDev")
        If Not Fld Is Nothing Then
            Fld.Select
            .Application.Selection.Delete
            .Application.Selection.Text = Range("C44").Text
            .Application.Selection.Collapse wdCollapseEnd
        End If

"B44" = 4.69
"C44" = 0.09

In the Word document, the text is <<OverallMeanScore>> ± <<OverallStDev>>. But when running the macro, the values end up being 4.69 ±0.09. The preceding space for "0.09" is removed.

Elsewhere in the Word document, the merge codes are within tables and are replaced the same as above.

VBA Code:
        Set Fld = GetField(wrdDoc, "RelevantMean")
        If Not Fld Is Nothing Then
            Fld.Select
            .Application.Selection.Delete
            .Application.Selection.Text = Range("B5").Text
            .Application.Selection.Collapse wdCollapseEnd
        End If
        
        Set Fld = GetField(wrdDoc, "RelevantStDev")
        If Not Fld Is Nothing Then
            Fld.Select
            .Application.Selection.Delete
            .Application.Selection.Text = Range("C5").Text
            .Application.Selection.Collapse wdCollapseEnd
        End If

"B5" = 4.65
"C5" = 0.66

In the Word document, the text is <<RelevantMean>> ± <<RelevantStDev>>. The values end up being 4.65± 0.66. The following space for "4.65" is removed.

Both of these behaviors are occurring throughout the document both in paragraphs and in tables. How can I correct this from happening? Thanks.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
I've run into this before as well even when manually selecting words, sentences, etc. Word likes to delete spaces.

This is what I've used in programming. The trick is to put a period before and after the selection you want to delete. Then, delete the periods after you do what you want in between. This updated code will select the merge field, put a period before and after, change the merge field, then remove the periods. The spacing in front and after the old fields is maintained. Copy this same type of code wherever you need it. Or you can shorten it up by making another Sub with these functions and calling it every time.

VBA Code:
    Dim r As Word.Range
    Dim Fld As Word.Field
  
    With wrdDoc
        Set Fld = GetField(wrdDoc, "OverallMeanScore")
        If Not Fld Is Nothing Then
            With .Application.Selection
                Fld.Select
                .Collapse wdCollapseStart
                .TypeText "."
                Fld.Select
                .Collapse wdCollapseEnd
                .TypeText "."
                Fld.Select
                .Delete
                Set r = .Application.Selection.Range
                r.Text = Range("B5").Text
                r.Select
                .Collapse wdCollapseStart
                .TypeBackspace
                r.Select
                .Collapse wdCollapseEnd
                .Delete
            End With
        End If
 
Upvote 0
The separate Sub option makes it much shorter in each bookmark. It would go something like this.

VBA Code:
    With wrdDoc
        Set Fld = GetField(wrdDoc, "OverallMeanScore")
        If Not Fld Is Nothing Then
            UpdateField wrdDoc, Fld, Range("B5").Text
        End If

        Set Fld = GetField(wrdDoc, "OverallStDev")
        If Not Fld Is Nothing Then
            UpdateField wrdDoc, Fld, Range("C5").Text
        End If
...

VBA Code:
Sub UpdateField(Doc As Word.Document, F As Word.Field, Value As String)
    Dim r As Word.Range
   
    With Doc.Application.Selection
        F.Select
        .Collapse wdCollapseStart
        .TypeText "."
        F.Select
        .Collapse wdCollapseEnd
        .TypeText "."
        F.Select
        .Delete
        Set r = .Application.Selection.Range
        r.Text = Range("B5").Value
        r.Select
        .Collapse wdCollapseStart
        .TypeBackspace
        r.Select
        .Collapse wdCollapseEnd
        .Delete
    End With
End Sub
 
Upvote 0
Solution

Forum statistics

Threads
1,212,927
Messages
6,110,695
Members
448,293
Latest member
jin kazuya

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
Back
Top