Share via

Populating Word template from Excel

Anonymous
2021-02-01T07:05:30+00:00

I use to populate a Word template with data from an Excel workbook which contained many tabs for about 10 years on previous office versions.

Since moving to Office 365 when I run the macro it works for the first tab and thereafter the bookmarks I had in the Word template disappear and it gives me the error "bookmark does not exist". 

This is the code I use:

  Sub ProvTax()

Dim iX As Integer

Dim myInt As Integer

myInt = ActiveDocument.Bookmarks.Count

'Excel

Dim myWB

Set myWB = CreateObject("Excel.Application")

myWB.Workbooks.Open ("C:\Users\DStewart\Desktop\Provisional Tax Masters\Fund V\******.xlsm")  'Source path--------------------------------------------------------

'New Word Document

Dim objWord

Set objWord = CreateObject("Word.Application")

Set objDoc = objWord.Documents.Add()

'objWord.Visible = False

objWord.Visible = True

myWB.Visible = False

For iX = 1 To myWB.Worksheets.Count

    myWB.Sheets(iX).Unprotect

    myWB.Sheets(iX).Activate

    Selection.GoTo What:=wdGoToBookmark, Name:="Name"

    Selection.TypeText (myWB.Sheets(iX).Range("J2"))

    Selection.GoTo What:=wdGoToBookmark, Name:="Address1"

    Selection.TypeText (myWB.Sheets(iX).Range("J3"))

    Selection.GoTo What:=wdGoToBookmark, Name:="Address2"

    Selection.TypeText (myWB.Sheets(iX).Range("J4"))

    Selection.GoTo What:=wdGoToBookmark, Name:="Address3"

    Selection.TypeText (myWB.Sheets(iX).Range("J5"))

    Selection.GoTo What:=wdGoToBookmark, Name:="Address4"

    Selection.TypeText (myWB.Sheets(iX).Range("J6"))

    Selection.GoTo What:=wdGoToBookmark, Name:="Heading"

    Selection.TypeText (myWB.Sheets(iX).Range("B2"))

    Selection.GoTo What:=wdGoToBookmark, Name:="Heading2"

    Selection.TypeText (myWB.Sheets(iX).Range("B3"))

    Selection.GoTo What:=wdGoToBookmark, Name:="Name1"

    Selection.TypeText (myWB.Sheets(iX).Range("J2"))

    Selection.GoTo What:=wdGoToBookmark, Name:="InterestR"

    Selection.TypeText (Fneg(Round(myWB.Sheets(iX).Range("F27"), 2)))

    Selection.GoTo What:=wdGoToBookmark, Name:="Net"

    Selection.TypeText (Fneg(Round(myWB.Sheets(iX).Range("F30"), 2)))

    Selection.GoTo What:=wdGoToBookmark, Name:="Other"

    Selection.TypeText (Fneg(Round(myWB.Sheets(iX).Range("F28"), 2)))

    'Selection.GoTo What:=wdGoToBookmark, Name:="Waco"

    'Selection.TypeText (Fneg(Round(myWB.Sheets(iX).Range("H43"), 2)))

    'Selection.GoTo What:=wdGoToBookmark, Name:="Busby"

    'Selection.TypeText (Fneg(Round(myWB.Sheets(iX).Range("H48"), 2)))

    Selection.WholeStory

    Selection.Copy

    For intUndo = 1 To myInt

       ActiveDocument.Undo 1

    Next intUndo

    objWord.Selection.Paste

    objWord.Selection.InsertBreak Type:=wdPageBreak

Next iX

For iX = 1 To objWord.ActiveDocument.Tables.Count

    If objWord.ActiveDocument.Tables(iX).Columns.Count >= 4 Then  'This if governs the tables. Change to ">= 4" to include the other table as well

        objWord.ActiveDocument.Tables(iX).Range.Select

        objWord.Selection.Font.Size = 10

    End If

Next iX

Set objDoc = Nothing

Set myWB = Nothing

End Sub

Function Fneg(Value)

    Dim myStr

    If Value < 0 Then

        myStr = "(" + Format(CStr(Value * -1), "Standard") + ")"

    Else

        myStr = Format(Value, "Standard")

    End If

     Fneg = myStr

End Function

Microsoft 365 and Office | Word | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

