CODEWITHSUNDEEP
excelvbavbe
C111
C111

Reputation: 1

Macro to update linked binary worksheet object in PowerPoint

My company uses a PowerPoint presentation that has linked Excel charts (namely, linked binary worksheet objects). Each month, the name of the workbook changes (from Feb to Mar, for instance) and the name of the folder the sheet is saved in changes as well. There are 40 links to update.

Does anyone know of a VBA script/macro to batch update the name of the links in PowerPoint? Currently, each link has to be updated individually.

When I run a script, nothing happens and no links update. The PowerPoint is macro-enabled.

I've tried a few different scripts, such as:

Sub switch()
    Dim osld As Slide
    Dim oshp As Shape
    Dim oldPath As String
    Dim newPath As String
    Dim strLink As String

    oldPath = "C:\Users\FAKE FILE NAME 1.xlsx\"
    newPath = "C:\Users\FAKE FILE NAME 2.xlsx\"

    For Each osld In ActivePresentation.Slides
        For Each oshp In osld.Shapes
            If oshp.HasChart Then
                If oshp.LinkFormat.SourceFullName <> "FAKE SOURCE NAME" Then
                    strLink = oshp.LinkFormat.SourceFullName
                    oshp.LinkFormat.SourceFullName = Replace(strLink, oldPath, newPath)
                    Debug.Print oshp.LinkFormat.SourceFullName
                    oshp.LinkFormat.AutoUpdate = ppUpdateOptionAutomatic
                    oshp.LinkFormat.Update
                End If
            End If
        Next oshp
    Next osld
End Sub

When I run this, nothing happens. Thanks in advance for your help.

Upvotes: 0

Views: 165

Answers (0)

Related Questions