Replacing Merge Field in Word Removes Spaces

[jessebh2003]

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...

    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.

        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.

 

[shknbk2]

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.

    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

 

[shknbk2]

The separate Sub option makes it much shorter in each bookmark. It would go something like this.

    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
...

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