Thursday, December 04, 2008 9:35 PMHey -- 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?
Tuesday, December 09, 2008 9:38 AMModeratorHi ,
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.
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.
- Marked As Answer by Tim LiModerator Friday, December 12, 2008 2:01 AM
Tuesday, December 30, 2008 6:15 AMModeratorHi ,
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.
Wednesday, February 24, 2010 3:10 PM
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
Friday, March 12, 2010 1:23 PMI'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 = sActivePrinterThe 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