Solved

Excel adds extra, spurious page breaks

Posted on 2002-05-03
12
1,218 Views
Last Modified: 2008-02-01
I have some Excel VBA macros which take a large text file and convert to an Excel formatted print. I maintain a line count of details lines, plus two types of total lines. I insert my own page breaks programmatically when I reach a certain line count, and I also take into account the 5 lines repeated as the page header. When the program ahs finished running, and I go into Print Preview or Page Break preview, I find that Excel has added its own Page Breaks usually in front of mine. I then have to go in and drag off the additional breaks so that they coincide with the ones I forced. The Excel breaks appear as dotted (broken) lines and mine as unbroken solid lines. How can I prevent Excel interfering with my program.

macbone2
0
Comment
Question by:macbone2
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 2

Author Comment

by:macbone2
ID: 6989370
I should also have said that when I remove the Excel inserted breaks, my prints still fit the page perfectly.
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6989640
Hi Macbone2,

what i would try instead if that's possible, since i see no remove method on the hpagebreaks collection only an add

-in the pagesetup dialog set your papersource to non page breaks
-then run the code

just an idea
:O)Bruintje
0
 
LVL 2

Author Comment

by:macbone2
ID: 6990084
Hi Bruintje
I'm using Office 97 and when I go into Page Setup | Options | Paper | Paper Source, I am offered only 2 options - Automatic/Normal Media and  Manual/Special Media. Maybe I should choose the second. I'll give this a try next week.
What i've also tried, since I posted this question, and found it seems to work is :_

    ActiveSheet.ResetAllPageBreaks

before running the macro. But I've yet to prove this works in all circumstances.
If i needed to use the method selecting Manual/Special. how can that be done in VBA (I can't record a macro for page setup that records these parameters)

Thanks
macbone2
0
 
LVL 1

Expert Comment

by:SHardy
ID: 6993570
In your page setup, how have you set your scaling?

If you use the "Fit to x pages wide by y tall", then Excel will always use it's own page breaks.

To utilise the page breaks that you have inserted, and no others, then you have to scale by using "Adjust to x% normal size".
0
 
LVL 2

Author Comment

by:macbone2
ID: 6993681
Hi SHardy,
in the print setup macro I have

    With ActiveSheet.PageSetup
        .Zoom = 85
        .FitToPagesWide = 1
        .FitToPagesTall = False

    End With

so the 85% zoom should override the page height because of .FitToPagesTall = False.  In theory the page width shouldn't have any effect on the horizontal page breaks.

Thanks
macbone2
0
 
LVL 1

Expert Comment

by:SHardy
ID: 6993805
Yes it SHOULD do, but then why have the "FitToPagesWide=1"? If you are setting the zoom to 85%, then the FitToPagesWide SHOULD have no effect. What if you replace the above with:

With ActiveSheet.PageSetup
       .Zoom = 85
       .FitToPagesWide = False
       .FitToPagesTall = False

   End With

Does this make any difference?
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 2

Author Comment

by:macbone2
ID: 6993888
I've experimented and found that it doesn't matter whether I use
.FitToPagesWide = False
      or
.FitToPagesWide = 1

Excel still does what IT wants. I was carefully counting rows, and setting row widths to the width I wanted. I calculated 35 rows (including repeated headers for each page) but each time, Excel put a broken page break line 1 row in front of my unbroken, program inserted page break. I dropped my line count to 34, Excel inserted at 33, I dropped to 33, Excel inserted at 32. I finally dropped to line count of 32, and Excel breathed a huge sigh of relief, and didn't insert any more page breaks.
The answer seems to be "Obey Excel and all will be OK".

Thanks all very much for your help

Geoff Mckeown
(Macbone2)
     
0
 
LVL 5

Accepted Solution

by:
TigerMan earned 50 total points
ID: 7005382
macbone2,

Some year or 2 ago I answered a rather complex printing problem.  What follows is a) the original question, and b) my solution that was implemented and worked fine.

Reading through this code, I think you will find some very useful tips.  If you need explanation of anything, please reply with a question.

The original question and details can be found at: http://www.experts-exchange.com/jsp/qShow.jsp?ta=msoffice&qid=10308035 - but of course it will cost you some points.


The original question:

Can we print out runing totals, i.e. for data on single worksheet at the time of printout I would like
to print carried forward total at the end of the page and brought forward total at the starting of the
next page.


The solution:

The following code will insert a subtotal at the bottom of each page, a blank row and a carried forward
subtotal on each subsequent page, and a grand total two cells below the final value in column B.  After
completing the print, it will also delete the inserted rows.  This code will run if your data is set
out as you previously provided i.e. Column A contains customer details, Column B contains Numeric data
for totalling.

If yoru sheet name is not Sheet1, then you will need to change those references in the code below.

It runs at around 3-4 seconds a page on my 400.  There are no doubt quicker ways to do this, however
this method is functional.

Sorry took a while to get back to you - have a lot on at present, and probably bit off more than I should
have.


Sub PrintSubTotals()

' Init looping variable
Counter = 1

Application.ScreenUpdating = False

' Refresh page breaks and clear print areas
ActiveSheet.ResetAllPageBreaks
ActiveSheet.PageSetup.PrintArea = ""

