Something which has bugged me for years is automation of PowerPoint creation. Unlike Word and Excel which can be created with HTML. PowerPoint just sucks when it comes to formatting. This week I completed my 6 years journey on repeatedly trying to automate the creation of PowerPoint slides.

VBA has always been something I have liked to play with because it generates a LOT of functionality customers expect from their applications. I have used the user’s locally installed MS Word to function as a spell checker. I have used Excel through the web and MS Excel client to create graphs and pivot tables.

I have a customer who semi-annually inputs all their project information into a Notes database. By entering into into a web based application it exposes the information to other internal customers who can then edit it for accuracy.

This amounts to around 150 projects and each of these projects has to be briefed at least once to the senior leadership using a standard briefing template. This is currently a copy and paste job and wastes a significant amount of man hours.

My problem – automate the brief creation from the information in the database. I tried the following.
Knowing that Office files can be saved as HTML files; I tried to save the brief as HTML and then try to create the HTML using a LS agent. Unfortunately when PowerPoint saves as a web page it generates a multude of files including macro files. Creating all these files is not practical, time consuming on the server and all round.

Using Office 2003 you can record a Macro. Which allows me to record a VBA version of what I do to the template. I tried to build a template from scratch using the macro recorder. This provided the means but generated a significant amount of VBA code which would take me eons to build the 20+ pages necessary and then I would still have to convert to something usable.

Finally I basically compromised and came up with a solution which does require a modicum of interaction from the user – but still saves them hours of work. The user has to download the template to their machine, using a File Upload Control tell me where it is, and I will load it using vbScript, and fill it from the database. This is not total automation but sometimes an 80% solution is a whole lot better than 0% !

Here is an example article