Changing Source Links in a PPT

[Eldrod]

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!

 

[dnortje23]

Hi Elrod,

Just saw your original post and was wondering if you ever figured it out? Am currently stuck in exactly the same predicament...

Thanks!