Below is my code:
Public Sub ChangeExcelSource(ByVal sSourceOrig1, ByVal sSource1 As
String, ByVal sFileName As String)
Dim objPPT As Object 'PowerPoint.Application
Dim objPresentation As Object 'PowerPoint.Presentation
Dim I As Integer
Dim K As Integer
Dim linkname As String ' Create a variable to store the
worksheet reference string.
Dim linkpos As Integer ' Create a variable to store the
position of the worksheet
' reference in the .SourceFullName
property of the OLEFormat
' object.
'Creates an instance of PowerPoint
On Error Resume Next
Set objPPT = GetObject(, "PowerPoint.Application")
If objPPT Is Nothing Then
'Create a PowerPoint instance
Set objPPT = CreateObject("PowerPoint.Application")
If objPPT Is Nothing Then
MsgBox "PowerPoint is not Installed on your System!", vbCritical
', POWERVIEW_TITLE
Exit Sub
End If
objPPT.Visible = True
End If
'Opens the specified PowerPoint file
With objPPT
Set objPresentation = objPPT.Presentations.Open(sFileName)
End With
'*****************************
On Error GoTo 0
'*****************************
' Set a For loop to go from slide 1 to the last slide in the
' presentation.
For I = 1 To objPresentation.Slides.Count
' Select the slide based on the index value.
With objPresentation.Slides(I)
objPresentation.Application.ActiveWindow.ViewType =
ppViewSlideSorter
objPresentation.Slides.Range(Array(I)).Select
objPresentation.Application.ActiveWindow.ViewType = ppViewSlide
' Loop through all the objects on slide.
For K = 1 To objPresentation.Slides(I).Shapes.Count
' Use only shapes on the slide.
With .Shapes(K)
objPresentation.Application.ActiveWindow.Selection.SlideRange.Shapes(K).Select
' If the shape's type is an OLE object then...
If objPresentation.Slides(I).Shapes(K).Type = 7 Then
'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,
objPresentation.Slides(I).Shapes(K).LinkFormat.SourceFullName, "!", _
vbTextCompare)
' Assign linkname to worksheet reference at the
' end of the source file path.
linkname = Right(.SourceFullName, _
Len(.SourceFullName) - linkpos)
If .SourceFullName = sSourceOrig1 & linkname Then
.SourceFullName = sSource1 & linkname
.AutoUpdate = ppUpdateOptionAutomatic
End If
End With
End If
objPresentation.Application.ActiveWindow.Selection.Unselect
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.
objPresentation.UpdateLinks
objPresentation.Save
objPresentation.Close
'Exit PPT if no more presentations
If objPPT.Presentations.Count = 0 Then objPPT.Quit
'Free Memory
Set objPresentation = Nothing
Set objPPT = Nothing
End Sub
The line that does not work is:
linkpos = InStr(1,
objPresentation.Slides(I).Shapes(K).LinkFormat.SourceFullName, "!", _
vbTextCompare)
