CODEWITHSUNDEEP
vbaexcelms-word
dejour
dejour

Reputation: 1

Determining whether or not a Word document has just inserted a soft page break

I have an Excel VBA program that can produce hundreds of potential reports in Word. It exports a large table (up to 100 rows) from Excel to Word, and then inserts a hard page break before the next section. Then it continues to the next section of the report, pasting another table. The issue is that sometimes the table length is just the right size so that Word inserts a soft page break. Then the Excel VBA code adds a hard page break, and the result is a blank page in the middle of the report.

I'd like to insert some sort of IF statement where the Excel VBA determines if a soft page break has just occurred and only insert a hard page break if a soft page break is not there. Is this possible?

Here's the relevant code:

For Each cell In ThisWorkbook.Names("TemplateTextRange").RefersToRange.Cells

If cell.Value <> False Then
  If UCase(Right(cell.Value, 8)) = "TBLPASTE" Then

        strTableName = Left(cell.Value, Len(cell.Value) - 10)
        DoEvents
        ThisWorkbook.Names(strTableName).RefersToRange.Copy
        DoEvents
        wrdApp.Selection.Paste
        Application.CutCopyMode = False
        DoEvents
        wrdApp.Selection.TypeParagraph

  ElseIf cell.Value = "<insert page break>" Then

      wrdApp.Selection.InsertBreak Type:=wdPageBreak

  Else
    wrdApp.Selection.EndKey Unit:=wdStory
    DoEvents
    cell.Copy
    DoEvents
    wrdApp.Selection.PasteAndFormat (wdPasteDefault)
    Application.CutCopyMode = False
    DoEvents
    wrdApp.Selection.TypeParagraph
  End If
End If

Next cell

What it is doing is reading a set of instructions from an Excel worksheet. A potential template might look like this:

Cell 1:FALSE

Cell 2:A bunch of text (appears as written)

Cell 3:SummaryValuesTable, TBLPASTE (tells the program to paste the values in range SummaryValuesTable as a table)

Cell 4:A bunch more text

Cell 5:insert page break> (tells the program to insert a hard page break)

Cell 6:A bunch more text

Cell7:AnotherTable, TBLPASTE

etc.

Upvotes: 0

Views: 272

Answers (2)

CSS
CSS

Reputation: 182

Try using PageBreakBefore property unless you really need visible hard page break. I mean, instead of wrdApp.Selection.InsertBreak Type:=wdPageBreak use wrdApp.Selection.ParagraphFormat.PageBreakBefore = true

Upvotes: 0

dejour
dejour

Reputation: 1

I used the Information(wdVerticalPositionRelativeToPage) property to determine where on the page I was.

Due to large headers, when the cursor was positioned at the top of the page, the value was < 290.

And so this IF statement was placed before the .InsertBreak command:

If wrdApp.Selection.Information(wdVerticalPositionRelativeToPage) > 290 Then
        wrdApp.Selection.InsertBreak Type:=wdPageBreak
End If

I wish the 290 wasn't so hard-coded but it works for my purposes. I suppose for more dynamic code, you can retrieve the cursor position when you know you are at the top of the page. Save it as a variable, and then use that in place of the 290.

Upvotes: 0

Related Questions