How to break links to excel chart

[werafa]

Hi all,
I have a template I paste excel charts into for a presentation. If I change a chart after pasting, it updates itself, and I want to stop this behavior.

I have found a 'break links' function on the file tab that you can navigate to and break each link after pasting, but find this a rather ordinary way to work.

Is there a way to force ppt to not refresh external data automatically (The break links tab says it is on manual update, but helpfully changes things anyway).

Alternatively, I run some code every time I paste a graph - what is the vba to toggle the external data link for the active shape/graph?

Thanks
Tim

[John Wilson]

have you tried paste by RIGHT click and choosing one of the embed choices (I'm assumiing you have 2010)

Otherwise I don't think you can toggle but you can break the link (possibly only from v. 2007)

ActiveWindow.Selection.ShapeRange(1).LinkFormat.BreakLink

It will error if the selected shape is not linked

[werafa]

Thanks John, much appreciated - I do have 2010, and am not very impressed with the 'let us choose what you want to do - and we'll bury the more obscure options where you can't find them' approach.

I already have a bit of code that I run (using advice you gave earlier (thanks :cloud9: )) and this should slot straight in.

Tim

[Paul_Hossler]

I think this does it.

I created an Excel pie chart, copied it, selected PP slide, and ran the macro.

I changed to data in Excel and the slide did not update


Sub PasteAndEmbed()
Call Windows(1).View.PasteSpecial(ppPasteOLEObject, msoFalse)
End Sub


BTW, this was good timing since I'm doing some of this right now.

Hopefully, other people will have suggestions to improve it, but for now I'm going to add the macro to the QAT since it seems to work

This seems to be the VBA equivalent of the [Paste] icon option (K) on the ribbon

Paul

[John Wilson]

Not sure this matters to you but it's not the same as "K"

For example right click will not give the option to change chart type. Not sure what code equates to "K" exactly though. I though ppPasteShape but it updates if the Excel chart updates.

This doesn't really make sense but seems to work

Dim osld As Slide
Call Windows(1).View.PasteSpecial(DataType:=ppPasteShape, Link:=False)
Set osld = Windows(1).View.Slide
osld.Shapes(osld.Shapes.Count).LinkFormat.BreakLink

[werafa]

If you mean "doesn't seem to make sense' as in you paste the link = false, then break it, I agree - If I go into the link manager, all the automatically updating links are set to only update manually - go figure.

Anyway, thanks for this, tis much appreciated and valuable education.
Tim

[John Wilson]

Yes, that's exactly what I meant!

[werafa]

Here is my working code

Sub SizeGraph()
' set size and position of active shape
' use to set graph to full width of slide with room for banner and footer
' also breaks auto-update link (probable bug in office 2010 ingores manual update setting)
' to use, select the shape of interest and run the macro - I point this macro to a custom ui button - see elsewhere in vbax forum for more details on custom ui

With ActiveWindow.Selection.ShapeRange(1)
.LockAspectRatio = False
.Top = 65 'points from top
.Left = 0 'points from left
.Width = 720 'points wide
.Height = 410
on error resume next
.LinkFormat.BreakLink
on error goto 0
End With
End Sub
It compiles and runs, and the powerpoint chart did not change when the excel version of the chart was changed after running it

I hope this helps someone
Tim