Can you change linked charts to embedded charts in PowerPoint 2010? Or use relative links to Excel files?

Question

I need to replicate a PowerPoint presentation across multiple countries then send the presentations to colleagues and give them the ability to edit charts.

To do this I have created an Excel file that contains data for all countries and a datasheet that uses vlookups to pull through the data for one country at a time. I then have a PowerPoint presentation that contains approximately 30 charts that are linked to the datasheet. To create a country presentation I save a copy of the PowerPoint presentation and the Excel file - one copy for each country - and then change the country on the data sheet so the presentation updates (after updating the links in the file using a macro).

The problem I have is that if I just send the PowerPoint file to colleagues they cannot edit the charts. If I also send the Excel file they still cannot edit the charts without changing the links to the location they save the file in - which is not a workable solution.

I've searched for a solution but to no avail.

I would be grateful if anyone could provide a way to implement one of the solutions below using a macro - or alternatively suggest another one I haven't thought of:

• Change all linked charts in the presentation to embedded charts
• Change the absolute links in PowerPoint to relative links so that as long as the Excel file is saved in the same folder as the presentation it will be possible to edit the charts

Thank you!!

Simon

Solution

This code may help. But you need to have the chart pasted in PowerPoint as a Linked OLE Object.

Option Explicit

Sub HyperLinkSearchReplace()

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

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

    sReplaceWith = InputBox("What text should I replace it with" & 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 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

Comments

[S Jacobs]

Thank you for the suggestion. I've tried pasting the charts with the workbook embedded however the problem is that it embeds the whole workbook each time you add a chart. So if you have 20 charts you have the workbook embedded 20 times which makes the PowerPoint file really large. It also makes updating the charts really tedious as you can't simply paste over 1 embedded workbook and update all charts. You'd have to do it for each chart separately which defeats the purpose of having all the data on one sheet.

[Gilbert]

Ah that makes sense. I've only tried doing this with one chart not multiple.