VBA Excel to VBA Bookmark - Keep Bookmark

Question

I am copying a table to a word document bookmark. The bookmark includes a "placeholder table". The code I'm currently using is

'set word application dimensions
Dim wdApp As Object
Dim wdDoc As Object
Set wdApp = CreateObject("word.application")


'Open the relevent Document
    wdApp.Visible = True
    Set wdDoc = wdApp.Documents.Add(Template:="\\svr\documents\Word\Document Template.dotx", NewTemplate:=False, DocumentType:=0)


    '  Create reference to range and copy

    Dim ExcListObjQ As Range
    Set ExcListObjQ = Sheets("Quote Tables").Range("C4.I24")
    ExcListObjISRQ.Copy


    'Paste Object to Location
    wdDoc.Bookmarks("RNBMQuoteTableW").Range.PasteExcelTable _
    LinkedToExcel:=False, _
    WordFormatting:=False, _
    RTF:=True

I would like to retain the bookmark RNBMQuoteTableW for use at a later date to update this table. I don't want to link the table as there is large number of charts and tables within the word document and linking has a performance impact.

To clarify the table resides inside the bookmark.

The start location of the bookmark may change during the life of the word document, but could be used when running the sub.

This wordmvp.com article has some good info about working with bookmarks and VBA and goes on to address the issue of bookmarks being deleted (with a solution!). (posting as an alternative method of the answer below). — Samuel Everson
Thanks Samuel. Still struggling... although can more accurately say I have "Enclosing Bookmark". I used this text to try and capture the range Set BMrange = wdDoc.Bookmarks("RNBMISRQuoteTableW").Range That isn't working for me — MarkTVC
Cross-posted at: mrexcel.com/board/threads/…. For cross-posting etiquette, please read: excelguru.ca/content.php?184 — macropod

John Korchok John Korchok · Accepted Answer · 2020-06-17T01:34:19

Replacing the content of a bookmark normally deletes the bookmark. So you have to save the bookmark range, then replace it after the paste. Your code is not something I can easily run here for testing, but here is a typical VBA routine to replace a bookmark:

Sub ReplaceBookmark(Which$, What$)
    Dim BookmarkRange As Range, SmartCutPasteWasOnB As Boolean
    If Options.SmartCutPaste = True Then
        SmartCutPasteWasOnB = True
        Options.SmartCutPaste = False
    End If
    Set BookmarkRange = ActiveDocument.Bookmarks(Which$).Range
    BookmarkRange.Text = What$
    ActiveDocument.Bookmarks.Add Name:=Which$, Range:=BookmarkRange
    If SmartCutPasteWasOnB = True Then
        Options.SmartCutPaste = True
    End If
End Sub

Code modified for your application:

Dim BookmarkRange As Range, SmartCutPasteWasOnB As Boolean

If Options.SmartCutPaste = True Then
    SmartCutPasteWasOnB = True
    Options.SmartCutPaste = False
End If
Set BookmarkRange = wdDoc.Bookmarks("RNBMQuoteTableW").Range
BookmarkRange.PasteExcelTable LinkedToExcel:=False, WordFormatting:=False, RTF:=True
wdDoc.Bookmarks.Add Name:="RNBMQuoteTableW", Range:=BookmarkRange
If SmartCutPasteWasOnB = True Then
    Options.SmartCutPaste = True
End If

VBA Excel to VBA Bookmark - Keep Bookmark

2 Answers