6 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Doug Robbins - MVP - Office Apps and Services 323K Reputation points MVP Volunteer Moderator
    2021-02-01T13:08:50+00:00

    It is all too easy for bookmarks to be destroyed when you insert data into them, which is the reason for my suggesting alternatives.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2021-02-01T11:41:14+00:00

    I haven't tried your new suggestion yet but the previous suggestion is working but has issues.

    It creates the first persons letter from the first tab and then when the macro goes to create the second letter on a new page it gets as far as the Attention section and puts the name from the first tab as well as the name from the second tab on the new letter.

    Thereafter it gives me a run-time error 5941 on the subject line.

    The Word document is created from a .dotm file each time it needs to run.

     'Identify current Bookmark range and insert text

                Dim BMRange As Range

            Set BMRange = ActiveDocument.Bookmarks("Name").Range

            BMRange.Text = (myWB.Sheets(iX).Range("J2"))

            ActiveDocument.Bookmarks.Add "Name", BMRange

            Set BMRange = ActiveDocument.Bookmarks("Address1").Range

            BMRange.Text = (myWB.Sheets(iX).Range("J3"))

            ActiveDocument.Bookmarks.Add "Address1", BMRange

            Set BMRange = ActiveDocument.Bookmarks("Address2").Range

            BMRange.Text = (myWB.Sheets(iX).Range("J4"))

            ActiveDocument.Bookmarks.Add "Address2", BMRange

            Set BMRange = ActiveDocument.Bookmarks("Address3").Range

            BMRange.Text = (myWB.Sheets(iX).Range("J5"))

            ActiveDocument.Bookmarks.Add "Address3", BMRange

            Set BMRange = ActiveDocument.Bookmarks("Address4").Range

            BMRange.Text = (myWB.Sheets(iX).Range("J6"))

            ActiveDocument.Bookmarks.Add "Address4", BMRange

            Set BMRange = ActiveDocument.Bookmarks("Attention").Range

            BMRange.Text = (myWB.Sheets(iX).Range("J2"))

            ActiveDocument.Bookmarks.Add "Attention", BMRange

          Set BMRange = ActiveDocument.Bookmarks("Subject").Range

    BMRange.Text = (myWB.Sheets(iX).Range("B2"))

    ActiveDocument.Bookmarks.Add "Subject", BMRange

            Set BMRange = ActiveDocument.Bookmarks("Yearend").Range

            BMRange.Text = (myWB.Sheets(iX).Range("B3"))

            ActiveDocument.Bookmarks.Add "Yearend", BMRange

            Set BMRange = ActiveDocument.Bookmarks("InterestR").Range

            BMRange.Text = (myWB.Sheets(iX).Range("F27"))

            ActiveDocument.Bookmarks.Add "InterestR", BMRange

            Set BMRange = ActiveDocument.Bookmarks("Net").Range

            BMRange.Text = (myWB.Sheets(iX).Range("F30"))

             ActiveDocument.Bookmarks.Add "Net", BMRange

            Set BMRange = ActiveDocument.Bookmarks("Other").Range

            BMRange.Text = (myWB.Sheets(iX).Range("F28"))

            ActiveDocument.Bookmarks.Add "Other", BMRange

    Was this answer helpful?

    0 comments
  3. Doug Robbins - MVP - Office Apps and Services 323K Reputation points MVP Volunteer Moderator
    2021-02-01T10:16:25+00:00

    I would recommend that you populate a new sheet it Excel by referencing the data on the individual sheets in a layout that is suitable for use as a mail merge data source and then use mail merge to create the documents.

    The following code could be used to create that sheet and populate it with the data in the required layout

    Dim i As Long, r As Long

    Dim lngsheets As Long

    Dim shtTarget As Worksheet

    lngsheets = ActiveWorkbook.Sheets.Count

    Set SheetTarget = ActiveWorkbook.Sheets.Add

    With SheetTarget.Range("A1")

        .Offset(0, 0) = "Name"

        .Offset(0, 1) = "Address1"

        .Offset(0, 2) = "Address2"

        .Offset(0, 3) = "Address3"

        .Offset(0, 4) = "Address4"

        .Offset(0, 5) = "Heading"

        .Offset(0, 6) = "Heading2"

        .Offset(0, 7) = "Name1"

        .Offset(0, 8) = "InterestR"

        .Offset(0, 9) = "Net"

        .Offset(0, 10) = "Other"

        r = 1

        For i = 1 To ActiveWorkbook.Sheets.Count

            .Offset(r, 1) = ActiveWorkbook.Sheets(1).Range("J2")

            .Offset(r, 2) = ActiveWorkbook.Sheets(1).Range("J3")

            .Offset(r, 3) = ActiveWorkbook.Sheets(1).Range("J4")

            .Offset(r, 4) = ActiveWorkbook.Sheets(1).Range("J5")

            .Offset(r, 5) = ActiveWorkbook.Sheets(1).Range("J6")

            .Offset(r, 6) = ActiveWorkbook.Sheets(1).Range("B2")

            .Offset(r, 7) = ActiveWorkbook.Sheets(1).Range("B3")

            .Offset(r, 8) = Abs(Round(ActiveWorkbook.Sheets(1).Range("F27")))

            .Offset(r, 9) = Abs(Round(ActiveWorkbook.Sheets(1).Range("F30")))

            .Offset(r, 10) = Abs(Round(ActiveWorkbook.Sheets(1).Range("F28")))

            r = r + 1

        Next i

    End With

    Was this answer helpful?

    0 comments
  4. Anonymous
    2021-02-01T08:55:09+00:00

    I don't understand how to use:

    Dim BMRange As Range
    'Identify current Bookmark range and insert text
    Set BMRange = ActiveDocument.Bookmarks("MyBookmark").Range
    BMRange.Text = "Hello world"
    'Re-insert the bookmark
    ActiveDocument.Bookmarks.Add "MyBookmark", BMRange

    How would I be able to get the name and address with all the tables and figure into a letter with the above when each tab in excel has different names and financial data.

    Thanks in advance

    Was this answer helpful?

    0 comments
  5. Doug Robbins - MVP - Office Apps and Services 323K Reputation points MVP Volunteer Moderator
    2021-02-01T07:51:53+00:00

    I would suggest that you use the Range object rather than the Selection object,

    See the article "Working with Bookmarks in VBA” at:

    http://wordmvp.com/FAQs/MacrosVBA/WorkWithBookmarks.htm

    However, I would suggest that you use DOCVARIABLE fields instead of Bookmarks.

    Was this answer helpful?

    0 comments