Insert Section break on bookmark position of word through Excel macro

Question

I'm trying to create an automated report. So far I've managed to be able to create the word document from a template full of bookmarks, insert specific data from excel into the correct positions and finally to save the file as .docx and a random name.

My problem is that after all these bookmarks are updated I need to add a section break on bookmark "PgBrk" and create a new page. I don't mind preserving any bookmarks since any new file is being automatically saved with a new name; thus my template is always intact.

Been looking on the net for the last couple of days but always end up with some type of error, depending on what I've tried.

My code goes like this:

Sub REP_DET()

Dim Template_Path As String
Dim Template_Name As String
Dim LRandomNumber As Integer

Template_Path = Application.ActiveWorkbook.Path    
Template_Name = "\template.docx"

Randomize
LRandomNumber = Int((9999 - 1000 + 1) * Rnd + 1000) 'Int ((min - max + 1) * Rnd + min)

Set wApp = CreateObject("Word.Application")
    wApp.documents.Open (Template_Path & Template_Name)
    wApp.Visible = True
Set wdoc = wApp.documents.Open(Template_Path & Template_Name)
With wdoc
    .Bookmarks("Item_Code").Range.Text = Sheets("A").Range("H2").Value

' various figures are populated in designated bookmark positions within the doc file
end with
With wdoc
    .Bookmarks("PgBrk").Range.Select
    With Selection
        .Collapse Direction:=0
        .InsertBreak Type:=wdSectionBreakNextPage
    End With
End With

With wdoc
    .SaveAs2 Filename:=(Template_Path & "\template" & LRandomNumber & ".docx"), _
FileFormat:=wdFormatXMLDocument, AddtoRecentFiles:=False
End With

Any help appreciated.

Please clarify 'some error'... does the error always occur in the code related to ".Bookmarks("PgBrk").Range.Select"? And if so, what error did you get for that code? — Wayne G. Dunn
@ Wayne G. Dunn the error is "Run-time error 438 Object doesn't support this property or method" and occurs right after the ".Bookmarks("PgBrk").Range.Select" line which works properly as the bookmark is always selected in the word document. The code breaks right after this line and the same error is reported both at ".Collapse Direction:=0" or ".InsertBreak Type:=wdSectionBreakNextPage" line (depending on whether I have the former enabled in my code). Thank you! — aldebaran
I may be mistaken about this, but it may be possible that not all properties and methods are exposed for Word if you are executing thru Excel [anyone want to chime in?] I had a lot of 'Word' code in Access and the only way I could get some of it to work was to create a VBA subroutine in my 'Normal' template and then call that from Access. If I do the same with your code, no error, but it still doesn't work. I can find the bookmark, insert a page break, then insert section break,but not sure if that will solve your issue. — Wayne G. Dunn

Olaf Olaf · Accepted Answer · 2019-05-12T08:39:42

Try:

With wdoc .Bookmarks("PgBrk").Range.Select With **.**Selection

So that it uses the Selection of Word as opposed to Excel. Also "With wdoc.selection" would do the trick I think

Insert Section break on bookmark position of word through Excel macro

1 Answers