Problems Using Page Breaks in Excel 97 from Visual Basic
Author Message
Problems Using Page Breaks in Excel 97 from Visual Basic

Hi,

I hope somebody can help me with this problem.  I have a spreadsheet
that haas varying lengths of reports depending on information entered in
the spreadsheet.  I have been able to generate the different reports
depending on the information.  The problem that I need help solving is
inserting Horizontal Page Breaks into the report if the length of the
report is greater than a set amount.  I know what the set length of the
report that I am using as the limit and how many pages that the report
needs to
be.  I know that I have to use a Select Case statement to choose the
proper page break insertion points depending on how
many pages the report is.  Here is the scenario:

1}    I have the Print Area set to a specific range that does NOT change
no matter which report is being printed.
2}    I have determined how long the report is through a For Next loop
adding up the height of each row in points.
3}    I know the limit that I want to have on one page.  It is 1300
points.  If the report is longer than 1300 points, I set the
number of pages required to 2 instead of 1.
4}    I know where the page break has to be inserted if the report has
to be on 2 pages.

What I need to know is how to set the page breaks.  Here is the code
that I have tried:

Sub Print_Routine(PrintCopy As Integer, PageSplit As String)
'
' Print_Routine Macro
' Printing routine that determines the number of pages the
' report should be spread over and sets up the page properly.
'
Worksheets("Sheet1").ResetAllPageBreaks
ReportPages = 1
Select Case PageSplit
Case "Y"
ReportPoints = 0
ReportRows = Worksheets("Sheet1").Range("Print_Area").Rows.Count

For I = 1 To ReportRows
b = Worksheets("Sheet1").Range("Print_Area").Rows(I).Height
ReportPoints = ReportPoints + b
Next I
If ReportPoints > 1299 Then
ReportPages = 2
End If
Case "N"
ReportPages = 1
End Select
With Worksheets("Sheet1").PageSetup
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = ReportPages
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.Orientation = xlPortrait
.PaperSize = xlPaperLegal
End With
Select Case ReportPages
Case 2
Before:=Worksheets("Sheet1").Range("A72")
End Select
Worksheets("Sheet1").PrintOut Copies:=PrintCopy
Select Case ReportPages
Case 2
ReportPages = 1
Worksheets("Sheet1").ResetAllPageBreaks
End Select
End Sub

When there is two pages to print, the application calculates where the
page breaks should be because of the FitToPagesTall parameter.  I have
inserted break points and checked the cell A72 to find that there is a
page break inserted
there but it does not show up when the Page Break Preview is selected.
When the report is sent to the printer, what I see on the screen is what
ends up on the printer.  I could manually record the macro for the part
that moves the automatic page break but when the number of lines on the
report changes, I would have to re-record the macro to move the
automatic page break.  I have tried a couple different ways but have not
yet been able to solve the problem.

If you think you might be able to help, please respond to my email

Trevor Schellhorn

Sat, 01 Dec 2001 03:00:00 GMT
Problems Using Page Breaks in Excel 97 from Visual Basic
Trevor,
If you use fittopages, it overrides all your pagebreaks.  You will have to
do it without using FitToPages.  You need to scale you sheet rather than use
FitToPages.

Regards,
Tom Ogilvy

Quote:

>Hi,

>I hope somebody can help me with this problem.  I have a spreadsheet
>that haas varying lengths of reports depending on information entered in
>the spreadsheet.  I have been able to generate the different reports
>depending on the information.  The problem that I need help solving is
>inserting Horizontal Page Breaks into the report if the length of the
>report is greater than a set amount.  I know what the set length of the
>report that I am using as the limit and how many pages that the report
>needs to
>be.  I know that I have to use a Select Case statement to choose the
>proper page break insertion points depending on how
>many pages the report is.  Here is the scenario:

>1}    I have the Print Area set to a specific range that does NOT change
>no matter which report is being printed.
>2}    I have determined how long the report is through a For Next loop
>adding up the height of each row in points.
>3}    I know the limit that I want to have on one page.  It is 1300
>points.  If the report is longer than 1300 points, I set the
>number of pages required to 2 instead of 1.
>4}    I know where the page break has to be inserted if the report has
>to be on 2 pages.

>What I need to know is how to set the page breaks.  Here is the code
>that I have tried:

>Sub Print_Routine(PrintCopy As Integer, PageSplit As String)
>'
>' Print_Routine Macro
>' Printing routine that determines the number of pages the
>' report should be spread over and sets up the page properly.
>'
>Worksheets("Sheet1").ResetAllPageBreaks
>ReportPages = 1
>Select Case PageSplit
>    Case "Y"
>        ReportPoints = 0
>        ReportRows = Worksheets("Sheet1").Range("Print_Area").Rows.Count

