Link to home
Start Free TrialLog in
Avatar of J Zee
J Zee

asked on

Linked excel chart in PPT for export to other users

Hello I am trying to keep in place or make it possible for my Excel chart to remain linked in my PowerPoint file. I have tested this with a download of a zip file and when I open the PowerPoint there are no links ... as far as linked documents or linked charts.
 
 The way I set this up was to simply copy and paste the charts into my PowerPoint file.

This works fine locally, but when packaging for other users ... not so much. I have seen some talk about using a macro but I am trying to avoid this.
 
 Of course the plan is to have the files extracted into the same folder. If the charts where link I could at least offer steps for the user to link the files locally.
 
 As I have stated currently the link doesn't appear when I look in Powerpoint. The charts are there but the link is not.

I'm wondering if adding the charts from within PowerPoint instead of copying and pasting with the links which again work properly for me locally ... but I'm wondering if I do an embed from within PowerPoint if that will work better for zipping up in extracting with the links In-Place possibly.

Any feedback you can offer while I troubleshoot is greatly appreciated. If i must use a macro, please advise with specifics. Thank you!
ASKER CERTIFIED SOLUTION
Avatar of Davis McCarn
Davis McCarn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Where are you checking for the linked files? If you've copied and pasted a chart, it will be linked by default. You'll see the link in File | Info at the bottom right -- there will be an "edit links to files" option there.

But if you're expecting the chart data to be included in the file, then you need to embed the chart when you paste. Copy the chart and then right-click on the slide and choose the appropriate past option there to either link or embed when you're adding the chart.
As Davis and Echo have both mentioned, there are two ways of getting an editable chart into your PowerPoint file:

