Sycdan
08-02-2010, 12:08 PM
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.
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.