Issues with replacing bookmarked data

Question

I recently asked a similar question but thanks to a comment I have made some updates. However, I am running into some issues.

I am attempting to use Word bookmarks in a "template" to auto-populate the text that is bookmarked based on excel values. My Macro does almost everything I want it to do, except some of the text is not showing up in the output. I have a template (word doc) that looks like this:

..date..

..ID..

..Address Line 1.

..Address Line 2..

..Address Line 3..

Dear Customer,

I want the "..date.." to be replaced with the current date, "..ID.." with the ID (from an excel workbook), and so on. Here is my code:

' Filepath to the data and template
Const FilesPath As String = "filepath"
Const TemplateFile As String = "temp.docx"

Sub CreateWordDocuments()
    Dim prenom As String
    Dim nom As String
    Dim firstname As String
    Dim lastname As String
    Dim add1 As String
    Dim add2 As String
    Dim add3 As String
    Dim id As String
    Dim wd As Word.Application
    Dim doc As Word.Document
    Dim tday
    Dim NomRange As Range, NomCell As Range, ws As Worksheet
   
    Set ws = ActiveSheet
    Set NomRange = ws.Range("A2", ws.Cells(Rows.Count, 1).End(xlUp))
    Set wd = New Word.Application
    wd.Visible = True
    
    For Each NomCell In NomRange.Cells
        
        Set doc = wd.Documents.Open(FilesPath & TemplateFile)
        With NomCell.EntireRow
        firstname = .Columns("C").Value
        lastname = .Columns("B").Value
        add1 = .Columns("X").Value
        add2 = .Columns("Y").Value
        add3 = .Columns("Z").Value
        id = .Columns("A").Value
        tday = Date
        If .Columns("J").Value = "Keep" Then
           doc.Bookmarks("date").Range.Text = tday
           doc.Bookmarks("id").Range.Text = id
           doc.Bookmarks("name").Range.Text = firstname & lastname
           doc.Bookmarks("add1").Range.Text = add1
           doc.Bookmarks("add2").Range.Text = add2
           doc.Bookmarks("add3").Range.Text = add3
           
           doc.SaveAs2 FilesPath & firstname & ".pdf", wdExportFormatPDF
           doc.Close False
        End If
        End With
    Next NomCell
    wd.Quit
End Sub

However, when I run this Macro, The address line bookmarks are removed from the final word output- the only things there are the date and ID. Any thoughts as to what may be happening?

Why not move this test `If .Columns("J").Value = "Keep" Then` further up, directly under `With NomCell.EntireRow`? No need to read any values if you're not going to populate a Word document with the result... If you're missing text in the final PDF that tends to suggest that `add1` etc are not getting correctly populated - did you debug to track down what's going wrong? — Tim Williams, Aug 19 '22 at 17:06

Tim Williams · Answer 1 · 2022-08-19T22:00:05.817

This worked for me in testing. I tend to avoid "single-use" variables (populated but then only accessed once) and that can simplify your code by reducing the bulk.

' Filepath to the data and template
Const FilesPath As String = "C:\Temp\Test\"
Const TemplateFile As String = "temp.docx"

Sub CreateWordDocuments()
    Dim wd As Word.Application, doc As Word.Document
    Dim NomCell As Range, ws As Worksheet
   
    Set ws = ActiveSheet
    Set wd = New Word.Application
    wd.Visible = True
    
    For Each NomCell In ws.Range("A2", ws.Cells(Rows.Count, 1).End(xlUp)).Cells
        
        With NomCell.EntireRow
            If .Columns("J").Value = "Keep" Then
               'open as read-only
               Set doc = wd.Documents.Open(FilesPath & TemplateFile, ReadOnly:=True)
               doc.Bookmarks("date").Range.Text = Date
               doc.Bookmarks("id").Range.Text = .Columns("A").Value
               doc.Bookmarks("name").Range.Text = .Columns("C").Value & " " & _
                                                  .Columns("B").Value
               doc.Bookmarks("add1").Range.Text = .Columns("X").Value
               doc.Bookmarks("add2").Range.Text = .Columns("Y").Value
               doc.Bookmarks("add3").Range.Text = .Columns("Z").Value
               
               doc.SaveAs2 FilesPath & .Columns("C").Value & ".pdf", _
                                                   wdExportFormatPDF
               doc.Close False
            End If
        End With
    Next NomCell
    wd.Quit
