How do I create dynamic hyperlinks in powerpoint?

Question

I am trying to link a powerpoint presentation to data in excel. However the excel and powerpoint will change locations every day.

Here is my methodology so far: - Copy from excel - Paste special... paste link... As: Microsoft Excel Worksheet (code) object

This allows me to live edit the excel document and the powerpoint updates in real time.

However, when I change the location of the excel document, all 100+ links are broken.

How do I dynamically set the path of the links so they always follow the correct excel document, no matter where the excel document is? The excel document will never name change, neither will the powerpoint.

I have been looking for a solution for over 6 months... Thank you.

Answer 1

I've got a page on my PPTFAQ site that includes code that might help:

Batch Search and Replace for Hyperlinks, OLE links, movie links and sound links http://www.pptfaq.com/FAQ00773_Batch_Search_and_Replace_for_Hyperlinks-_OLE_links-_movie_links_and_sound_links.htm

Here's the specific code ... it deals with both hyperlinks and OLE links (ie, your Excel links). You can remove the hyperlink stuff if you don't need it and pass the new path as a parameter rather than getting it from an InputBox:

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