Replace Text in Bookmark in Word without Deleting Bookmark

Question

I'm working on an Excel File that calls a Word Doc Template and updates/replaces all text within specific enclosing bookmarks without deleting the actual bookmark. Retaining the Bookmark brackets in Word is very important because there are cross references that rely on these bookmarks staying intact or they won't work. I've combed a few different posts here and some other forums but after about 4 hours of trial and error I cannot seem to get it working.

The most recent error I get when I run the macro is "Error No: 13" Here's the code - Can anyone help clue me in on what I'm goofing up on please? Utilizing the following advice gets me most of the way there, but when I set the BMRange to the Bookmark, I get the Error 13 - http://word.mvps.org/faqs/macrosvba/InsertingTextAtBookmark.htm

ub BCMerge()
Dim pappWord As Object
Dim docWord As Object
Dim wb As Excel.Workbook
Dim xlName As Excel.Name
Dim TodayDate As String
Dim Path As String
Dim BMRange As Range

  Set wb = ActiveWorkbook
  TodayDate = Format(Date, "mmmm d, yyyy")
  Path = wb.Path & "\pushmerge1.dot"

  On Error GoTo ErrorHandler

'Create a new Word Session
  Set pappWord = CreateObject("Word.Application")

  On Error GoTo ErrorHandler

'Open document in word
  Set docWord = pappWord.Documents.Add(Path)

'Loop through names in the activeworkbook
  For Each xlName In wb.Names

    'if xlName's name is existing in document then put the value in place of the bookmark
        If docWord.Bookmarks.Exists(xlName.Name) Then

       'Identify current Bookmark range and insert text
        Set BMRange = docWord.Bookmarks(xlName.Name).Range '''Mismatch Error 13'''
        BMRange.Text = Range(xlName.Value)
        'Re-insert the bookmark
        docWord.Bookmarks.Add xlName.Name, BMRange
        End If

  Next xlName

'Activate word and display document
  With pappWord
      .Visible = True
      .ActiveWindow.WindowState = 0
      .Activate
  End With

'Release the Word object to save memory and exit macro
ErrorExit:
   Set pappWord = Nothing
   Exit Sub

'Error Handling routine
ErrorHandler:
   If Err Then
      MsgBox "Error No: " & Err.Number & "; There is a problem"
      If Not pappWord Is Nothing Then
        pappWord.Quit False
      End If
      Resume ErrorExit
   End If
End Sub

Comintern Comintern · Accepted Answer · 2015-05-03T22:04:58

The type mismatch comes from how you declare BMRange. A Word Range is not the same object type as an Excel Range, so you need to either set it as an Object (if you don't have a Word reference) or explicitly declare it as Word.Range. Since you appear to be using late binding, change it to Object.

'Incorrect
'Dim BMRange As Range
'Correct
Dim BMRange As Object

After that, all you need to do is keep a copy of the Bookmark range in order to re-add it after changing the Text:

'Loop through names in the activeworkbook
For Each xlName In wb.Names

    'if xlName's name is existing in document then put the value in place of the bookmark
    If docWord.Bookmarks.Exists(xlName.Name) Then
        'Copy the Bookmark's Range.
        Set BMRange = docWord.Bookmarks(xlName.Name).Range.Duplicate
        BMRange.Text = Range(xlName.Value)
        'Re-insert the bookmark
        docWord.Bookmarks.Add xlName.Name, BMRange
    End If

Next xlName

Replace Text in Bookmark in Word without Deleting Bookmark

2 Answers