Tag Archives: PowerPoint

Dynamic Charts in PowerPoint – Self-refreshing Charts using VBA

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

Technically in PowerPoint you can link an Excel file to a PowerPoint presentation. However that will not make the Chart dynamic, and sometimes it is hard to say when the Chart will be updated. But what if we want even more – what if we want the chart to refresh during a Presentation? I can imagine this can have many uses. Imagine wanting to present a series of dynamic slides in an Office environment, each slide showing latest charts from an updates Excel dashboard. In other cases you may want to prevent any linking out to Excel files and simply have a dynamic reference to your source file or files. Today we will explore how to create Dynamic Charts in PowerPoint using VBA.

See an example in this Youtube video:

Why not use Linked Charts?

Before we start I want to explain that you don’t need to use the approach explained below to simply link an Excel Chart to a PowerPoint presentation. If this is all you need, read this Support Office article. The purpose of this exercise is to avoid linking at all and make it entire flexible on your own preference when you want to update your Charts and even update the Excel files before doing so.

Creating Dynamic Charts in PowerPoint

Below is a step by step tutorial of how to setup the Charts and Macro:

Create the PowerPoint and Shapes

First we need to create a PowerPoint Shape that will be used as a placeholder to mark the location and size of our Chart. Click on image below for a reference.

PowerPoint with Placeholders for Shapes
Click to enlarge

Create an Excel Chart

Next we need to create and name an Excel Chart. This will be copy pasted as an Image to our PowerPoint to avoid linking and to assure the format is identical to how it is visualized in your Excel file.

Excel file with Chart to be copied to PowerPoint
Click to enlarge

Create the Dynamic Charts VBA macro

If you don’t know how to access your Developer tab you can proceed similarly as in Excel. Next create a new VBA Module and copy the following VBA code:

Let me break this down. Above first we defined the VBA Sleep function as we will use it as a delay mechanizm between updates. Next we define the CopyChartFromExcelToPPT function which basically does the following:

  • Open an Excel file defined with excelFilePath
  • Copies the chart chartName from the sheet sheetName
  • Pastes it into the ActivePresentation on slide dstSlide an the location shapeTop and shapeLeft and with the size of shapeWidth and shapeHeight

As you can see this is a generic function you can reuse to your own purpose regardless of the example usage below.

Auto refresh the Chart

Now let us create simple scenario using the generic function for copying an Excel Chart to PowerPoint:

  1. Run in Presentation mode
  2. Every second update the Excel Chart and update the TimeStamp
  3. Exit Presentation mode

Below VBA code does exactly that:

Use Cases of Dynamic PowerPoint Charts

For me a way to animate Excel Charts in PowerPoint present a new pallete of options to further push the boundries of what we can do with PowerPoint. Below a short list of ideas that can help you image how useful this can be:

  • Create a dynamic Office presentation with auto refreshing Charts from multiple Excel files
  • Create a button to manually refresh single or all Charts in your PowerPoint deck
  • Create an easy way to link your charts in PowerPoint to Excel files that might move (e.g. link to Excel files in same directory

Have more ideas? Raise a Question or let us meet on Reddit.

VBA Paste from Excel to PowerPoint

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

In this post we will explore how VBA paste from Excel to PowerPoint objects such as a Range, Chart or other element. Below you will find working code snippets. We will learn also to modify this routine to address different VBA Copy Paste from Excel to PowerPoint.

VBA Paste Range from Excel to PowerPoint

We will start with an example of VBA Paste Excel Range into PowerPoint as Picture as this is the most typical scenario. For this I created a custom function called CopyFromExcelToPPT:

What does the VBA Function do? In short you need to provide the following parameters:

  • excelFilePath – full file path to the Excel from which you want to copy a VBA Range
  • sheetName – the Sheet name from which you want to copy
  • rngCopy – the VBA Range you want to copy
  • dstSlide – the number of the slide (starting at 1) to which you want to copy the Range
  • shapeTop Optional. The Top position in pixels of the new pasted Shape
  • shapeLeftOptional. The Left position in pixels of the new pasted Shape

Let us use this function in the following scenario. We want to copy range A1:B4.
VBA Paste Excel Range to PowerPoint as Picture
Let us use our function above for this scenario

VBA Paste Chart from Excel to PowerPoint

Now an example of VBA Paste Excel Graph into PowerPoint as Picture as this is also a useful case. For this I created a custom function called CopyChartFromExcelToPPT:

Again let us use it on the example below where we want to copy a Chart from a Excel Workbook to PowerPoint:
VBA Paste Excel Chart to PowerPoint as Picture
Example execution of the VBA Function below:

If you want to place the Chart at a specific place use the shapeTop and shapeLeft arguments. The below will place the chart at 10 pixels from the Top and 100 pixels from the Left.

Changing Height / Width of pasted elements

In the examples above we didn’t change the Width and Height of the pasted Range or Chart. To do this use the adjusted functions below:

Easy PowerPoint Timer

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)
Loading...

