For Office users, the closest thing to a “programming language of the people” is VBA. It’s not too hard to get started, there are gobs of help information from a good search, and the results are immediate. But VBA’s abilities haven’t expanded as its environment has changed. This has become abundantly clear with current versions of Office, where task panes and the Windows-version Backstage haven’t been included in the VBA object model. Many want to edit OOXML with VBA, but Microsoft prefers to shuffle you off to the Open XML SDK programmed with C# to do that job.
Fortunately, we’re on the case at Brandwares. We collaborated with programmer Jan Karel Pieterse to develop a PowerPoint version of his macro set that edits Excel OOXML. We’re making this freely available as a download so you can get the benefit of this.
Let me apologize to my macOS readers. I really try to provide solutions that work cross-platform, but this macro set relies on Windows system calls.
I’ll be honest, this isn’t the most elegant OOXML editing solution. The macro set unzips the OOXML to its component files, gives you the opportunity to edit the XML using VBA string manipulation, then rezips the OOXML to a usable PowerPoint file. The unzip/rezip operations are fairly slow, especially with large files. It’s not something you can use in a real-time editing situation.
One of Brandwares’s specialties is converting legacy presentations to new themes/templates. Often, there are OOXML mismatches that make reused slides retain artifacts or formatting from the old decks. We solve these issues with macro convertors that take a folder full of old decks and transform them into new presentations with new branding. This macro set is great for that.
Edit OOXML with VBA: a Peek Under the Hood
THe file contains 3 VBA modules and 1 class module. Module modConvert is the only one you need to modify. In it, Sub MainVBAOperations does the actual work of opening files, saving as a work file, calling the XML process, saving the modified file and deleting the work file. This is also where you would do any additional VBA processing. As one example, after you modify the XML of a slide master or layout, you have to reset the slide based on it to display the changes. MainVBAOperations is where you would do this.
The other Sub is ProcessXML. Here’s where you unzip the file being modified, open different XML parts for find and replace processes, followed by a rezip of all files back to a working file. The sample code in this module shows a typical revision to the idx numbers of placeholders, a common requirement of legacy presentation conversions and one that can’t be done with the PowerPoint interface.
The module modDisplay, by Shyam Pillai, provides the PowerPoint equivalent of the Application.Screenupdating command that exists in Word VBA. Useful to prevent the screen flashing and jumping as files are processed, it also helps speed code execution. modUNC by Randy Birch, assists with file management.
Jan Karel Pieterse wrote the class module clsEditOpenXML that does the heavy lifting of unzipping and rezipping the document to be modified and reading and writing the XML.
As noted in the code, You are free to use this code within your own applications, add-ins, documents etc but you are expressly forbidden from selling or otherwise distributing this source code without prior consent. This includes both posting free demo projects made from this code as well as reproducing the code in text or html format.
Converting Legacy Presentations
We use often use this macro set to update old (legacy) presentations with a new design. Successful updating requires meeting 5 criteria, please read this article for more details: Legacy Slides – Best Practices. As noted on that page, the 5th requirement is that placeholder idx numbers in the OOXML must match on the old and new layouts. There’s nothing in PowerPoint’s interface that allows you to set idx number, but this macro set allows you to do just that. The pre- and post-processing sections of the macros allow you to set the the other 4 parameters for each slide layout. Click here to download it.
The following advice is particular to presentation conversion. It’s routine that slide masters and layouts will be changed in that process. Then, to apply those changes to the actual slides in a presentation, the slides must be reset, as if you pressed the Home>Reset button in a presentation. Resetting slides wipes out character-based formatting. If a user has applied bold or italic or an underline to particular text, that all will disappear. It’s important to notify your client of this. To make an exact update would require a painstaking construction of a multi-dimensional array for each placeholder on each slide that would record all character-based formatting, then restore it after the update, for which you would have to charge many times as much as for the basic conversion work.
Brandwares is a world leader in presentation updating and conversion. We’re available for presentation assessments, to identify potential problems. We have multiple techniques for seamless re-use of legacy presentations. Contact us when you’re redesigning to ensure your new template will reuse your old slides without a hiccup.
