Update Linked Excel Slide in Powerpoint Slide Show

Question

I have a Powerpoint presentation that needs to loop continuously in order to constantly display information. I want the linked Excel Worksheet object in one slide to refresh its data each time that slide is displayed, showing the updated data in the looping presentation.

How would I go about doing this?

do you have any initial code at the moment? (which is usually expected when asking here in SO). at the moment I would say it's possible to do so. But you provide too little information to help you. — Kazimierz Jawor
I don't have any code yet. This is something I am trying to set up for end-users to be able to do themselves, and so I wanted to know if there was functionality integrated into the Powerpoint GUI that would allow for this interactivity. I understand that macros will likely be necessary, but I haven't written any yet as I'm not sure the angle to approach it from for this issue. — Addison Schuhardt

Stewbob Stewbob · Accepted Answer · 2013-08-07T20:08:00

The code to do the update is just one line:

ActivePresentation.Slides(2).Shapes(1).LinkFormat.Update

You can refer to both Slides and Shapes by index number or by name. The above example updates the link for the 1st Shape object on the 2nd Slide object. You need to follow the steps below in order to make that code fire at the proper time.

From the Microsoft Office documentation:

How to: Use Events with the Application Object

To create an event handler for an event of the Application object, you need to complete the following three steps:

Declare an object variable in a class module to respond to the events.

Write the specific event procedures.

Initialize the declared object from another module.

Declare the Object Variable

Before you can write procedures for the events of the Application object, you must create a new class module and declare an object of type Application with events. For example, assume that a new class module is created and called EventClassModule. The new class module contains the following code.

VBA

 Public WithEvents App As Application

Write the Event Procedures

After the new object is declared with events, it appears in the Object list in the class module, and you can write event procedures for the new object. (When you select the new object in the Object list, the valid events for that object are listed in the Procedure list.) Select an event from the Procedure list; an empty procedure is added to the class module.

VBA

Private Sub App_SlideShowNextSlide(ByVal Wn As SlideShowWindow)
    ActivePresentation.Slides(2).Shapes(1).LinkFormat.Update
End Sub

Initializing the Declared Object

Before the procedure will run, you must connect the declared object in the class module (App in this example) with the Application object. You can do this with the following code from any module.

VBA

Dim X As New EventClassModule 
Sub InitializeApp()
   Set X.App = Application 
End Sub

Run the InitializeApp procedure. After the procedure is run, the App object in the class module points to the Microsoft Office PowerPoint Application object, and the event procedures in the class module will run when the events occur.

Update Linked Excel Slide in Powerpoint Slide Show

1 Answers