We were recently asked by one our clients to update an Excel VBA based tool to create Powerpoint reports from Excel data.  It reminded me just how powerful creating Powerpoint presentations with Excel VBA can be.  This customer has created THOUSANDS of Powerpoint presentations using the tool we made for them, all at the click of a button.  It’s insane how much time and money they have saved – all with the power of Excel automation! 

In this session, I share the secrets of this Excel-based application.  We go through the automation step-by-step, with download files available containing the VBA code for you to adapt to your purposes.  If you want to say goodbye to manual Powerpoint and harness the power of Excel VBA to create attractive and dynamic presentations, stick around. 

EXCEL DOWNLOAD FILE 

POWERPOINT DOWNLOAD FILE 

30 DAY EXCEL ANALYST

WATCH THE VIDEO Here

00:00 INTRODUCTION

00:45 STEP 1: PREPARE DATA

In our example, we’ll create a presentation about video performance on Tiger’s YouTube channel.  The first step is to ensure your data is good shape and create the analysis you need in Excel, including charts.  Never created a chart before?  Don’t worry – we’ve got you covered with Excel beginner chart videos right here on the channel (see links below). 

I recommend some kind of ‘dynamic’ mechanism here.  In our example, when the input cell for the video name changes, the analysis automatically updates.  So, there is ‘dynamic’ quality.  We want to do the ‘heavy lifting’ in Excel, not Powerpoint.  This helps the magic happen later … 

01:53 STEP 2: PREPARE POWERPOINT TEMPLATE

Next, prepare a Powerpoint template.  To keep things simple, we’re not dealing with slide creation in this session, though that too is possible with Excel VBA.  You’ll need a Powerpoint presentation with a cover sheet, and the number of slides you need – in our case we’ll create a presentation for three videos.  So, four slides in total. 

The benefit of the ‘template’ approach is beautifully presented Powerpoint slides, since you can integrate any visual features you choose.  Include generic elements such as titles, subtitles and company logos at this point.  For our example, we add a textbox to display the ‘mini report’ which is the main output of the analysis. 

Then, take the time to access the Powerpoint selection pane, as I demonstrate in the session.  Understanding how many objects are on each slide, and their names, will help us with the coding later. 

03:34 STEP 3: GET EXCEL TO ‘SEE’ POWERPOINT

The next step is to open the VBA editor and enable what’s called the ‘Powerpoint object library’.  This allows Excel to ‘talk’ to Powerpoint, facilitating the automation.  I show you how to do it in the session.

Next, staying in the VBA editor, it’s time to get Excel and Powerpoint to interact.  This can be tricky, involving some unusual coding syntax, and is where most people give up!  I share the exact code you need to help Excel i) understand Powerpoint is open and ii) identify the Powerpoint file it needs to work with. 

05:28 STEP 4: POPULATE POWERPOINT TEXTBOX FROM EXCEL

Now Excel and Powerpoint are ‘talking’, it’s time to get data moving between them.  The first challenge is to display a value from a cell in Excel in a Powerpoint textbox.  I share the exact syntax to do this in the session.  A punch-the-air moment!  But there’s so much more … 

07:21 STEP 5: COPY EXCEL CHART TO POWERPOINT

Now we can transfer text from Excel to Powerpoint, can we transfer a chart, too?  The VBA required, once again, is tricky, and which version works depends on your version of Office.  I share the solution for the latest version of Office, and a possible alternative, in the session.  Beautiful charts displaying in Powerpoint at the click of a button, cool! 

At this point, let’s take the time to optimise the appearance of the slide.  I show you how to delete unwanted charts, saving on manual work, and how to perfect the position and size of the chart.  Yes, there’s no reason not to make this presentation beautiful, your customer doesn’t know you created it at the click of a button 😊 

09:48 STEP 6: INTEGRATE A VBA LOOP

Now, this is where the magic really happens!  Harnessing the power of loops in Excel VBA, we can repeat the above actions (populating a textbox, copy / pasting a chart) any number times.  I show you how to easily scale up the code by integrating a For / Next loop and a module-level variable to bring things together.  Now it’s time to punch the air! 

FOLLOW-UP LINKS

EXCEL VBA FOR BEGINNERS 

CAN'T DO DATA ANALYSIS IN EXCEL? JUST DO THIS 

THIS FORMULA COUNTS 

EXCEL CHARTS FOR BEGINNERS