1

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

3 Answers 3

1

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
0

I think you need to copy and paste the charts from Excel to Powerpoint in a specific way. The 2nd option from Step 5 in the guide from Microsoft says to paste special and it will include access to the whole workbook.

https://support.office.com/en-US/Article/Copy-Excel-data-or-charts-to-PowerPoint-f5f84f7b-b566-47fa-8c67-c95b8d14e7a3?ui=en-US&rs=en-US&ad=US#bmpastechartpowerpoint

2
  • 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.
    – S Jacobs
    Oct 3, 2014 at 23:23
  • Ah that makes sense. I've only tried doing this with one chart not multiple.
    – Gilbert
    Oct 7, 2014 at 13:15
0

If you start with the Excel files and the PPT files in the same directory and THEN link them, then depending on the version of PPT at the receiving end, everything may work ok if the PPT and XL files stay in the same directory together, regardless of what directory that is. Other versions won't work this way. 2010 seems to.

Other than this one exception, links to XL and other OLE content must be fully pathed; relative pathing won't work.

This is one of those unfortunate situations where both linking and embedding have advantages and disadvantages but you can't choose which of each you want.

1
  • Thank you for the suggestion. I am working with 2010, however unfortunately it doesn't seem to work for me. The two files were in the same folder initially and have been kept together the whole time.
    – S Jacobs
    Oct 9, 2014 at 16:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.