CODEWITHSUNDEEP
excelpowerpointpowerpoint-2016
Marco
Marco

Reputation: 49

ThinkCell change link Excel of the chart Power Point

I' have many bar charts ThinkCell in a Power Point, these ones are linked to different tables of the same Excel. Now, I need to duplicate this Power Point but linked to another Excel file with the same structure (same sheets, same tables in the same positions but different values). I would like to change the path of the source Excel in this second PPTX. So as I don't have to remove link and link again all charts one by one.

Does Someone of you know how to execute this task with or without VBA? There is a way?

Thank You

Upvotes: 1

Views: 2748

Answers (1)

Steve Rindsberg
Steve Rindsberg

Reputation: 14809

For more detailed information, have a look at this page on the FAQ site I maintain about PowerPoint: https://www.rdpslides.com/pptfaq/FAQ00773_Batch_Search_and_Replace_for_Hyperlinks-_OLE_links-_movie_links_and_sound_links.htm

But here's the VBA that will help, unless there's something odd about ThinkCell links:

Sub HyperLinkSearchReplace()

    Dim oSl As Slide
    Dim oHl As Hyperlink
    Dim sSearchFor As String
    Dim sReplaceWith As String
    Dim oSh As Shape

    sSearchFor = InputBox("What text should I search for?", "Search for ...")
    If sSearchFor = "" Then
        Exit Sub
    End If

    sReplaceWith = InputBox("What text should I replace" & vbCrLf _
        & sSearchFor & vbCrLf _
        & "with?", "Replace with ...")
    If sReplaceWith = "" Then
        Exit Sub
    End If

    On Error Resume Next

    For Each oSl In ActivePresentation.Slides

        For Each oHl In oSl.Hyperlinks
            oHl.Address = Replace(oHl.Address, sSearchFor, sReplaceWith)
            oHl.SubAddress = Replace(oHl.SubAddress, sSearchFor, sReplaceWith)
        Next    ' hyperlink

        ' and thanks to several astute user suggestions, let's fix OLE links 
        ' and movie/sound linkes too
        For Each oSh In oSl.Shapes
           If oSh.Type = msoLinkedOLEObject _
            Or oSh.Type = msoMedia Then
              oSh.LinkFormat.SourceFullName = _
                   Replace(oSh.LinkFormat.SourceFullName, _
                   sSearchFor, sReplaceWith)
           End If
       Next

    Next    ' slide

End Sub

Upvotes: 2

Related Questions