Keep Paste Special Links Contained within a Copy/Pasted and Renamed Folder

Question

I have links from Excel Charts to Powerpoint slides, using the Paste Special Link option, so that when the Powerpoint is opened the charts can be updated easily. The Powerpoint and Excel files are all contained in the same folder, but the information needs to be updated monthly. My company wants to create a new folder for each month so there's an archive of information for previous months.

I thought there was a way to copy/paste the folder with the links and rename it while keeping the links contained to the newly renamed folder, since everything is all together and there are no links that point outside of the folder. However, when I check the PowerPoint in the new folder the links still all point to the Excel files in the original folder. So the links go from pointing to files within the folder to pointing to files outside of the folder.

I know I can edit the links to point to the new folder, and it only takes about 10 minutes in this instance because there are so few of them, but I want to know if there is a way to keep the links contained automatically.

The first time I tried to copy/paste/rename this morning I had just recently replaced a small number of files in the original folder by saving over them with newer copies I'd emailed to myself from home. When I looked at the PowerPoint in the newly renamed folder that time, those specific files had updated their paths to match the new folder, but the others hadn't. I went through and re-linked all of the charts, including the ones from the overwritten files, in the old folder, then repeated the copy/paste/rename. I checked the PP in this new folder and all links pointed to the old folder and none pointed to the new one. Not sure if it was a fluke that those select few updated their links or not.

I've tried to find the answer elsewhere, but can't seem to find anything that gives a good "No, it's not possible" or "Here's how you do it" answer.

Ideas? Suggestions? Solutions? Or am I just going to have to manually update the links every time a new folder is created?

Answer 1

Another approach:

Instead of copy/pasting to a new folder each time there's an update, copy the updated files INTO the same folder each time:

January: Put your Excel file and PPT file into the \CurrentMonth folder. Create links as needed.

February: Copy the Excel file from \CurrentMonth into \January then copy the new Excel file into \CurrentMonth, overwriting the old Excel file.

And so on for each month. Change the directory names to whatever's appropriate.

Answer 2

There is no way using copy and paste. But as a workaround, you can use

The easiest solution: use relative path in hyperlinks

If you have your files in the same folder, for example:

Monthly Report Calculation.xlsx
Monthly Report.pptx

Then you can link between PPTX and XLSX in both directions if you use relative paths.

In PowerPoint, be sure that you create link to Monthly Report Calculation.xlsx (which basically is .\Monthly Report Calculation.xlsx, a path relative your PPTX file current directory) and not to D:\Data\Monthly Report Calculation.xlsx (which is absolute path).

In Excel, you can use the following formula inside your Monthly Report Calculation.xlsx:

=HYPERLINK("Monthly Report.pptx")

Or also with friendly display name:

=HYPERLINK("Monthly Report.pptx", "Monthly Report")

Now you can copy your files anywhere you want and the links from PowerPoint to Excel and back will work in any directory where these files stay together.

Mind the path I shown in Excel formula was entered as relative, so you can traverse across directories if you need:

=HYPERLINK("..\Presentations\Monthly Report.pptx", "Monthly Report")

The same directory referencing works in Hyperlink box in PowerPoint. Tested.

---

Another possibility is to create custom VBA code to update hyperlinks and launch it every month. If you check one of the answers, you should get the idea.

One approach or another, do not expect specific functionalities out of the box. You need either to adjust the process on your side (do it different way) or create a code to perform tasks specific for your environment and processes.

Answer 3

This webpage gives a search-and-replace macro for PowerPoint. I haven't tested this, but the solution from that page follows:

Solution

This macro will ask you what text you want to search for and what you want to replace it with. Then it will look at each hyperlink in your presentation and do the search/replace on both the hyperlink Address and Subaddress.

Here's the code:

Option Explicit

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

        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

Limitations

• The macro only works in PowerPoint 2000 or greater. It won't run in PowerPoint 97 (because of the Replace command, which isn't supported in 97).
• The macro only works on hyperlinks and OLE links, not action settings. But some action settings (most of the Link To ones) are hyperlinks as well, so links to other files, whether hyperlinks or action settings, will generally work with this macro.

See How do I use VBA code in PowerPoint? to learn how to use this example code.