I got an error but it was because of this:
PPT.Presentations.Open Filename:=pptPath 'Loads the already existing (specific) object
So I removed that line from my [original] code.
Then everything worked except when it was time to save the updated presentation. In the With...End With the ".Save" stops execution with the following error, "Presentation.Save: This presentation is read-only and must be saved with a different name." followed by the "End" and "Debug" buttons.
After looking at the PPT Help file for Open, I changed this line
Set oPPTPres = PPT.Presentations.Open(pptPath, , , False)
to this
Set oPPTPres = PPT.Presentations.Open(pptPath, ReadOnly:=msoFalse)
Tested...and...IT WORKS!
Shyam, my man, you ROCK!! Basically what you've shown me is that I can remove a simple macro from one application and run it in another
Plus you've given me a fine example using objects; an area where I need to improve my skills.
Steve, thanks for your help as well, it made me play with the code and think of other possibilities.
Here is the code I'm using...feel free to use it for any FAQ or hints/help pages y'all may have:
---------------
Private Sub Workbook_Open()
'Note: PowerPoint (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 = "Y:\Private-Employees\Dashboard Project\" 'UNC can be used as well
pptFileName = "Daily Orders Dashboard.ppt"
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
---------------
Shyam Pillai said:
I am assuming that the SetLinksToManualUpdt code is identical to the one on
the following page?
http://www.mvps.org/skp/ppt00029.htm. Copy the two routines below into your
excel code module and modify your exisiting code as show below:
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
' Set the link to automatic update mode
sh.LinkFormat.AutoUpdate = 2
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
' Set the link to manual update mode
sh.LinkFormat.AutoUpdate = 1
End If
Next sh
Next sld
End Sub
Dim PPT As Object
Set PPT = CreateObject("PowerPoint.Application") 'Creates the object
pptDir = "C:\Projects\Automate\" '*
pptFileName = "Daily Orders Dashboard-test.ppt" '*
pptPath = pptDir & pptFileName
Dim oPPTPres As Object
Set oPPTPres = PPT.Presentations.Open(pptPath, , , False)
With oPPTPres
Call SetLinksToAutoUpdt(oPPTPres) ' new line
.UpdateLinks
Call SetLinksToManualUpdt(oPPTPres) 'new line
.Save
.Close
End With
Set oPPTPres = Nothing
PPT.Quit 'Exit the object
'Macro is done so quit PPT
Set PPT = Nothing 'Destroy the object (free up memory)
Application.Quit 'Exit Excel
....
