OOXML Hacking: Linked Excel Charts

Posted on by - Updated on June 8, 2021

Time marches on and this post is obsolete. Please check out XML Hacking: Fix Broken PowerPoint Links for more current information on this topic.

OS X versions of Microsoft Office have always been the poor step-children in the Microsoft family. Always missing important features found only in the Windows alternatives. One of these obvious disparities has been in the area of linked Excel charts. In Windows, Microsoft uses their OLE technology to allow, for instance, an Excel workbook to be linked to a PowerPoint presentation.

The Excel workbook can still be edited independently. The charts can be revised based on new data, and when the presentation is opened, the updated information will be displayed. This is a powerful tool in many situations where information is changing rapidly and the presentation must stay current. This approach also leverages the inheritance of data. This allows users to have only one data source that drives updates in many different places.

Of course, OLE being a proprietary Microsoft technology, it has almost no support on other operating systems. The only way it appears in OS X is if an individual software vendor creates an instance that works with their code. Office for Mac has had its own tiny version of OLE that allows some, but not all the features found in Windows. You could only insert Office objects (forget about PDFs) and you couldn’t link, only embed.

Until now. With the release of Office 2016 for Mac, the tiniest crack of linkability has finally opened. Try these steps: Open Excel 2016 for Mac and create a chart. Select that chart and copy it. Open a presentation in PowerPoint and click on the down-pointing arrowhead beside the Paste button. Now your options include all of the following:

  • Use Destination Theme & Embed Workbook
  • Keep Source Formatting & Embed Workbook
  • Use Destination Theme & Link Data
  • Keep Source Formatting & Link Data
  • Paste as Picture

Options 1, 2 and 5 have always been available. The news is with 3 and 4, where linked data for charts becomes a new possibility. But along with this fresh opportunity comes a problem that hasn’t been addressed by Microsoft. It’s very nice to link charts, but the Microsoft default is always to hard code the link path. This means that moving the presentation and Excel source to a different computer destroys the links. The charts are no longer editable, because the link path has changed.

Remember the poor step-child analogy? Here it is again: Windows versions of PowerPoint allow you to edit the links in the program so you can fix the path problem. But no such facility exists on the Mac. To update those linked Excel charts, you need to … hack the XML!

If you’re new to XML hacking, please read my introduction to the subject. Since this topic is specific to OS X, it’s also vital to read XML Hacking: Editing in OS X as well. I assume that you have figured out the correct path to the Excel file on the computer where the presentation has been moved.

Updating linked Excel Charts with XML Editing

After unzipping the presentation, you’re going to look inside the folders for ppt/charts/rels. Office XML files are full of rels folders that contain the relationships between the components of the document. Each chart in the presentation consists of a file i.e. chart1.xml with a corresponding chart1.xml.rels inside the rels folder. The number in the chart name increments for each additional chart linked.

The contents of chart1.xml.rels looks like this:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
  <Relationship Id="rId1" Type="http://schemas.microsoft.com/office/2011/relationships/chartStyle" Target="style1.xml" />
  <Relationship Id="rId2" Type="http://schemas.microsoft.com/office/2011/relationships/chartColorStyle" Target="colors1.xml" />
  <Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/oleObject" Target="file://localhost/Users/server/Documents/Dockets/Test/Excel/LinkTest.xlsx" TargetMode="External" />
</Relationships>

The lines of code are long, please scroll to see where I’ve bolded the path and file name, this is the section you have to modify to update the linked Excel chart.

Just as a comparison, here’s the analogous information from a PowerPoint 2010 file. In this case, there is not a chart folder containing chart.xml files. Instead, the charts are part of the slide files and are found in slide1.xml. The rels file is slide1.xml.rels and it looks like this:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
	<Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/oleObject" Target="file:///I:\Dockets\Test\Excel\ExcelLink2010.xlsx!Sheet1!%5bExcelLink2010.xlsx%5dSheet1%20Chart%201" TargetMode="External"/>
	<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/slideLayout" Target="../slideLayouts/slideLayout7.xml"/>
	<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing" Target="../drawings/vmlDrawing1.vml"/>
	<Relationship Id="rId4" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/image" Target="../media/image1.emf"/>
</Relationships>

A close examination shows that much of the same information in a Mac file is also here, but the file and path is Windows-style. Using this information, you’re ready to update those linked Excel charts with the best of them!

2:29 am

Leave a Reply

*Required fields. Your email address will not be published.

Posting XML? To enter XML code, please replace all less than signs "<" with "&lt;" and greater than signs ">" with "&gt;". Otherwise, Wordpress will strip them out and you will see only a blank area where your code would have appeared.