?
Solved

Excel adds extra, spurious page breaks

Posted on 2002-05-03
12
Medium Priority
?
1,541 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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
 
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 200 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

649 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