Results 1 to 3 of 3

Thread: Page count of printout is inconsistent

  1. #1
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    675
    Articles
    0
    Excel Version
    Excel 2010 64bit

    Page count of printout is inconsistent



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Have a workbook I created 11 or 12 years ago with Excel 97.

    Users enter data on a daily basis via user form and info goes to a Master sheet.

    There is a command button on the user form to print out the Master sheet. Clicking this button brings up another small user form telling the user how many pages the printout will be and yes-no buttons to print or not.

    Here's the problem, from day 1 I have used numpages = ExecuteExcel4Macro("Get.Document(50)") to get the printout page count.

    It has always given the correct number of pages to be printed. If I run the workbook in Excel 2003 it is correct. The current Master sheet I am working with is 4 pages.
    If I run the workbook in Excel 2010 the number of pages indicated is 2 not 4.

    If I use numpages = ActiveSheet.HPageBreaks.Count + 1 Excel 2003 still indicates the proper 4 pages will be printed, but Excel 2010 tells me the printout will be 133 pages.

    Can any one suggest what I need to do to rectify this issue?

    Thanks in advance
    NoS

  2. #2
    Running your line of code in my Excel 2010 gives the correct page count, so it doesn't seem to be version related.

    Also, using the HPageBreaks count method also returns the correct count (but had to remove the +1)

    Don't know the cause of your issue, but the above at least rules out a potential version issue with the code (which is pretty much generic).

    Have you, or can you, test the code on a different PC running 2010?

  3. #3
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    675
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Palmetto, thanks for taking a look at this situation.

    After much Goggling and experimentation I have discovered that if the sheet is put into layout view before HPageBreaks.count the result is correct.
    May have something to do with the print sheet page setup being landscape orientation.

    Code:
         ActiveWindow.View = xlPageLayoutView
         numpages = Sheets("Master").HPageBreaks.Count + 1
         ActiveWindow.View = xlNormalView
    This works in my Excel 2010, but Excel 2003 can't deal with xlPageLayoutView and gives a compiler error of "variable not defined".
    If I comment out the xlLayoutView line Excel 2003 has no issues.

    It's kind of looking like I'll be putting the above code in it's own procedure, check for the version of Excel being used, and only call it for 2010 (and 2007 ???) having Excel 2003 and older avoid xlPageLayoutView altogether.

    Thanks again
    NoS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •