Auto updating links.

  • Thread starter Thread starter System Administrator
  • Start date Start date
S

System Administrator

I am looking for a fairly simple solution to build a Powerpoint presentation
which will, for the most part display data from linked Excel workbooks.

The source workbooks will be located on a shared server directory and
updated by multiple users (although probably only one user for each Excel
file).

The presentation will be set up to switch slides automatically on a timer,
and to loop at the end. The idea is to have the presentation run on a
seperate PC from the users handling data entry but to automaticaly update
the data displayed from the source files each time a slide is displayed.

This seems like it should be a simple task, but I haven't found the right
steps yet.

C. Newell
Shiawassee County
MI
 
I have a scheduler that runs this for me at night. Use it as a starting block
(modify it to your needs). This is probably too complex for what needs to be
done. A simpler way (I'm guessing) would be to set the Links to
Automatic...you just need a way to toggle a refresh to the .ppt. But if that
doesn't work here's this...

The following code (between the ======= ) goes in the ThisWorkbook module of
an Excel workbook (for our example let's call it "RunPPT.xls"):
=======
Private Sub Workbook_Open()
'
'This opens an existing PowerPoint (PPT) file, switches the Update Links to
Automatic, updates
'the links, then switches them back to Manual. It saves the updated PPT file
then closes the
'opened PPT and current Excel files.
'
'Note: PPT must be running when this is executed. PPT should be
'empty (no presentation loaded).

Dim PPT As Object
Dim oPPTPres As Object
Dim pptDir As String
Dim pptFileName As String
Dim pptPath As String

Application.DisplayAlerts = False
Set PPT = CreateObject("PowerPoint.Application") 'Creates the object
pptDir = ActiveWorkbook.Path & "\" 'Uses path of this workbook
pptFileName = "Daily Orders Dashboard.ppt" 'Put the name of your .ppt file
here
pptPath = pptDir & pptFileName

Set oPPTPres = PPT.Presentations.Open(pptPath, ReadOnly:=msoFalse) 'Opens the
presentation object as read/write
With oPPTPres
Call SetLinksToAutoUpdt(oPPTPres)
.UpdateLinks
Call SetLinksToManualUpdt(oPPTPres)
.Save
.Close
End With
Set oPPTPres = Nothing 'Destroy the object (free up memory)
PPT.Quit 'Exit the object
Set PPT = Nothing 'Destroy the object (free up memory)

Application.DisplayAlerts = True
Application.Quit 'Exit Excel
End Sub

Sub SetLinksToAutoUpdt(oPres As Object)
Dim sld As Object
Dim sh As Object
For Each sld In oPres.Slides
For Each sh In sld.Shapes
If sh.Type = 10 Then
sh.LinkFormat.AutoUpdate = 2 'Set links to Automatic update mode
End If
Next sh
Next sld
End Sub

Sub SetLinksToManualUpdt(oPres As Object)
Dim sld As Object
Dim sh As Object
For Each sld In oPres.Slides
For Each sh In sld.Shapes
If sh.Type = 10 Then
sh.LinkFormat.AutoUpdate = 1 'Set links to Manual update mode
End If
Next sh
Next sld
End Sub
=======

1. Have your Excel workbook that contains the data update itself. Exit the
workbook.
2. Launch PowerPoint (don't launch an existing .ppt file, you simply want a
blank presentation) i.e. C:\Program Files\Microsoft
Office\Office10\POWERPNT.EXE
3. Launch the Excel workbook that contains the above code (RunPPT.xls).
Note: It is assumed that the .ppt and .xls files are in the same directory;
if they are not they you need to modify your code to point to the correct
directories.
3a. Make sure you change the code for your filename! Find this line above
and change it:
pptFileName = "Daily Orders Dashboard.ppt" 'Put the name of your .ppt
file here


This automatically updates the links that point to the Excel source(s) in the
..ppt. Now, in the .ppt file that you have, set the Links to Manual updating
(Edit | Links...). Note: The two subroutines to toggle the Links (Manual or
Automatic) was given to me by someone else in this newsgroup.
 
Back
Top