Excel VBA to change link source in powerpoint linked objects

[mmohon]

I have this code I've been using for a few years. It opens a powerpoint and changes the source of all linked objects in the powerpoint file back to the excel file the code was executed from.

It's great for juggling multiple templates, it falls short in one instance though. It's great for links to a range of cells, but when it tries to change the source for a chart object it ends up breaking the chart link.

Is there anyone that knows a better way to accomplish this?

Sub ChangeExcelSource()

   Dim i As Integer
   Dim k As Integer
   Dim ThisFile As String
   Dim PPTFile As String
   Dim PPApp As PowerPoint.Application
   Dim PPPres As PowerPoint.Presentation
   Dim PPSlide As PowerPoint.Slide

   ThisFile = ActiveWorkbook.FullName
   
    PPTFile = Application.GetOpenFilename
    ' Create instance of PowerPoint
    
    
    Set PPApp = CreateObject("PowerPoint.Application")
  

    ' For automation to work, PowerPoint must be visible
    ' (alternatively, other extraordinary measures must be taken)
    PPApp.Visible = True

    ' Create a presentation
    
    PPApp.Presentations.Open Filename:=PPTFile
    Set PPPres = PPApp.ActivePresentation

   
   ' Create a variable to store the worksheet reference string.
   Dim linkname As String

   ' Create a variable to store the position of the worksheet
   ' reference in the .SourceFullName property of the OLEFormat
   ' object.
   Dim linkpos As Integer

   ' Set a For loop to go from slide 1 to the last slide in the
   ' presentation.
   For i = 1 To PPPres.Slides.Count

      ' Select the slide based on the index value.
      With PPPres.Slides(i)

         ' Loop through all the objects on slide.
         For k = 1 To .Shapes.Count

            ' Use only shapes on the slide.
            With .Shapes(k)

               ' If the shape's type is an OLE object then...
               If .Type = msoLinkedOLEObject Then

                 ' Change the path to new source and set the update
                 ' type to Automatic. First find where the worksheet
                 ' reference is, and then store it in linkname. Next
                 ' assign the new path and concatenate the chart name
                 ' to the end of the path.
                 With .LinkFormat

                 ' Find where in the source path string the
                 ' character "!" occurs, and assign the position
                 ' to the variable linkpos.
                 linkpos = InStr(1, .SourceFullName, "!", _
                    vbTextCompare)

                 ' Assign linkname to worksheet reference at the
                 ' end of the source file path.
                 linkname = Right(.SourceFullName, _
                    Len(.SourceFullName) - linkpos)
                 .SourceFullName = ThisFile & "!" & linkname
                    .AutoUpdate = ppUpdateOptionAutomatic
                 End With

               End If

            End With

         Next k

      End With

   Next i

   ' Update all links in the presentation, so that the changes are
   ' visible and the source file locations are correct on the screen.
   PPPres.UpdateLinks
   End Sub

 

[Bean44]

Well.. I guess you can do this:

The charts don't have the whole "C:\Users"... in the filename.. so you can tell it to change only the links that do contain this by making this change (there's probably an easier way thank this, too):

With .LinkFormat

If InStr(.SourceFullName, ":\") = 2 Then

linkpos = InStr(1, .SourceFullName, "!", _
vbTextCompare)


linkname = Right(.SourceFullName, _
Len(.SourceFullName) - linkpos)
.SourceFullName = ThisFile & "!" & linkname
.AutoUpdate = ppUpdateOptionAutomatic

End If
End With