ThinkCell 更改图表 PowerPoint 中的 Excel 链接

huangapple go评论127阅读模式
英文:

ThinkCell change link Excel of the chart Power Point

问题

I have many bar charts in 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.

英文:

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

答案1

得分: 2

以下是您要翻译的内容:

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
英文:

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

huangapple
  • 本文由 发表于 2023年6月1日 18:32:46
  • 转载请务必保留本文链接:https://go.coder-hub.com/76380993.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定