>        For I = 1 To ReportRows
>            b = Worksheets("Sheet1").Range("Print_Area").Rows(I).Height
>            ReportPoints = ReportPoints + b
>        Next I
>        If ReportPoints > 1299 Then
>            ReportPages = 2
>        End If
>    Case "N"
>        ReportPages = 1
>End Select
>With Worksheets("Sheet1").PageSetup
>    .Zoom = False
>    .FitToPagesWide = 1
>    .FitToPagesTall = ReportPages
>    .LeftMargin = Application.InchesToPoints(0.5)
>    .RightMargin = Application.InchesToPoints(0.5)
>    .TopMargin = Application.InchesToPoints(0.5)
>    .BottomMargin = Application.InchesToPoints(0.5)
>    .Orientation = xlPortrait
>    .PaperSize = xlPaperLegal
>End With
>Select Case ReportPages
>    Case 2
>Before:=Worksheets("Sheet1").Range("A72")
>End Select
>Worksheets("Sheet1").PrintOut Copies:=PrintCopy
>Select Case ReportPages
>    Case 2
>        ReportPages = 1
>        Worksheets("Sheet1").ResetAllPageBreaks
>End Select
>End Sub

>When there is two pages to print, the application calculates where the
>page breaks should be because of the FitToPagesTall parameter.  I have
>inserted break points and checked the cell A72 to find that there is a
>page break inserted
>there but it does not show up when the Page Break Preview is selected.
>When the report is sent to the printer, what I see on the screen is what
>ends up on the printer.  I could manually record the macro for the part
>that moves the automatic page break but when the number of lines on the
>report changes, I would have to re-record the macro to move the
>automatic page break.  I have tried a couple different ways but have not
>yet been able to solve the problem.

>If you think you might be able to help, please respond to my email

>Trevor Schellhorn

Sun, 02 Dec 2001 03:00:00 GMT
Problems Using Page Breaks in Excel 97 from Visual Basic
Thanks for your help.  It was much appreciated.

Trevor

Quote:

> Trevor,
> If you use fittopages, it overrides all your pagebreaks.  You will have to
> do it without using FitToPages.  You need to scale you sheet rather than use
> FitToPages.

> Regards,
> Tom Ogilvy

> >Hi,

> >I hope somebody can help me with this problem.  I have a spreadsheet
> >that haas varying lengths of reports depending on information entered in
> >the spreadsheet.  I have been able to generate the different reports
> >depending on the information.  The problem that I need help solving is
> >inserting Horizontal Page Breaks into the report if the length of the
> >report is greater than a set amount.  I know what the set length of the
> >report that I am using as the limit and how many pages that the report
> >needs to
> >be.  I know that I have to use a Select Case statement to choose the
> >proper page break insertion points depending on how
> >many pages the report is.  Here is the scenario:

> >1}    I have the Print Area set to a specific range that does NOT change
> >no matter which report is being printed.
> >2}    I have determined how long the report is through a For Next loop
> >adding up the height of each row in points.
> >3}    I know the limit that I want to have on one page.  It is 1300
> >points.  If the report is longer than 1300 points, I set the
> >number of pages required to 2 instead of 1.
> >4}    I know where the page break has to be inserted if the report has
> >to be on 2 pages.

> >What I need to know is how to set the page breaks.  Here is the code
> >that I have tried:

> >Sub Print_Routine(PrintCopy As Integer, PageSplit As String)
> >'
> >' Print_Routine Macro
> >' Printing routine that determines the number of pages the
> >' report should be spread over and sets up the page properly.
> >'
> >Worksheets("Sheet1").ResetAllPageBreaks
> >ReportPages = 1
> >Select Case PageSplit
> >    Case "Y"
> >        ReportPoints = 0
> >        ReportRows = Worksheets("Sheet1").Range("Print_Area").Rows.Count

> >        For I = 1 To ReportRows
> >            b = Worksheets("Sheet1").Range("Print_Area").Rows(I).Height
> >            ReportPoints = ReportPoints + b
> >        Next I
> >        If ReportPoints > 1299 Then
> >            ReportPages = 2
> >        End If
> >    Case "N"
> >        ReportPages = 1
> >End Select
> >With Worksheets("Sheet1").PageSetup
> >    .Zoom = False
> >    .FitToPagesWide = 1
> >    .FitToPagesTall = ReportPages
> >    .LeftMargin = Application.InchesToPoints(0.5)
> >    .RightMargin = Application.InchesToPoints(0.5)
> >    .TopMargin = Application.InchesToPoints(0.5)
> >    .BottomMargin = Application.InchesToPoints(0.5)
> >    .Orientation = xlPortrait
> >    .PaperSize = xlPaperLegal
> >End With
> >Select Case ReportPages
> >    Case 2
> >Before:=Worksheets("Sheet1").Range("A72")
> >End Select
> >Worksheets("Sheet1").PrintOut Copies:=PrintCopy
> >Select Case ReportPages
> >    Case 2
> >        ReportPages = 1
> >        Worksheets("Sheet1").ResetAllPageBreaks
> >End Select
> >End Sub

> >When there is two pages to print, the application calculates where the
> >page breaks should be because of the FitToPagesTall parameter.  I have
> >inserted break points and checked the cell A72 to find that there is a
> >page break inserted
> >there but it does not show up when the Page Break Preview is selected.
> >When the report is sent to the printer, what I see on the screen is what
> >ends up on the printer.  I could manually record the macro for the part
> >that moves the automatic page break but when the number of lines on the
> >report changes, I would have to re-record the macro to move the
> >automatic page break.  I have tried a couple different ways but have not
> >yet been able to solve the problem.

> >If you think you might be able to help, please respond to my email
> >address and to the Newgroup.