• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1226
  • Last Modified:

Inserting page breaks in excel 2011 with VBA

I am trying to insert page breaks in a worksheet in Excel with VBA.  (code attached)

The sheet has 4 columns, but only columns A and B are important here.  Column A has an alphabetic list of Artists and for each Artist, column B has a list of Songs for that Artist.  The data is divided logically into alphabetical segments with a header row (e.g. "A     B     C", "D     E     F").  Here is an example of the data:

A     B     C
Artist                            Song
Adele                        Hometown Glory
                                Make You Feel My Love
                                One And Only
                                Someone Like You
Albert E. Brumley        I'll Fly Away
Alison Krauss        A Living Prayer
                                Baby, Now That I've Found You
                                But You Know I Love You
                                Forget About It
                                Ghost In This House
                                Oh, Atlanta
                                Stay
                                When You Say Nothing At All
Amos Lee                Arms Of A woman
                                Black River

Whenever Excel's natural page break lands in the middle of a song list, I want to back up and force a page break just before the name of the artist in column A so that the artist and all associated songs start at the top of the next page.  Likewise, if the last row on a page is one of the header rows, I want to bump that row to the top of the next page.

My code does this nicely, but only when I am in debug mode and slowly stepping through it.  If I get out of debug mode and "let 'er rip" the code works for the first case where I insert a page break but then does not work for the remaining situations where it inserts a page break.

In a post somewhere (cannot remember the web site or post topic) someone suggested that when you insert a page break, Excel communicates with the printer.  Thus, when I am stepping slowly through the code in debug mode, there is plenty of time for that.  But when I run the code normally, there is not enough time to accomplish that for the newly inserted page break before the code races along to handle the next situation.

I have tried inserting "Do Events" and "Wait" instructions (e.g. wait 10 seconds), but neither of them has any effect.

Is there any better way to insert page breaks?  (That will be better, but hopefully not super slow.)
Insert-Page-Break-Code.rtf
0
wsturdev
Asked:
wsturdev
  • 5
  • 4
1 Solution
 
Glenn RayExcel VBA DeveloperCommented:
First of all, nice selection of music!  I fired up my Alison Krauss playlist while working on this.. :-)

I had to add several more rows of example artists and titles to test.  When I only had two pages of data, it ran fine in real-time.  I had an issue after I added third page of data, in which case it only adjusted the first page break but not the second.  

So I added a Wait method of just one second and that seems to work.  See the attached example file with this section of modified code at the bottom:
        If currRow <> origRow Then
            rng.EntireRow.Select
            ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
            Application.Wait (Now + TimeValue("0:00:01"))
        End If

Open in new window


-Glenn
EE-TestPageBreaks.xlsm
0
 
wsturdevAuthor Commented:
As I indicated in my original question I tried the Wait method also and it did not work.

My actual data is 254 rows long.  You might have had different results with that much data.  I have attached a sample file with the full data in it.

I have also done more testing:

I checked to see how many page breaks there are by putting this instruction immediately after changing to xlPageLayoutView:
Z = ws.HPageBreaks.Count .  Z was 6 after I executed that statement.

Then, after the loop, but before changing the view back to xlNormalView I inserted the same statement.  Now Z was 8!!

Then I changed the view back to xlNormalView and ran that same statement again and Z was now back to 6!

Somehow, inserting the new breakpoints (which are intended to take the place of existing ones) the loop gets screwed up.
Insert-Page-Break-Sample-Data.xlsx
0
 
Glenn RayExcel VBA DeveloperCommented:
I updated my example workbook with your sample data and could replicate your issue.

I then changed your ActiveWindow.View commands to use the xlPageBreakPreview parameter instead of xlPageLayoutView.  This seemed to work better, so I added a pair of lines after the page break insertion that flips from normal to page break and all breaks look correct.  Finally, I turned off screen updating to speed it up a bit.

See the updated workbook and run "Fix_Breaks" to call the subroutine.

-Glenn
EE-TestPageBreaks.xlsm
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
wsturdevAuthor Commented:
When I run:
    ActiveWindow.View = xlPageBreakPreview
I get a 1004 error. "Method 'View' of object 'Window' failed."

The 3 options for ActiveWindow.View are xlNormalView, xlPageLayoutView and xlPageBreakPreview, so I am not sure why I am getting the error.

Also, I keep forgetting this might make a difference, but I am using Excel 2011 for Mac.

Bill
0
 
wsturdevAuthor Commented:
I just found this on stackoverflow.com:

Although the VBA editor for Mac has the xlPageBreakPreview property it won't be able to execute because there is no page break preview mode in Excel 2011. That is why this will always return an error when executed within Excel 2011.

And it is true, the only view options on the View Menu are "Normal" and "Page Layout".
0
 
Glenn RayExcel VBA DeveloperCommented:
Sorry; I don't have an answer for you there.  I was testing with Excel 2010 (Windows).
0
 
wsturdevAuthor Commented:
I may be forced to reset the page breaks (turn them off) and then loop through adding up the row heights until I find a total height that is appropriate and then back up to the proper row if necessary and set a break.

Right now I cannot think of another way.
0
 
Glenn RayExcel VBA DeveloperCommented:
I'm inclined to agree with you.  You'll just need to estimate the maximum page height printable - including the header row - and then do a running total while checking the value in the Artist column.
0
 
wsturdevAuthor Commented:
I am thankful you have attempted to help me and will accept your last response as the "solution".  Once I figure out a routine as indicated, or if I discover more information that provides a solution, I will come back here and post it.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now