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

Question

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.

You can use Document.Range.Information(wdNumberOfPagesInDocument) to get the number of pages in a Word document. Store the number of pages before you insert the table, compare with the number of pages after you insert the table. (Enum wdNumberOfPagesInDocument = 4) — Chronocidal
Thanks for the suggestion Chronocidal! That sounds like a promising route. I'll have to think about how to use that property because some of the tables are quite long and take up to 3 pages though. Just because the Word document has added a page doesn't mean that I don't need to use a hard page break. For instance, I might have 60 rows (perhaps 40 of which fit on one page). Then the 2nd page has the next 20. I want a hard page break after that. — dejour
Chronocidal, I think I'm going to play with this other property. Document.Range.Information(wdVerticalPositionRelativeToPage) — dejour

dejour dejour · Accepted Answer · 2018-03-22T16:52:24

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.

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

2 Answers