Updating the existing embedded excel doc on a chart

Well, I've put something together for you - it's

The environment is that I created a single slide PPTX and just inserted a chart, changing nothing, on the first slide. Saved and closed. What the code below does is change the value of the second row and second column of the embedded Excel sheet. It opens the PowerPoint package, loops through the slides to find the first chart and then from that, finds the Excel package and the first worksheet. Then it updates the value of the second row/second column (which is the first value on the chart). Again, this is not very elegant and could probably be a whole lot prettier using the SDK, but it should be enough to get you started.

On the note about "gets you half way there", I've discovered that the chart.xml document in the PowerPoint does not respect the Open XML Specifications of <c:chartSpace>.<c:externalData>.<c:autoUpdate val="1"/> . What this is supposed to do is to rehydrate the chart with the data in the embedded Excel workbook (by setting val="1" instead of the default val="0"). Changing this manually has no effect and when you click "edit data" in PowerPoint on the chart, the chart then gets updated and the attribute is set back to val="0"). This may be an issue you or I could ask about on the Office Open XML File Format Implementation forum later. So what this means is that you will still need to update the chart.xml as well as updating the embedded Excel worksheet.

Without further ado, here's the code.

Imports System.IO
Imports System.IO.Packaging
Imports System.Xml
Imports <xmlns:p="http://schemas.openxmlformats.org/presentationml/2006/main">
Imports <xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
Imports <xmlns:s="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
Module UpdateChart
    Private Const documentRelationshipType As String = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument"
    Private Const chartRelationshipType As String = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/chart"
    Private Const packageRelationshipType As String = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/package"
    Sub UpdateChartViaEmbeddedExcel()
        Dim pptxFilePath = "C:\Users\me\Documents\Presentation1.pptx"
        Using presentationPackage As Package = Package.Open(pptxFilePath, FileMode.OpenOrCreate, FileAccess.ReadWrite)
            Dim documentRelationship As PackageRelationship = presentationPackage.GetRelationshipsByType(documentRelationshipType).FirstOrDefault
            Dim documentUri As Uri = PackUriHelper.ResolvePartUri(New Uri("/", UriKind.Relative), documentRelationship.TargetUri)
            Dim documentPart As PackagePart = presentationPackage.GetPart(documentUri)
            Dim presentation = XElement.Load(New StreamReader(documentPart.GetStream))
            Dim slideRIDs = presentation.<p:sldIdLst>.<p:sldId>.Select(Function(f) f.@r:id.ToString).ToArray
            For i = 0 To slideRIDs.Count - 1
                Dim slidePart As PackagePart = presentationPackage.GetPart(PackUriHelper.ResolvePartUri(documentPart.Uri, documentPart.GetRelationship(slideRIDs(i)).TargetUri))
                Dim firstChartPartRelationship As PackageRelationship = slidePart.GetRelationshipsByType(chartRelationshipType).SingleOrDefault
                If firstChartPartRelationship IsNot Nothing Then
                    Dim firstChartPart = presentationPackage.GetPart(PackUriHelper.ResolvePartUri(slidePart.Uri, slidePart.GetRelationship(firstChartPartRelationship.Id).TargetUri))
                    Dim excelPackagePartRelationship As PackageRelationship = firstChartPart.GetRelationshipsByType(packageRelationshipType).SingleOrDefault
                    Dim excelPackagePart = presentationPackage.GetPart(PackUriHelper.ResolvePartUri(firstChartPart.Uri, firstChartPart.GetRelationship(excelPackagePartRelationship.Id).TargetUri))
                    Using excelPackage = Package.Open(excelPackagePart.GetStream, FileMode.OpenOrCreate, FileAccess.ReadWrite)
                        documentRelationship = excelPackage.GetRelationshipsByType(documentRelationshipType).FirstOrDefault
                        documentUri = PackUriHelper.ResolvePartUri(New Uri("/", UriKind.Relative), documentRelationship.TargetUri)
                        documentPart = excelPackage.GetPart(documentUri)
                        Dim spreadsheet = XElement.Load(New StreamReader(documentPart.GetStream))
                        Dim firstWorksheetRID = spreadsheet.<s:sheets>.<s:sheet>.FirstOrDefault.@r:id
                        Dim firstWorksheetPart As PackagePart = excelPackage.GetPart(PackUriHelper.ResolvePartUri(documentPart.Uri, documentPart.GetRelationship(firstWorksheetRID).TargetUri))
                        Dim firstWorksheet = XElement.Load(firstWorksheetPart.GetStream)
                        Dim secondRowSecondColumn = firstWorksheet.<s:sheetData>.<s:row>.Skip(1).<s:c>.Skip(1).FirstOrDefault
                        secondRowSecondColumn.<s:v>.Value = 11
                        Using xw As XmlWriter = XmlWriter.Create(firstWorksheetPart.GetStream(FileMode.Create, FileAccess.Write))
                            firstWorksheet.Save(xw)
                        End Using
                    End Using
                    Exit For
                End If
            Next
        End Using
    End Sub
End Module