Editing chart data after breaking a link.

Question

Editing chart data after breaking a link.

Anonymous

I want to know if its possible to edit the the chart data in PowerPoint after i break the link from its source excel file. I am using the following script to insert a chart from excel to powerpoint and then break the link between the two.

Sub ChartX2P()

Dim PowerPointApp As Object

Dim myPresentation As Object

Dim mySlide As Object

Dim myShape As Object

If ActiveChart Is Nothing Then

MsgBox "Hey, please select a chart first."

Exit Sub

End If

If PowerPointApp Is Nothing Then _

Set PowerPointApp = CreateObject(class:="PowerPoint.Application")

On Error GoTo 0

Application.ScreenUpdating = False

Set myPresentation = PowerPointApp.Presentations.Add

Set mySlide = myPresentation.Slides.Add(1, 11) '11 = ppLayoutTitleOnly

ActiveChart.ChartArea.Copy

mySlide.Shapes.Paste

Set myShape = mySlide.Shapes(mySlide.Shapes.Count)

myShape.Left = 200

myShape.Top = 200

PowerPointApp.Visible = True

PowerPointApp.Activate

Application.CutCopyMode = False

myShape.LinkFormat.BreakLink

End Sub

The edit data option when i right click the chart on PowerPoint is greyed out.

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

4 answers

Answer 1

I spent quite a while looking into this today: there isn't a way to edit this data conventionally from within Powerpoint absent some kind of extension, because it delegates all that to Excel. However, it is editable. People often suggest that the semantic data is lost when a chart is unlinked, but this isn't exactly true: you can verify that if you change the chart type, the data is reflowed logically. I haven't found the answer anywhere else, so so in the hope of saving others some time, here is what you can do if you don't mind getting your hands dirty.

The short answer is that if you rename your .PPTX file to ,ZIP, you can browse it as an archive. Inside, the display semantics for unlinked charts are stored at /ppt/charts/chart1.xml, chart2.xml, etc. (You'll have to figure which is which, though you can generally tell by inspecting them.) Data series are stored in c:ser tags, with the values in c:v tags. These are the main things you'll likely need to edit, as the rest is mostly editable from PPT even when unlinked.

If you want to do something complicated, the specifications for these XML files are a part of the Office Open XML specification, which you should be able to find around. For instance here is the spec for a bar chart series, and Microsoft's API for the same thing is here. Unfortunately, the official API for this is in C#, with all the overhead that implies; I don't believe it's possible to do this with the normal VBA document structure. If you don't mind an ugly solution though, you can use VBA to unzip the PPTX, edit the XML manually using MSXML, and then put it back together. This library automates some of this, though I haven't tried it myself, and it should be possible to do the same in pure PowerShell.

Answer 2

As Johnk's said, once you break the link to an Excel chart or data, you're left with a metafile picture of the original Excel object.

You can usually ungroup the picture a time or two and get to a point where you can edit the individual bits of text used for labels and such, but it's no longer chart data ... changing the numbers will no longer change the height of the bars in a bar chart, for example.

My question would be: Why are you breaking the link? If it's to keep other people from having access to the data behind the charts when you share the PowerPoint, don't give them the linked files. All they'll have is the metafile representation of the chart to fiddle with.

You might in that case want to use:

MyShape.LinkFormat.AutoUpdate = ppUpdateOptionManual

so that the chart won't attempt to update every time someone opens the presentation.

Answer 3

John Korchok 231.4K Volunteer Moderator

The data is in Excel, but you broke the link. So the chart data is no longer editable. In effect, you're creating a graphic object that looks like a chart.

If you will need to edit the data in the future, remove the command to break the link.

0 comments

Answer 4

Hi Rishab

I'm AnnaThomas and I'd be happy to help you with your question. In this Forum, we are Microsoft consumers just like yourself.

If the "Edit Data" option is grayed out, it could be due to the following reasons:

The chart is an image: If you pasted the chart as an image, you won't be able to edit the data in PowerPoint. You would need to recreate the chart in PowerPoint or link it back to the Excel file to edit the data.

The chart is grouped: If the chart is grouped with other objects in PowerPoint, you may not be able to edit the data. Try ungrouping the objects, and then see if the "Edit Data" option becomes available.

PowerPoint is in protected view: If the PowerPoint file is in "Protected View," some features such as editing the chart data may be disabled. You can try enabling editing by clicking on "Enable Editing" on the yellow bar at the top of the PowerPoint window.

If none of the above options work, you can try copying the chart from PowerPoint and pasting it into a new Excel file to edit the data. Then, you can recreate the chart in PowerPoint or link it back to the new Excel file.

I hope this helps ;-), let me know if this is contrary to what you need, I would still be helpful to answer more of your questions.

Best Regards,

AnnaThomas

Give back to the community. Help the next person with this problem by indicating whether this answer solved your problem. Click Yes or No at the bottom.

Share via

Editing chart data after breaking a link.

4 answers