CODEWITHSUNDEEP
excelvbams-word
Mikki
Mikki

Reputation: 1

How to add to Bookmark collection?

I have working VBA code that pulls information from Excel and autofills a Word document.

I want to add another column/bookmark.

I added new bookmarks in the Word template and added the lines:

.BookMarks("CouncilRegion2").Range.Text = Range("W" & r).Value
.BookMarks("CouncilRegion3").Range.Text = Range("X" & r).Value

I get

'Run-time error '5941': The requested member of the collection does not exist.

I did not write the code, I maintain it and add new lines when needed.

I tried changing the Range.

Private Sub CreateTemplate1(tPath As String, r As Integer)
    Dim wdApp As Object
    Dim wdDoc As Object
        
    On Error Resume Next
    Set wdApp = GetObject(, "Word.Application")
    If wdApp Is Nothing Then Set wdApp = CreateObject("Word.Application")
    On Error GoTo 0
        
    wdApp.Visible = True
    'wdApp.DisplayAlerts = False
    Set wdDoc = wdApp.Documents.Open(FileName:=tPath)
        
    With wdDoc
        .BookMarks("STPNumber").Range.Text = Range("L" & r).Value
        .BookMarks("ProposedUse").Range.Text = Range("L" & r).Value
        .BookMarks("SiteAddress").Range.Text = Range("E" & r).Value
        .BookMarks("LotRp").Range.Text = Range("O" & r).Value
        .BookMarks("hSTPNumber").Range.Text = Range("L1").Value
        .BookMarks("hSiteAddress").Range.Text = Range("E" & r).Value
        .BookMarks("hLotRp").Range.Text = Range("O" & r).Value
        .BookMarks("ClientName").Range.Text = Range("C" & r).Value
        .BookMarks("ClientName1").Range.Text = Range("C" & r).Value
        .BookMarks("TownPlanner").Range.Text = Range("Q" & r).Value
        .BookMarks("ProposedUse1").Range.Text = Range("L" & r).Value
        .BookMarks("SiteAddress1").Range.Text = Range("E" & r).Value
        .BookMarks("CouncilRegion").Range.Text = Range("P" & r).Value
        .BookMarks("CurrentDate").Range.Text = Format(Now(), "dd/mm/yyyy")
        .BookMarks("CouncilFee").Range.Text = Range("F" & r).Value
        .BookMarks("CouncilFee1").Range.Text = Range("F" & r).Value
        .BookMarks("hours").Range.Text = Range("K" & r).Value
        .BookMarks("hours1").Range.Text = Range("K" & r).Value
        .BookMarks("SiteAddress2").Range.Text = Range("E" & r).Value
        .BookMarks("ProposedUse2").Range.Text = Range("L" & r).Value
        .BookMarks("SiteAddress3").Range.Text = Range("E" & r).Value
        .BookMarks("LotRp1").Range.Text = Range("O" & r).Value
        .BookMarks("SiteAddress4").Range.Text = Range("E" & r).Value
        .BookMarks("LotRp2").Range.Text = Range("O" & r).Value
        .BookMarks("ProposedUse3").Range.Text = Range("L" & r).Value
        .BookMarks("CouncilRegion2").Range.Text = Range("W" & r).Value
        .BookMarks("CouncilRegion3").Range.Text = Range("X" & r).Value
                              
        Dim ourFee As Long, ourTotal As Long
        Dim ourGST As Long, ourDeposit As Long
            
        ourFee = Range("G" & r).Value
        ourGST = ourFee * 0.1
        ourTotal = ourFee + ourGST
        ourDeposit = ourTotal * 0.6
            
        .BookMarks("OurFeeGST").Range.Text = Format(ourFee, "#,###.00")
        .BookMarks("OurFee").Range.Text = Format(ourFee, "#,###.00")
        .BookMarks("OurGST").Range.Text = Format(ourGST, "#,###.00")
        .BookMarks("OurTotal").Range.Text = Format(ourTotal, "#,###.00")
        .BookMarks("OurDeposit").Range.Text = Format(ourDeposit, "#,###.00")
    End With
End Sub

The code opens a Word template that is saved in the same folder, and autofills the document using Bookmarks that have been set up.

It won't autofill the lines that I have added and then comes up with the error.

Upvotes: 0

Views: 179

Answers (1)

Tim Williams
Tim Williams

Reputation: 166351

Try printing out all the bookmark names (to the immediate pane in the VB editor) and make sure you see the ones you added:

'...
'...
Set wdDoc = wdApp.Documents.Open(FileName:=tPath)
Dim bm
For Each bm In wdDoc.Bookmarks
    Debug.Print bm.Name
Next bm
'...
'...

Upvotes: 1

Related Questions