Solved: Painfully slow PowerPoint automation in Office 2010

[Sycdan]

Hi all,

Firstly, a little introduction: I have been writing VBA code for about 4 years, for multiple clients within one very large company, with a great deal of focus put on speed and efficiency. Most of what I do amounts to producing sales performance reports in PowerPoint (sometimes Excel). I generally store code in Excel workbooks -- "codebooks" -- and manipulate Access & PowerPoint from there, where needed.

Until very recently, I was using Office 2003, and all was well with the world. Then, my clients started upgrading to 2010; I followed suit, having done a bit of research to determine (or so I thought) that the vast majority of my code would work as it always had. I was largely correct, with a couple of notable exceptions, one of which is why I am here seeking your help.

I have a very large monthly report, which consists of creating around 20 presentations containing 200 or so slides each, with a lot of charts and tables. These all require a large amount of data, the raw form of which I parse into an Access database then load into Excel's memory (consuming about 1GB of it) before creating any presentations, so it's all available at runtime. Might sound silly, but after a great deal of experimentation, that was by far the fastest method I found. In Office 2003, creating the presentations took about 20 minutes per, which I thought to be just about tolerable. In stark contrast, Office 2010 requires 2-3 hours to do exactly the same thing (create one of the many presentations). This is just not acceptable to me, and I really hope someone can give me some insight into how I might speed things up, if it's even possible.

Now for my actual observations:
It seems that where PowerPoint 2010 is the most inefficient is with drawing tables. Filling a 20x13 table, cell by cell, with varying cell background colours, takes me nearly 2 minutes now, as opposed to a lot less than 1 in 2003.
Another sticking point appears to be custom chart labels (not auto-text ones), again with varying colours.What I have tried:
Converting all files to native Office 2010 formats (xlsm, pptx), instead of running everything in compatibility mode. This made no difference.
Finding some way to emulate Application.ScreenUpdating = false (via the Windows API calls "LockWindowUpdate" and "SendMessage WM_SETREDRAW"). Again, either these just didn't work or made no discernible difference, though the SendMessage approach did seem to disable redrawing the PowerPoint window.I'd greatly appreciate any advice on how I could speed up my processes. I know I've not posted any code, but that's probably not going to help at this point... I will if it seems useful given any feedback I get, but there is a lot of it.

Thank you for your patience and possible help; I know I rambled a lot!

-Dan

Edit: thought I should mention that I use PowerPoint tables and Graph charts, rather than embedded Excel sheets and charts. I plan to move to the latter in future, but it's not an option in the short term.

[Paul_Hossler]

One thing to try is to turn off screen updating

Look in the thread here called



Solved: PP2010 ScreenUpdating = False


Paul

[John Wilson]

Are you saying that filling ONE table with various colours takes minutes?

It took me 3-4 seconds to fill each cell in a 20 x 13 table with random colours in 2010. Screen updating made no difference.

Maybe post some code

[Sycdan]

Are you saying that filling ONE table with various colours takes minutes?

It took me 3-4 seconds to fill each cell in a 20 x 13 table with random colours in 2010. Screen updating made no difference.

Maybe post some code

Update: I have now tried downgrading to Office 2007, and the performance is still bad.

John: Yes, I agree, it is quick if you just run some code from a module located within a PPT file. Try using the same code called from Excel, however, and the result is very different. Running code directly from PPT is not really feasible for me, in this case.

Forgetting the colours for now, this is a very slim version of the code I use:

Sub PPTFillTable()

Dim ppApp As PowerPoint.Application
Dim sh As PowerPoint.Shape
Dim tr As PowerPoint.TextRange
Dim tc As PowerPoint.Cell
Dim iR As Long, iC As Long
Dim start As Double

start = Timer
Randomize Timer
Set ppApp = GetObject(, "Powerpoint.Application")
Set sh = ppApp.ActivePresentation.Slides(1).Shapes("table")
For iR = 1 To 20
For iC = 1 To 13

Set tc = sh.Table.Cell(iR, iC)
Set tr = tc.Shape.TextFrame.TextRange
tr.Text = Int(Rnd() * 1000)

Next iC
Next iR
Debug.Print Format(Timer - start, "#,##0.0") & " seconds"

End Sub
Running that code from a module within a .ppt file with one slide that has a 20x13 table on it takes me < 1 second, which is great.

With the same presentation open, if I put that exact same code in a .xls file and run it from there, it takes 10-20 seconds. That's just not right!

[Sycdan]

Well, after some hacking, I've determined the best thing to do is have the routines that populate PPT objects located in the actual PPT file, which is a pain but I'm going to have to figure out how to make it with in my project as a whole, because the performance difference is too great to pass up:

Filling a 20x13 table with random numbers and random cell background colours, using code residing in an XLS file just took 80 seconds; identical code located in the PPT file and just called from the XLS file using PowerpointApplication.Run: 4.9 seconds.