1. Embedded - the Excel file and all of its data travel inside the PowerPoint file (take care with this as I've seen many people accidentilly share confidential data in hidden rows, cells, sheets!)
2. Linked - the Excel file stays outside of the PowerPoint file and optionally, the chart is automatically updated when the data in the Excel file changes, as long as the file is accessible.

If you want to distribute a PowerPoint file with a link or links to external Excel data, you have to make sure that:

(a) the linked Excel file is distributed along your PowerPoint file
(b) the linked Excel file is saved in EXACTLY the same location as defined by the link

Note that links to charts use absolute paths and not referenced paths. For example, let's say I have a file here:

C:\Users\MyName\Documents\Test.xlsx

The link is made to the full path starting C:\ (or a server name if not local) as opposed to simply "\Test.xlsx" and because the "MyName" part will be unique to you, sharing your file using such a link WILL require VBA macros to deploy it correctly. This is probably why your ZIP file approach isn't working because you have no control over where the user will unzip the two files and hence the link(s) will effectively be broken. Taking the example above, if they unzip it here:

C:\Temp\Downloads

You can see that the link to C:\Users\MyName\Documents\Test.xlsx will not be available since the linked file is [incorrectly] located at C:\Users\MyName\Documents\Test.xlsx

With this in mind, if you want the ability to send the PowerPoint file to multiple users and have them see updates to your Excel file each time they open it you need to link your Excel file from a static shared location that all recipients of your PowerPoint file have access to. This might be a corporate server path e.g. \\myserver\ExcelFiles\Test.xlsx or SharePoint or a OneDrive location or any other cloud based services.

But, the approach you're currently taking of sending the Excel file in a zip file implies that there is no need to link to the file since the data cannot change in that scenario so in that case, you should simply embed the file or to avoid the previous confidentiality risk, embed a picture of your chart.
To build on Jamie's excellent explanation, you can use Package for CD to resolve those links and package the PPTX and the Excel file together to send. You can ignore the "for CD" part of that feature and consider it more a "package presentation" option instead. (Besides, who uses CDs anymore?!)

Choose File | Export | Package Presentation for CD. Click the Package for CD button on the right.

In that dialog, your PPTX file will be listed. Your linked files will be included by default but not listed in the "files to be copied" area. You can check on these by clicking the Options button and ensuring that "linked files" is checked.

Then click Copy to Folder and OK through the prompts.

Now go check that folder. You can delete the autorun.inf and the PresentationPackage folder, which is basically an HTML interface that you don't need here. Make sure the PPTX and XLSX files are in the main folder, zip it up and send it on.

If you double-click the PPTX file and check File | Info | Edit Links, you'll see that the path to the Excel file has been stripped, so when the user unzips the file, the links should be intact. Be sure to test it, of course....
Ahhhh. Ooooh. You always teach me something new Echo. So, when PowerPoint saves as a CD package, "all it does" is put all the PowerPoint and linked Excel files in the same folder and swap the chart links from absolute to reference format. For example, a link to this file:

C:\Users\MyName\Documents\Test.xlsx

Becomes:

Test.xlsx

That means the whole process could be encompassed in a macro, assuming the user keeps the PowerPoint file and files to be linked, in the same folder BEFORE they are linked. This ChangeChartLinksToRelative macro will then change the absolute links for relative ones:


Option Explicit

' PowerPoint Macro to change paths for linked Excel charts from absolute to relative
' Written by : Jamie Garroch at YOUpresent Ltd. (http://youpresent.co.uk)
' Date : 07MAR2017
' References : None
Public Sub ChangeChartLinksToRelative()
  Dim oSld As Slide
  Dim oShp As Shape
  Dim counter As Long
  Dim strCharts As String
  
  For Each oSld In ActivePresentation.Slides
    For Each oShp In oSld.Shapes
      Select Case oShp.Type
        Case msoChart
          If ChangeLink(oShp) Then
            counter = counter + 1
            If counter <= 10 Then strCharts = strCharts & vbCrLf & "Slide " & oSld.SlideIndex & ", " & oShp.Name
          End If
        Case msoPlaceholder
          If oShp.PlaceholderFormat.ContainedType = msoChart Then
            If ChangeLink(oShp) Then
              counter = counter + 1
              If counter <= 10 Then strCharts = strCharts & vbCrLf & "Slide " & oSld.SlideIndex & ", " & oShp.Name
            End If
          End If
      End Select
    Next
  Next
  
  ' Clean up
  Set oSld = Nothing
  Set oShp = Nothing
  
  MsgBox counter & " chart" & IIf(counter = 1, " was", "s were") & " updated with relative links." & _
          IIf(counter > 0, vbCrLf & vbCrLf & "Here are the first few:" & vbCrLf & strCharts, ""), _
          vbInformation + vbOKOnly, "Chart Links Changed"
End Sub

' Supporting function to change the path for a linked chart
Private Function ChangeLink(oCht As Shape) As Boolean
  Dim strOldPath As String, strNewPath As String
  Dim lSlash As Long
  
  ' Get the link path and if none exists, quit
  On Error Resume Next
  strOldPath = oCht.LinkFormat.SourceFullName
  If Err Then GoTo quit
  
  ' Find the last back slash or forward slash
  Select Case True
    Case InStr(1, strOldPath, "\") > 0
      lSlash = InStrRev(strOldPath, "\")
    Case InStr(1, strOldPath, "/") > 0
      lSlash = InStrRev(strOldPath, "/")
  End Select
  
  If lSlash = 0 Then Exit Function

  ' Create and set the link from absolute to relative by stripping off the path
  strNewPath = Mid(strOldPath, lSlash + 1, Len(strOldPath) - lSlash)
  oCht.LinkFormat.SourceFullName = strNewPath
  
  ' If the path changed, return true so the counter can be incremented
  If Len(strNewPath) < Len(strOldPath) Then ChangeLink = True
  
quit:
  On Error GoTo 0
End Function

Open in new window


@ J Zee - you now have the choice of using the PowerPoint UI as is and modifying the output as per Echo's instructions or using a macro. Swings and roundabouts - the choice is yours!
Avatar of J Zee
J Zee

ASKER

All great answers! Early after posting, I re-confirmed the absolute path piece, which was it. If anyone wants to share as I am, with others, your Excel file will be linked with the path which was set when you linked, of course!

But, if not careful, you can get tripped up.

For example, you may be moving files from a cloud drive, over to your C:\ drive, then back to your cloud folder for packaging, right,

In my case, I had to place the files on my C:\ drive, link up the PPT to Excel file (both files were in C:/Foldername.)

Once linked and saved, I zipped. Then, I placed all of these files back in my cloud folder to keep backed up.

This is all simple, but explaining to be clear.

Now, when emailing or putting online for download ... I can browse to the zip file on my cloud drive to grab the zip file.

Whoever opens this file, I make sure (in readme.txt, etc) they know to extract to the specific folder. Now, when they open either the PPT or Excel file, the two fikes are talkin.'

Everyone, thx so much, again! Your input is valued and will help others, to be sure.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial