Copying excel cells (ex. A1:C10) to word document using bookmarks without deleting the bookmarks

Question

I have tried finding a solution to this but with little succes. I was succesfully able to update my Word Document but only once. I checked the word document and the bookmark has disappeared so doesn't work the second time unless I add a bookmark again in the Word Document. Please find the code below and let me know what may be done to achieve the required. Thanks!!

Sub Rectangle2_Click()
Dim tbl As Excel.Range
Dim WordApp As Word.Application
Dim myDoc As Word.Document
Dim WordTable As Word.Table

'Optimize Code
 Application.ScreenUpdating = False
 Application.EnableEvents = False

'Copy Range from Excel
 Set tbl = ThisWorkbook.Worksheets("Sheet1").Range("C6:M10")

'Create an Instance of MS Word
 On Error Resume Next

'Is MS Word already opened?
  Set WordApp = GetObject(class:="Word.Application")

'Clear the error between errors
  Err.Clear

'If MS Word is not already open then open MS Word
  If WordApp Is Nothing Then Set WordApp = CreateObject(class:="Word.Application")

'Handle if the Word Application is not found
  If Err.Number = 429 Then
    MsgBox "Microsoft Word could not be found, aborting."
    GoTo EndRoutine
  End If

 On Error GoTo 0

'Make MS Word Visible and Active
 WordApp.Visible = True
 WordApp.Activate

'Open the Report
 Set myDoc = WordApp.Documents.Open("C:\Users\Test.docx")

'Copy Excel Table Range
 tbl.Copy

'Delete old Table in MS Word & Paste New Table into MS Word
 Dim bkm As Bookmark
 For Each bkm In ActiveDocument.Bookmarks
    If bkm.Name = bkmname Then
        If bkm.Range.Information(wdWithInTable) = True Then
            bkm.Range.Expand (wdCell)
            bkm.Range.Cells.Delete
        End If
    End If
 Next bkm

myDoc.Bookmarks(1).Range.PasteExcelTable _
LinkedToExcel:=False, _
WordFormatting:=False, _
RTF:=False

'Autofit Table so it fits inside Word Document
 Set WordTable = myDoc.Tables(1)
 WordTable.AutoFitBehavior (wdAutoFitWindow)

 EndRoutine:
'Optimize Code
 Application.ScreenUpdating = True
 Application.EnableEvents = True

'Clear The Clipboard
 Application.CutCopyMode = False

'Closing the MS Word
 ActiveDocument.Close SaveChanges:=wdSaveChanges
 End Sub

NOTE : I am purely using VBA for the first time but have pieced this up from different sources with hopefully the right logic in mind

score 0 · Accepted Answer · answered Mar 09 '16 at 19:13

There is a way to re-use bookmarks, but if I correctly understand what you're doing it would be much better for you to create a template file (.dotx) and generate a new document from it for each iteration.

Your code should then be

Set myDoc = Documents.Add("template path")

and you save each document using the Document.SaveAs method.

The bookmarks will remain intact since your code always works with a fresh document created from the template.

Additional note: Since you're declaring an object (myDoc) use that instead of ActiveDocument! This will make your code more robust, since the user or other code could change which document is currently active in the Word UI. So, for example:

For Each bkm In myDoc.Bookmarks

In case you need to retain bookmarks, the way to do this is to first save the bookmark Range, assign the content to the bookmark (which deletes it), then restore the bookmark around the range. Something like:

Dim rngBkm as Word.Range
For Each bkm In myDoc.Bookmarks
  If bkm.Name = bkmname Then
    Set rngBkm = bkm.Range
    rngBkm.Text = "new content"
    myDoc.Bookmarks.Add(rngBkm, bkmname)
  End If
Next

Copying excel cells (ex. A1:C10) to word document using bookmarks without deleting the bookmarks

1 Answers1