Auto update / recalculate Excel links in PowerPoint

I'm using PowerPoint 2007 and I have a link to an Excel Spreadsheet.  This sheet "updates" whenever the date changes (calculations based on TODAY() ) . I need code to make the PowerPoint presentation automatically update whenever the date (or in some cases, the time) changes.
How can this be done?  So far, I can get it to 'work' with a 'fly-in' transition, if the mouse is appropriately located (so a 'mouseover' macro triggers), but this is an inconsistent workaround. There should be a way to incorporate a VBA 'ActivePresentation.UpdateLinks' command each time a slide reappears.  Isn't there?

help  :)
thasgolasAsked:
Who is Participating?
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Have you inserted all the code in the ppt file?

Alt + F11 for the VBE
Insert Module:
Option Explicit
Dim X As New class1 ' Where class1 is the calss module inserted above
Sub InitializeApp()
    Set X.App = Application
End Sub

Insert CLass Module
Public WithEvents App As Application

Then return to the PPT and use alt + F8 to run the InitializeApp sub.

Back to the VBE class module:Double click on teh class module, (expand the vbaproject if necessary).  Select app in teh drop down and the event you want to use i.e. App_SlideShowNextSlide and insert your code i.e.
    'ActivePresentation.UpdateLinks'

Chris
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
0
 
thasgolasAuthor Commented:
PowerPoint 2007 does not seem to allow me to use that.  I tried and, most on th etime, it would not even Add this add-in.  When I dd manage to get it to install, it would not load.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Chris BottomleySoftware Quality Lead EngineerCommented:
Add a class:

In the VBE:

1. Insert class module:
Add contents:
Public WithEvents App As Application

2. Insert normal module:
Add contents:
Option Explicit
Dim X As New class1 ' Where class1 is the calss module inserted above
Sub InitializeApp()
    Set X.App = Application
End Sub
Run this sub

In teh class module:
Select App and then the event you want to trigger your code.

Note InitializeApp needs to be run before the events will work.

Chris
0
 
thasgolasAuthor Commented:
Ok. Sounds good, but I'm a beginner at the VBE stuff.
Got a little confused as to how to run the app.
0
 
thasgolasAuthor Commented:
Thanks. That worked, only oddly, it took two rounds of slide changes for the
change to display - though, if consistent, that's more than good enough!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.