Populate word document from excel without deleting bookmarks

Question

I am trying to populate a Word document based on data from Excel. Due to number of specific work requirements, I need to retain the bookmarks in Word. I have used these sites as resources.

Replace Text in Bookmark in Word without Deleting Bookmark http://wordmvp.com/FAQs/MacrosVBA/InsertingTextAtBookmark.htm http://www.wiseowl.co.uk/blog/s199/word-bookmarks.htm

I am getting a compile error in the last line of CopyCell.

Option Explicit
Dim wd As New Word.Application
Dim DataCell As Range

Sub ReportData()

'Open word template
wd.Documents.Open (Range("D4") & Range("D5"))
wd.Visible = True

'Creates range with all of the data used in the report
Dim DataRange As Range
Range("D7").Select
Set DataRange = Range(ActiveCell, ActiveCell.End(xlDown))

'Uses copycell function. "Name" is the bookmark name, 0 is the Rowoffset
For Each DataCell In DataRange
  CopyCell "Name", 0
  CopyCell "Employer", 1
Next

End Sub

Sub CopyCell(BookMarkName As String, RowOffset As Integer)

Dim BMRange As Word.Range

wd.Selection.GoTo What:=wdGoToBookmark, Name:=BookMarkName
Set BMRange = wd.Selection.Range.Duplicate
BMRange.Text = DataCell.Offset(RowOffset, 0).Value
wd.Bookmarks.Add BookMarkName, BMRange

End Sub

score 1 · Accepted Answer · answered Apr 04 '17 at 16:40

Bookmarks is a property of a Document object not of Word Application object

so you have to change:

wd.Bookmarks.Add BookMarkName, BMRange

to:

wd.ActiveDocument.Bookmarks.Add BookMarkName, BMRange

furthermore you may consider what follows:

you should limit the use of Public variable to where strictly unavoidable (e.g.: to communicate with UserForms)
avoid the Activate/ActiveXXX/Selection/Select pattern and use fully qualified range references
you're iterating through a "vertical" range and then you're offsetting current cell "vertically" (i.e. one cell down) again: may be you wanted to offset "horizontally" (i.e. to the adjacent cell)?

for all what above I'd propose the following refactoring of your code:

Option Explicit

Sub ReportData()

    Dim wd As Word.Application
    Dim DataCell As Range

    Set wd = New Word.Application

   'Open word template
    wd.Documents.Open Range("D4") & Range("D5")
    wd.Visible = True

    'Creates range with all of the data used in the report
    With Range("D7")
        'Uses copycell function. "Name" is the bookmark name, 0 is the Rowoffset
        For Each DataCell In Range(.Cells, .End(xlDown))
          CopyCell wd, DataCell, "Name", 0
          CopyCell wd, DataCell, "Employer", 1
        Next
    End With

    wd.ActiveDocument.Close True '<--| close and save word document
    wd.Quit '<--| close word application
    Set wd = Nothing '<--| clean memory
End Sub

Sub CopyCell(wd As Word.Application, DataCell As Range, BookMarkName As String, ColOffset As Integer)

    Dim BMRange As Word.Range

    wd.Selection.GoTo What:=wdGoToBookmark, Name:=BookMarkName
    Set BMRange = wd.Selection.Range.Duplicate
    BMRange.Text = DataCell.Offset(0, ColOffset).Value
    wd.ActiveDocument.Bookmarks.Add BookMarkName, BMRange

End Sub

Populate word document from excel without deleting bookmarks

1 Answers1