VBA Copy Excel Table to Word Bookmark but keep bookmark

[MarkTVC]

Hi all,

I am running some code which copies a table from Excel to Word inside a bookmark I wish to retain the bookmark for later use. Below is the code.

The following is the general flow:
Opens Word
Opens a Document Template
Copies a ListObject Table
Finds the bookmark in Word
Pastes the Table


Sub UpdateBookmarkContents()

'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\Template.dotx", NewTemplate:=False, DocumentType:=0)


'Clear Clipboard
Application.CutCopyMode = False
' Create reference to QuoteTableW and copy

Dim ExcLisObjq As ListObject
Set ExcLisObjq = Sheets("Quote Tables").ListObjects("QuoteTableW")
ExcLisObjq.Range.Copy

'Paste Object to Location


wdDoc.Bookmarks("RNBMQuoteTableW").Range.PasteExcelTable _
LinkedToExcel:=False, _
WordFormatting:=False, _
RTF:=True

'Clear Clipboard
Application.CutCopyMode = False

End Sub

 

[MarkTVC]

I have found this, but not sure how to apply this to the code:

Working with Bookmarks in VBA

 

[Trevor G]

Welcome to the Forum,

Ok the code should retain your Bookmark as the content is being added at the position of the bookmark. What are you looking to do with the New Document, it isn't being saved or any other action via code. In your comments it says you are opening a document this is adding a new document from the template.

What are you trying to do, besides copying the Excel Table?

 

[MarkTVC]

unfortunately it does delete the bookmark. The paste replace all text and the bookmark.

To answer the question what am I trying to do..

Firstly this is a part of a much bigger set up subs which update the document and save it.

I have a large client report. there are about 20 different tables and charts to be added to specific locations. I can use paste and LinkedToExcel:=True, _ , however the number of tables wanting to refresh have an impact on the document performance.

The goal is to post the tables early in the process, allow the document to be update by user manually, then add the table again at a later date.

 

[Trevor G]

I have just written this code and the Bookmark stays in place. Perhaps you need to add in the GoTo Bookmark.

Sub GoToWord()
Dim wrdApp As Word.Application
Set wrdApp = CreateObject("Word.Application")
Application.Goto Reference:="QuoteTableW"
Selection.Copy
With wrdApp
.Visible = True
.Documents.Open "C:\XXXXX\Heading.docx"
'Bookmark name is tblDrop
.Selection.Goto What:=wdGoToBookmark, Name:="tblDrop"
    .Selection.PasteExcelTable False, False, True
End With
End Sub

 

[MarkTVC]

hmm tried this. still clears the bookmarks

 

[Trevor G]

What happens if you don't have the table within the bookmark and only have the bookmark position. Because otherwise your looking to fill a table and would need to identify which table number it is in the document.

 

[Macropod]

Cross-posted at: VBA Excel to VBA Bookmark - Keep Bookmark (which is where the 'found' answer in post 2 originates)
Please read Mr Excel's policy on Cross-Posting in rule 13: Message Board Rules

 

[MarkTVC]

This has provide an answer for now.


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