As a reporting analyst, part of my responsibilities involved compiling the monthly review slide deck for senior management
Whilst it was not a difficult task, it was a pretty boring and time consuming one.
Every month, I had to to compile 10 decks, each consisting of 70 slides, which had to be updated to show the figures for the past month.
Linking Excel to PowerPoint was not an option because of the resulting image resolution. Getting a screenshot of the relevant numbers as an Enhanced Metafile image, however, worked much better resolution-wise.
Therefore, my first step was to develop a list of VBA procedures that would export all the Excel figures I needed as Enhanced Metafile images and place them in interim slide decks
Having these in place meant that all I needed to do was compile all the slides from the interim decks into a presentation that followed a certain structure. The easiest way to do this would be to:
(a) develop the structure as a template
(b) come up with a way that would automatically place the latest slides in their respective sections
That’s how I came up with the following snippet.
Every time the code runs, it pops up a input box message asking you to indicate which section of the PowerPoint you would like to update
Depending on your input, it will then:
(1) open the relevant interim PowerPoint,
(2) take the slides associated with the region stated in the title of the slide
(3) and paste them immediately after the section header slide
Finally, it will check if the presentation had all the necessary slides and call a separate saving procedure if this was the case.