Microsoft Office Forums - View Single Post

Guessed · 11-04-2021, 03:39 PM

Firstly, I will say I'm not a fan of using bookmarks for a job like this because populating the text inside the bookmark is problematic. Instead, I would recommend you add Content Controls where you want to populate this content.

Secondly, I wouldn't bother with pre-setting the locations in your documents since that seems like an additional step which could simply be part of the Excel macro.

This code is how I would do it for a single document and it could be adapted to sit in your Excel code.

Code:

Sub PopItIn()
  FillCC sCCtitle:="Effective Date", sValue:="my date", sFind:="Effective Date: "
  FillCC sCCtitle:="Policy Number", sValue:="123456", sFind:="Policy Number: "
  FillCC sCCtitle:="Issued To", sValue:="Joe Dirt", sFind:="Issued To: "
End Sub

Function FillCC(sCCtitle As String, sValue As String, sFind As String, Optional aDoc As Document) As ContentControl
  Dim aCC As ContentControl, aRng As Range
  If aDoc Is Nothing Then Set aDoc = ActiveDocument
  If aDoc.SelectContentControlsByTitle(sCCtitle).Count = 0 Then
    Set aRng = aDoc.Range
    With aRng.Find
      .ClearFormatting
      .Text = sFind
      If .Execute Then
        aRng.Collapse Direction:=wdCollapseEnd
        Set aCC = aDoc.ContentControls.Add(Type:=wdContentControlText, Range:=aRng)
        aCC.Title = sCCtitle
      Else
        Debug.Print "Neither Content Control nor Anchor text: " & sFind
        Exit Function
      End If
    End With
  Else
    Set aCC = aDoc.SelectContentControlsByTitle(sCCtitle)(1)
  End If
  aCC.Range.Text = sValue
End Function