End Sub

The word documents still seem to not be populating correctly. The name and address lines are not being shown. Do you think a "find and replace" type method is possible and perhaps would solve this issue? — z_11122, Aug 20 '22 at 02:18
I think that generally The bookmark approach is easier and typically more reliable. It would be better to fix whatever is going wrong with your setup. — Tim Williams, Aug 20 '22 at 16:19
I ran the code in pieces, and it appears to breakdown when it gets to the "name" bookmark. The date and id work fine, but for anything after this point, the last bookmark is the only one that populates in the word document. So if I have code for "name" and "add1", only "add1" shows up in the output. Any thoughts as to why this may be occurring? — z_11122, Aug 22 '22 at 12:52
Difficult for me to troubleshoot unless you're able to share the Word document and the exact code. This type of approach has always worked well for me. — Tim Williams, Aug 22 '22 at 16:04

Sgdva · Accepted Answer · 2022-08-22T19:07:07.563

Explanation
While I do agree that bookmarks are useful, I have found easier just to replace keywords within a template with what I want: basically recreating what bookmarks do, but in my own terms. This is very useful when you are developing to larger groups, since they may get confused on how the bookmarks need to be handled and need to be set up, whereas explaining "(Name)" will be replaced for the name at Excel is easier for final users that may use the macro as well. I used a generic template from word called "Travel Newsletter" as an example for the word template, it looked nice and has many elements to show the efficency of doing it this way (formatting is kept, images, etc). The key function is "Exec_ReplaceInDocument", please see code and demonstration to understand the logic to it.
Demonstration

Code

' Filepath to the data and template
Const FilesPath As String = "filepath"
Const TemplateFile As String = "temp.docx"
Sub CreateWordDocuments()
    Dim wd As Word.Application
    Dim doc As Word.Document
    Dim CounterRow As Long
    Set wd = New Word.Application
    wd.Visible = True
    For CounterRow = 2 To Cells.SpecialCells(xlCellTypeLastCell).Row
    Set doc = wd.Documents.Open(FilesPath & TemplateFile, ReadOnly:=True)
    Call Exec_ReplaceInDocument(doc, "(date)", Format(Cells(CounterRow, 2).Value, Cells(CounterRow, 2).NumberFormat))
    Call Exec_ReplaceInDocument(doc, "(Name)", Cells(CounterRow, 3).Value)
    Call Exec_ReplaceInDocument(doc, "(LastName)", Cells(CounterRow, 4).Value)
    doc.SaveAs2 FilesPath & Cells(CounterRow, 3).Value & ".pdf", wdExportFormatPDF
    doc.Close False
    Next CounterRow
    wd.Quit
    'Whenever working with objects, even if the object has a property to close, I like to set them to nothing
    Set doc = Nothing: Set wd = Nothing
End Sub
Sub Exec_ReplaceInDocument(ObjWordDocument As Word.Document, TxtToFind As String, TxtToReplace As String)
    With ObjWordDocument.Content.Find
    .ClearFormatting
    .Text = TxtToFind
    .Replacement.ClearFormatting
    .Replacement.Text = TxtToReplace
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchCase = True
    .MatchWholeWord = False
    .Execute Replace:=wdReplaceAll
    End With
End Sub

Further comments
I noticed you have some checks before (like if cell has keep or not) and some other fields, I just made it generic, so just adjust to your needs.

Issues with replacing bookmarked data

2 Answers2