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!
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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\
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\
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
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....
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:
@ 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!
C:\Users\MyName\Documents\
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
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
@ 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!
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.