英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论