Replacing Merge Field in Word Removes Spaces

jessebh2003

New Member
Joined
Feb 28, 2020
Messages
34
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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

shknbk2

Active Member
Joined
Mar 5, 2016
Messages
386
Office Version
  1. 365
Platform
  1. Windows
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
 

shknbk2

Active Member
Joined
Mar 5, 2016
Messages
386
Office Version
  1. 365
Platform
  1. Windows
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
 

Forum statistics

Threads
1,147,477
Messages
5,741,364
Members
423,657
Latest member
Medrok2021

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