Replacing Linked Objects Source

[gdel02]

I've been using this code for a while now with some bugs every now and then:

Sub UpdateSheet()    Dim sld As Slide
    Dim sh As Shape
    Dim strNms As String
    Dim intI As Integer
    Dim strNewPath
    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")
    For Each sld In ActivePresentation.Slides
        For Each sh In sld.Shapes
            If sh.Type = msoLinkedOLEObject Then
                With sh.LinkFormat
                    strNms = .SourceFullName
                    intI = InStr(1, strNms, "!")
                    strNewPath = ExcelFile & Mid(strNms, intI, Len(strNms) - intI + 1)
                    .SourceFullName = strNewPath
                End With
            End If
        Next sh
    Next sld
    ActivePresentation.UpdateLinks
End Sub

The problem is, linked objects also contain a section within brackets, for example: [MyDocument.xlsm]Chart 1.
The code above, changes the string all up until !, but does not change the section within brackets also.

Any recommendations on how to loop for that section or any better code?

Thanks in advance!

 

[Sabina Telenska]

Hello i wanted to ask if you were able to solve this problem, i having same issue and when updating links only part gets updated and the section within brackets stays unchanged. Thank you very much