powerpoint timerPowerPoint presentations often fail to be delivered well, due to presenters exceeding their time spent on single slides or simply. If you spend more than 5 min on a single slide you are at high risk of boring your audience to death (“death by slide”). Focusing on keeping your pace is not always an easy task, and I see that it’s good to estimate the amount of time you should spend on each slide to keep a balanced pace. How much time should I spend on this slide? Less than 1 min will often seem to fast for you audience, whereas, more than 5 min may bore your audience. It’s important to keep the momentum going. But enough of drifting into the topic of how to make proper PowerPoint presentations. Let’s focus on the topic of today’s post – an easy PowerPoint Timer that you don’t need to install.

When looking for an easy way to add a PowerPoint Timer to my deck it seemed there is a lack of any free and easy solutions. Most potentially convenient ones, like the animated progress bar and animated clock, failed as being constrained to a 1 min interval (animations are constrained to 1 min in PowerPoint).

The Easy PowerPoint Timer Example

Below a simple solution the I tend to use when needing to time my slides:

PowerPoint Timer Example
PowerPoint Timer Example

Notice the timer in the lower right hand corner? The timer will start running whenever you want – when entering the slide, on-click etc. Just like any other animation. See below how it works.
PowerPoint Timer - The Timer control
PowerPoint Timer Example

How is done? It’s a simple MP4 video embedded inside the slide!. No need of installing any add-ins, third party software or even macros!

How to use the PowerPoint Timer?

Most of my slides have a duration from 1:00-5:00 min, however, just in case I have prepared for myself a pack of 7 intervals fro;m 1:00-7:00 min. There is no real sense in timing you slides with a lower level of granularity e.g. 5:30. Just follow the steps below to easily set up your timer:

Download a ZIP with MP4 videos of timers and unzip the file

Copy intervals to slides

Depending on your desired interval copy one of the MP4 intervals to your slide.

Set the animations

Click on the Timer video and go to Animations. Next set the Animation settings as shown below to initiate the timer on entering the slide:

Timer video settings
Timer video settings

That’s it!

To save time consider preparing a slide with all desired intervals set with their animation settings. Then simply copy paste them into your final deck.

How to make your own PowerPoint Timer

As this is no rocket science and the timers are simply videos, you can consider making your own custom timers. Here is a few tips how I did mine.

Find a Timer application to record

First you need to find an timer which you can record. In this post I used the following online app:
http://www.online-stopwatch.com/countdown-timer/

However, you may considering using other timers e.g.:

Install a screen recording app

To be able to record your timer you need an application to record you screen – or in this case a section of the browser window. I personally can recommend the free version of ActivePresenter:
http://atomisystems.com/activepresenter/free-edition/

The plus side is that ActivePresenter comes at no charge and has no hidden catches like ads or the need of registering on websites etc. It is also quite easy to learn.