' Loop for processing
While Worksheets("Sheet1").Cells(CStr(Counter + 1), 2).Value > 0


  ' Test if the current row + 1 has page break marker and next row in
  ' column B is not empty
  If Worksheets("Sheet1").Rows(Counter + 2).PageBreak <> xlNone And _
        Range("B" & Counter + 1).Value > 0 Then
     ' Select rows 1 either before and two after break, and insert three new rows
     Rows((Counter + 1) & ":" & (Counter + 3)).Select
     Selection.Insert Shift:=xlDown
   
     ' Show "Sub Total" in bottom of column A
     Cells(Counter + 1, 1).Select
     Cells(Counter + 1, 1).Value = "Sub Total"
     Selection.Font.Bold = True
   
     ' Set value to Sum This Page in column B
     Cells(Counter + 1, 2).Select
     ActiveCell.FormulaR1C1 = "=Sum(R[" & CStr(-Counter) & "]C2:R[-1]C2)"
   
     ' Format the SubTotal and CF cells
     Range("B" & Counter + 1).Select
     Selection.Font.Bold = True
        With Selection.Borders(xlEdgeTop)
           .LineStyle = xlContinuous
           .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeBottom)
           .LineStyle = xlContinuous
           .Weight = xlThin
        End With
     
     Range("B" & Counter + 3).Select
     Selection.Font.Bold = True
     With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
     End With
     With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
     End With
   
     ' Show "Carried Forward" in Column A
     Cells(Counter + 3, 1).Select
     Cells(Counter + 3, 1).Value = "Carry Fwd."
     Selection.Font.Bold = True
   
     ' Set carry forward formula
     Cells(Counter + 3, 2).Select
     ActiveCell.FormulaR1C1 = "=(R[-2]C)"
   
     ' Refresh Page Breaks
     ActiveSheet.ResetAllPageBreaks
     ActiveSheet.PageSetup.PrintArea = ""
   
     ' Increment counter to skip the inserted blank row
     Counter = Counter + 1
   
     ' Keep track of position of last subtotal for Grand Total
     LastSub = Counter
   
  End If
 
  ' Inc counter for next in loop
  Counter = Counter + 1
Wend

' Select next empty cell in B, and insert Grand Total amount
Range("B" & Counter + 2).Select
ActiveCell.FormulaR1C1 = "=Sum(R[" & CStr(-(Counter - LastSub)) & _
                                                  "]C2:R[-1]C2)"
                                                 
' Format Grand Total cell
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
   With Selection.Borders(xlEdgeLeft)
       .LineStyle = xlContinuous
       .Weight = xlMedium
       .ColorIndex = xlAutomatic
   End With
   With Selection.Borders(xlEdgeTop)
       .LineStyle = xlContinuous
       .Weight = xlMedium
       .ColorIndex = xlAutomatic
   End With
   With Selection.Borders(xlEdgeBottom)
       .LineStyle = xlContinuous
       .Weight = xlMedium
       .ColorIndex = xlAutomatic
   End With
   With Selection.Borders(xlEdgeRight)
       .LineStyle = xlContinuous
       .Weight = xlMedium
       .ColorIndex = xlAutomatic
   End With
 
' Provide text and format
Cells(Counter + 2, 1).Value = "Grand Total"
Range("A" & (Counter + 2) & ":B" & Counter + 2).Select
Selection.Font.Bold = True

ActiveWindow.SelectedSheets.PrintOut Copies:=1



' Processing loop to remove inserted rows
For DeleteCounter = 1 To Counter

  If Range("A" & (DeleteCounter + 1)).Value = "Sub Total" Or _
     Range("A" & (DeleteCounter + 1)).Value = "Grand Total" Then
        Rows(DeleteCounter + 1 & ":" & DeleteCounter + 3).Select
        Selection.Delete Shift:=xlUp
  End If

Next

Application.ScreenUpdating = True

End Sub


**** IMPORTANT ****
In my last comment I said you could cut/paste the code into the BeforePrint event.  

If you do this you MUST remove the line of code that reads:

ActiveWindow.SelectedSheets.PrintOut Copies:=1

If you don't your machine will continue printing until memory runs out, or the Titanic sails again.



Dave
0
 
LVL 2

Author Comment

by:macbone2
ID: 7005494
Tigerman,
Thanks for your comment. At the moment I'have these macros working by obeying Excel. see my previous comment. Because I'm folding the text in a couple of the cells with a forced linefeed (so that the 7 columns will fit across the A4 in portrait), I set the RowHeight to 26.25 in all detail, subtotal and total rows. Excel then appears to want to set the page break on row 32. So who am I to argue? What I do now is check if my total rows = 32, then break. The workbook is up and running and in daily use, so I will not interfere with it now. However, I'll try and adapt this code to the layout and see how it runs.
Thanks again
macbone2
0
 
LVL 44

Expert Comment

by:bruintje
ID: 7100158
Hi macbone2, any update on this one?
0
 
LVL 44

Expert Comment

by:bruintje
ID: 7118517
Ok time to clean up

-no activity anymore

my recom will be
-PAQ
-points to Tigerman (see stated Q & A)
-this will be finalized with no further update (06.07.2002)

PLEASE DO NOT ACCEPT THIS COMMENT AS ANSWER

HAGD:O)Bruintje
0
 
LVL 2

Author Comment

by:macbone2
ID: 7121356
I haven't used this code; as I pointed out in an earlier comment, I decide to conform to the demands of Excel, and set my page break after 32 rows. However, I might well use some of the code in the future so I'm happy to award the points to Tigerman.

Thanks all for your time

Geoff
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Some time ago I was asked to create a VBA function that would calculate a check digit for an input number, using the following procedure: First, sum up all the individual digits in the number If that sum value has more than one digit, then sum up …
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now