none
slow Excel PageSetup methods

    Question

  • Hey -- I'm not sure if there's a good reason for this, but when using Excel 2003 automation through VSTO 2005, I've noticed performing some tasks takes ridiculously long. Notably, accessing anything through an Excel Worksheet's PageSetup. For example, the following code takes just over 1 second to execute:

                    sheet.PageSetup.Zoom = false;
                    sheet.PageSetup.LeftMargin = 0.4;
                    sheet.PageSetup.RightMargin = 0.4;
                    sheet.PageSetup.TopMargin = 0.49;
                    sheet.PageSetup.BottomMargin = 0.5;
                    sheet.PageSetup.CenterHorizontally = true;

    Does anyone know what's going on here? Am I missing a better way to do this? I've tried creating a PageSetup object and manipulating that, but it's just as slow.

    I've also noticed that adjusting column width is really slow too. Any ideas?

    thanks
    Thursday, December 04, 2008 9:35 PM

Answers

  • Hi ,

    I'd like to say as far as I know there's no the same report from other users that said they have the same issue.So,this may be a typical problem which related with your environment.

    I suggest you check the following things:


    Please check if there is any other automation work at the same time, they may have slow operations that make you thought your add-in is slow.

    Or Please could build a VBA project then run the marco to check if this only happens in VSTO add-in.
    Sub test()  
    With Worksheets(1).PageSetup  
     
        .Zoom = False 
        .LeftMargin = Application.InchesToPoints(10.4)  
        .RightMargin = Application.InchesToPoints(10.4)  
        .TopMargin = Application.InchesToPoints(110.49)  
        .BottomMargin = Application.InchesToPoints(110.5)  
        .CenterHorizontally = True 
     
    End With 
    End Sub 
     


    Also, you could built a clean VSTO Project and just fill the above code to check if there's something wrong with the other part of your code.

    Thanks
    • Marked as answer by Tim Li Friday, December 12, 2008 2:01 AM
    Tuesday, December 09, 2008 9:38 AM

All replies

  • Hi ,

    I'd like to say as far as I know there's no the same report from other users that said they have the same issue.So,this may be a typical problem which related with your environment.

    I suggest you check the following things:


    Please check if there is any other automation work at the same time, they may have slow operations that make you thought your add-in is slow.

    Or Please could build a VBA project then run the marco to check if this only happens in VSTO add-in.
    Sub test()  
    With Worksheets(1).PageSetup  
     
        .Zoom = False 
        .LeftMargin = Application.InchesToPoints(10.4)  
        .RightMargin = Application.InchesToPoints(10.4)  
        .TopMargin = Application.InchesToPoints(110.49)  
        .BottomMargin = Application.InchesToPoints(110.5)  
        .CenterHorizontally = True 
     
    End With 
    End Sub 
     


    Also, you could built a clean VSTO Project and just fill the above code to check if there's something wrong with the other part of your code.

    Thanks
    • Marked as answer by Tim Li Friday, December 12, 2008 2:01 AM
    Tuesday, December 09, 2008 9:38 AM
  • Hi ,

    There a additional comment from my colleague.

    In order increase the performance, we could try to turn off  the UI update when we change the UI setting :


    Application.ScreenUpdating = False

    I'm not sure if this will helps a lot and looking forward to your response.

    Thanks
    Tuesday, December 30, 2008 6:15 AM
  • I’m probably a bit late with the answer but I had the same problem twice. It is the printer driver that causes the problem. When I select another printer as the default printer the execution is be much quicker.

    • Proposed as answer by martinitram Friday, March 12, 2010 1:16 PM
    Wednesday, February 24, 2010 3:10 PM
  • I've had the same problem and, inspired bij 'Anonymous8631 -nice name-, I came up with a workable solution.
    Try this:

    Dim sActivePrinter As String
    sActivePrinter = Application.ActivePrinter
    
    Application.ActivePrinter = "Microsoft XPS Document Writer on Ne01:" 
    
    If ActiveWorkbook.Worksheets.Count > 1 Then
        If vbYes = MsgBox("Pagesetup for every sheet (Yes) or just this sheet (No)?", vbYesNo) Then
            For Each sh In ActiveWorkbook.Worksheets
                Application.ScreenUpdating = False
                    With sh.PageSetup
                        .LeftHeader = "&T"
                        .CenterHeader = "&Z&F"
                        .RightHeader = "&A"
                        .LeftFooter = Application.UserName
                        .CenterFooter = "Page &P of &N"
                        .RightFooter = "&D"
                    End With
                Application.ScreenUpdating = True
            Next sh
        Else
            Application.ScreenUpdating = False
                With ActiveSheet.PageSetup
                    .LeftHeader = "&T"
                    .CenterHeader = "&Z&F"
                    .RightHeader = "&A"
                    .LeftFooter = Application.UserName
                    .CenterFooter = "Page &P of &N"
                    .RightFooter = "&D"
                End With
            Application.ScreenUpdating = True
        End If
    End If
    
    Application.ActivePrinter = sActivePrinter
    The trick is to temporarily set the printer to a documentwriter, like the XPS printer. Code runs 3x faster...



    Martin Drenth | MCP
    • Proposed as answer by evohnave Friday, July 20, 2012 2:49 PM
    Friday, March 12, 2010 1:23 PM