CODEWITHSUNDEEP
excelvbams-word
MarkTVC
MarkTVC

Reputation: 1

VBA Excel to VBA Bookmark - Keep Bookmark

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.

Upvotes: 0

Views: 990

Answers (2)

MarkTVC
MarkTVC

Reputation: 1

We were able to use this as a solution

Dim iTableNum As Integer

For J = 1 To wdDoc.Tables.Count
wdDoc.Tables(J).Select
iTableNum = J
Exit For
Next J

wdDoc.Bookmarks("RNBMQuoteTableW").Select
wdApp.Selection.Paste

wdDoc.Bookmarks.Add "RNBMISRQuoteTableW", wdDoc.Tables(iTableNum)

End Sub

Upvotes: 0

John Korchok
John Korchok

Reputation: 4913

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

Upvotes: 0

Related Questions