none
Excel Interop; How to reset PageSetup.Pages after PageSetup.PaperSize change. RRS feed

  • Question

  • I'm printing an Excel worksheet from code using the Excel.Interop. The worksheet was orignally set up for paper size A4. At this paper size, the spreadsheet spanned 4 printed pages (contained in the PageSetup.Pages property). I programatically changed the page size to Letter, which makes the page a bit wider and now there is only data on 2 of the 4 pages, but the PageSetup.Pages.Count is still 4 (which includes 2 blank pages). I've tried calling the Worksheet.ResetAllPageBreaks() method, but this doesn't seem to have any effect on the PageSetup.Pages count ... can anyone point me in the right direction? Is there anyway to programatically have Excel recalculate the number of pages required for print after changing the page size? I need a page count prior to the print call. If I open the spreadsheet in Excel and change the paper size there, the Page Layout recalculates everything automagically ... what is that automagical method that I need to call to update the Pages collection?

    Thanks,

    Brandon


    • Edited by Brandon72 Monday, May 13, 2013 9:39 PM typo
    Monday, May 13, 2013 9:39 PM

Answers

  • Hi Bspweb,

    Thank you for posting in the MSDN Forum.

    As far as I'm concerned, the pages.count property will change automatically and you don't have to update it manually.

    I've adapted your code from this page into below.

    int pages = worksheet.PageSetup.Pages.Count;
    //The former page size was A4. 
    worksheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperLetter;
    pages = worksheet.PageSetup.Pages.Count;
    

    In the code above, I use the variable pages to get the page count. The former page size was A4 with 2 pages of data. After I change the size into letter, the pages turned into 4 automatically. 

    I suggest you create a new application and a new workbook to test this or change your original workbook page setup -> Scaling -> Adjust 100% normal size and try again.

    Hope it helps.

    Best regards,

     


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Brandon72 Tuesday, May 14, 2013 3:21 PM
    Tuesday, May 14, 2013 2:56 PM
    Moderator

All replies

  • Hi Bspweb,

    Thank you for posting in the MSDN Forum.

    As far as I'm concerned, the pages.count property will change automatically and you don't have to update it manually.

    I've adapted your code from this page into below.

    int pages = worksheet.PageSetup.Pages.Count;
    //The former page size was A4. 
    worksheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperLetter;
    pages = worksheet.PageSetup.Pages.Count;
    

    In the code above, I use the variable pages to get the page count. The former page size was A4 with 2 pages of data. After I change the size into letter, the pages turned into 4 automatically. 

    I suggest you create a new application and a new workbook to test this or change your original workbook page setup -> Scaling -> Adjust 100% normal size and try again.

    Hope it helps.

    Best regards,

     


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Brandon72 Tuesday, May 14, 2013 3:21 PM
    Tuesday, May 14, 2013 2:56 PM
    Moderator
  • Quist,

    Thanks for the reply. I was walking through this with a colleague just before I saw your reply. As I was walking through it I did create a console app to try and reproduce and isolate the issue. The weird thing was ... in the console application the page count gets recomputed as you say above. This was very strange since I had the exact same code in my application and it WASN'T being recomputed. So we started looking for subtle differences between the two applications. Nothing. Checked the interop versions. Same. So we went so far as to clean both projects and I decided to even shut down both VS instances and reopen the projects ...

    At this point is when it dawned on me what the difference between the two projects was ... I run VS with my administrator account when working on our application, but the console application runs with my lower privileged account. I reran VS as administrator and opened the console app and it quit working. This is strange since it appears as though my highly privileged administrator account is the one with the issue ... it doesn't happen when VS is running with my low privileged user account.

    So, this may be because of the user that installed Excel on my computer? Or it may be a bug in Visual Studio debugger? Or it could be an issue within the Interop? I have no idea ... but if I run VS as low privilege user the issue goes away ... which is a problem because other areas of our application need administrator rights. Any thoughts? Not sure how to tell you to go about reproducing this issue, but it is definitely there.

    I marked your previous reply as the answer to my original issue, since, as you say, it does recompute after the page size change ... now I just have to nail down why it behaves differently depending on the privilege of the user running Visual Studio.

    Thanks for your help.

    Brandon P

    Tuesday, May 14, 2013 3:21 PM