PDA

View Full Version : Changing Source Links in a PPT



Eldrod
01-04-2012, 04:30 PM
I'm hoping someone already has the VBA code for this. I have a linked set of files, one Excel and one PPT with the charts in the PPT dynamically linked to the Excel file. I need to use this set sort of like a template so that I can make copies of both of these under new names and change the embedded Excel charts in the PPT to point to the new Excel copy. This way each new PPT will only be updated by the new linked Excel file, and not the original version.
I found this code to create a module under VBA, but it does not seem to work and my PowerPoint VBA skills are lacking. I do like the way it pops up the file selection box so that the new Excel file can be easily selected. Also, I'm running this on the Mac:Excel 2011 version if that matters.

Sub UpdateLinks()
Dim ExcelFile
Dim exl As Object
Set exl = CreateObject("Excel.Application")

'Open a dialog box to promt for the new source file.
ExcelFile = exl.Application.GetOpenFilename(, , "Select Excel File")

Dim i As Integer
Dim k As Integer

'Go through every slide
For i = 1 To ActivePresentation.Slides.Count
With ActivePresentation.Slides(i)
'Go through every shape on every slide
For k = 1 To .Shapes.Count
'Turn of error checking s that it doesn 't crash if the current shape doesn't already have a link
On Error Resume Next
'Set the source to be the same as teh file chosen in the opening dialog box
.Shapes(k).LinkFormat.SourceFullName = ExcelFile
If .Shapes(k).LinkFormat.SourceFullName = ExcelFile Then
'If the change was successful then also set it to update automatically
.Shapes(k).LinkFormat.AutoUpdate = ppUpdateOptionAutomatic 'other option is ppUpdateOptionManual
End If
On Error Goto 0
Next k
End With
Next i

End Sub Any help in figuring this out would be greatly appreciated!