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

Answer 1

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

Answer 2

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

Answer 3

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.

Answer 4

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