PowerPoint Interop Assemblies Slowness Issue

2
votes

I'm using MS Office interop assemblies to build a Powerpoint addin that inserts slides and charts on the fly. But during insertion it take up to 20 seconds to complete the process from inserting the slide to insertion of the chart.

After doing extensive logging, I noticed few lines of code where our application gets stuck for 20+ seconds. Here are those lines:

powerpointChartObj.SetSourceData(Source:=String.Format("=Sheet1!A1:B{0}", 5))

powerpointSlideObject.Shapes("some shape name")

We continuously play with PowerPoint chart and slide objects during the slide insertion process but some time it gets blocked on one of above lines. And it's random on which line it gets blocked.

Following are the office libraries we are using:

  1. Microsoft Office 16.0 Object Library
  2. Microsoft Excel 16.0 Object Library
  3. Microsoft Graph 16.0 Object Library
  4. Microsoft PowerPoint 16.0 Object Library
  5. Microsoft Word 16.0 Object Library
  6. Microsoft.Office.Tools.dll
  7. Microsoft.Office.Tools.Common.dll
  8. Microsoft.Office.Tools.Common.v4.0.Utilities.dll
  9. Microsoft.Office.Tools.v4.0.Framework.dll
  10. Microsoft.Vbe.Interop

Test Environemnt

Office 2016

Windows 8.1 + Windows 10

16 GB RAM, Intel Core i5-4570 CPU

Any solution or workaround would be highly appreciated.

PS. Also tried using Office 15.0 Object libraries

Updated

I've tried EnableEvents and set it false forever. It boost up the performance during chart insertion/update scenarios but if we continuously make calls to PowerPoint COM components(e.g. Shapes, slides etc. to manipulate PowerPoint things), PowerPoint gets stuck for around 20 seconds again. Not sure if there is anything similar to EnableEvents available in PowerPoint interop.

1
c#vb.netinteroppowerpointoffice-interop
Does the problem code run on a secondary thread? Is the thread it runs on a STAThread or MTAThread? – TnTinMn
It's running on default main thread. Nothing special is specified. – Azaz ul Haq
Delays that long are almost always environment problems. Temporarily disable the installed anti-malware product. And beware of page file thrashing, a pretty likely problem here. Use Task Manager, Processes tab and ensure you don't see a large amount of orphaned Excel.exe or Winword.exe processes. – Hans Passant
Thanks @HansPassant. Disabling windows defender real-time protection doesn't help. System specifications are updated in question. Also noticed that when Excel program is already open things gets done in 3-4 seconds. But still that's a huge time for desktop application. – Azaz ul Haq

1 Answers

2
votes

I don't have the rights to comment yet, so bear with me if this is not the perfect answer.

My experience with automation with office products is through VBS, but they both uses the COM-objects, so it probably works the same. I mostly use Excel, so powerpoint is not my strongest side.

My suggestion is to temporary disable screenupdates/events while adding data.

Here is an example (using VBS):

Application.Calculation = xlCalculationManual 'Excel specific
Application.ScreenUpdating = False 'All office products
Application.DisplayStatusBar = False 'All office products
Application.EnableEvents = False 'All office products
'your code running here
Application.EnableEvents = True 'All office products
Application. DisplayStatusBar = True 'All office products
Application.ScreenUpdating = True 'All office products
Application.Calculation = xlCalculationAutomatic 'Excel specific

Also avoid using a lot "select" calls in data, since it slows things down.

In my VBS scripts I got more or less no lag or problems by using this technique. I would also suggest that you create your own "please wait" popup screen while adding alot of data. For some calls do take time, even if you do it manually in Powerpoint / Excel.

Good luck, office automation can be a hazzle :)