CODEWITHSUNDEEP
excelvbams-word
Dmitrij Holkin
Dmitrij Holkin

Reputation: 2055

Insert Word document from template and fill bookmarks from Excel

I need to open a template file, fill its bookmarks, insert a new page from the same template, fill bookmarks again... but only on the inserted page.

Sub test()
Dim WA As Object, WD As Object
    Set WA = CreateObject("Word.Application")
    Set WD = WA.Documents.Add(TemplatesName)
        For i = 1 To 100
            fillBookmarks WA, WD
            With WD.Range
                .Collapse 0
                .InsertBreak Type:=wdSectionBreakNextPage
                .End = WD.Range.End
                '.Collapse 0
                .InsertFile TemplatesName
            End With
        Next i
    WD.SaveAs PdfFile, 17
    WD.Close False: Set WD = Nothing
    WA.Quit False: Set WA = Nothing
End Sub

Function fillBookmarks(ByVal WA As Object, ByVal WD As Object)
    With WD
        .Bookmarks.Item("Client_Code").Range.Text = "545"
            .Bookmarks.Item("Client_Code").Delete
        .Bookmarks.Item("Company_Name").Range.Text = "545"
            .Bookmarks.Item("Company_Name").Delete
        .Bookmarks.Item("Company_Street").Range.Text = "545"
            .Bookmarks.Item("Company_Street").Delete
        .Bookmarks.Item("Company_PostCode").Range.Text = "545"
            .Bookmarks.Item("Company_PostCode").Delete
        .Bookmarks.Item("Company_Country").Range.Text = "545"
            .Bookmarks.Item("Company_Country").Delete
    End With
End Function

UPDATE: the following does a good job but in reverse order (first page became last) how to reverse back it?

        With WD.Range
            .InsertBreak Type:=wdSectionBreakNextPage
            .Collapse Direction:=wdCollapseEnd
            .MoveEnd Unit:=wdCharacter, Count:=-1
            .InsertFile TemplatesName
        End With

The bookmarks are being filled only on first page (also after deleting), so how to insert page from template and fill bookmarks?

Upvotes: 2

Views: 1848

Answers (1)

Cindy Meister
Cindy Meister

Reputation: 25663

There are a couple of issues, here: 1. A bookmark name must be unique in the document. So whenever you insert the new section the bookmarks are being removed if the bookmark names already exist. 2. Getting the insertion point at the right place for new material.

Working with Word Range objects is the correct approach, but it needs to be refined a bit. The code in the question always tries to work with the entire document. It's necessary, however, to work with a more "pin-pointed" range. I've declared two Range objects: one for the entire document, one for the "pin-point" that marks where new content should be inserted.

Notice the use of Duplicate to "copy" the original document Range. If = were used, instead, that would be an exact copy: changing one would automatically change the other.

Notice also the user of the Start and End properties of the two ranges to always position the "pin-point" range at the end of the document.

There's no need to pass the Word application to the function that fills the bookmarks, so I've removed that in my sample code that follows.

For people who come across this question and want to use the code: If the bookmarks are the [content] rather than the I-beam type (there's text inside the bookmark) the original code will cause an error. Writing text to a [content] bookmark automatically removes the bookmark. In that case, the function fillBookmarks_withContent will work. The function fillRemoveBookmarks at the end will work with both types of bookmark.

Sub test()
Dim WA As Object, WD As Object
Dim rngDoc as Object, rngInsert as Object 'both data type Word.Range
    Set WA = CreateObject("Word.Application")
    Set WD = WA.Documents.Add(TemplatesName)
    Set rngDoc = WD.Content
    Set rngInsert = rngDoc.Duplicate
        For i = 1 To 100
            fillBookmarks WD
            With rngInsert
                .Start = rngDoc.End
                .InsertBreak Type:=wdSectionBreakNextPage
                .Start = rngDoc.End
                .InsertFile TemplatesName
            End With
        Next i
    WD.SaveAs PdfFile, 17
    WD.Close False: Set WD = Nothing
    WA.Quit False: Set WA = Nothing
End Sub

Function fillBookmarks(ByVal WD As Object)
    With WD
        .Bookmarks.Item("Client_Code").Range.Text = "545"
            .Bookmarks.Item("Client_Code").Delete
        .Bookmarks.Item("Company_Name").Range.Text = "545"
            .Bookmarks.Item("Company_Name").Delete
        .Bookmarks.Item("Company_Street").Range.Text = "545"
            .Bookmarks.Item("Company_Street").Delete
        .Bookmarks.Item("Company_PostCode").Range.Text = "545"
            .Bookmarks.Item("Company_PostCode").Delete
        .Bookmarks.Item("Company_Country").Range.Text = "545"
            .Bookmarks.Item("Company_Country").Delete
    End With
End Function

Function fillBookmarks_withContent(ByVal WD As Object)
    With WD
        .Bookmarks.Item("One").Range.Text = "545"
        .Bookmarks.Item("TWo").Range.Text = "544"
    End With
End Function

Function fillRemoveBookmarks(ByVal wd As Word.Document)
    With wd
        .Bookmarks.Item("One").Range.Text = "545"
        If Bookmarks.Exists("One") Then .Bookmarks("One").Delete

        .Bookmarks.Item("TWo").Range.Text = "544"
        If Bookmarks.Exists("Two") Then .Bookmarks("Two").Delete
    End With
End Function

Upvotes: 2

Related Questions