Break links in .pptx before saving (from excel vba)

[lolxor]

Hi all

I have an excel macro that draws some numbers from a database to populate an excel spreadsheet and then updates some graphs in a .pptx and sends it via outlook. It all works fine but now I really want to break the links in all graphs to avoid people accidentially updating the data (the reports are supposed to show data from a specific date).

I found a solution that worked well from inside powerpoint and tried to do something similar in my vba macro. When I run my modified macro now I get this error though: "Run-time error: Method 'BreakLink' of object 'LinkFormat' failed"

Here is the snippet of my code that concerns the linkbreaking:

'Load pptSet Ppt1 = PptApp.Presentations.Open(PptPath, msoFalse, msoTrue, msoFalse) 
 
 'Updatelinks
Ppt1.UpdateLinks 
Application.CalculateUntilAsyncQueriesDone 
 
 'Break links
Dim shp As PowerPoint.Shape 
Dim sld As PowerPoint.Slide 
For Each sld In Ppt1.Slides 
    For Each shp In sld.Shapes 
        If shp.Type = msoLinkedOLEObject Then 
            shp.LinkFormat.BreakLink 
        End If 
    Next shp 
Next sld 
 
 'Save file
NewPath = HomePath & "Rapporter\2015\" & Format(Date - i, "yyyymmdd") & " Daily report" 

Ppt1.SaveCopyAs (NewPath)

The vba editor recognizes the BreakLink method (suggests it), and I referenced the PowerPoint object library. I use Office 2013 in case that might be important. Any ideas to what may cause this error and how to fix it - or an alternate method entirely to remove the links.

Thanks in advance

 

[AlphaFrog]

I don't know the definitive answer. I did see one code example use this syntax.

[color=darkblue]If[/color] [color=darkblue]Not[/color] shp.LinkFormat [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color] shp.LinkFormat.BreakLink

 

[lolxor]

Thanks for the quick answer. I tried swapping but same error, same place. I guess it could have worked if the error was somehow related to linkformat being Nothing, but that seems not to be the case. When I open the powerpoint in question manually and run this similar code it works perfectly with no problems:

Sub Remove_links()    Dim sld As Slide, shp As Shape
    For Each sld In ActivePresentation.Slides
        For Each shp In sld.Shapes
            With shp
                If .Type = msoLinkedOLEObject Then
                    .LinkFormat.BreakLink
                End If
            End With
        Next shp
        Set shp = Nothing
    Next sld
    Set sld = Nothing
End Sub

I haven't got much of an idea of how the error arises: I will go back to testing while hoping for someone here to have an idea

 

[lolxor]

For anyone else with the same problem. It seems for some reason (obscure to me) the LinkFormat.BreakLink needs the pptx open in its own window. Changing:

'Load pptSet Ppt1 = PptApp.Presentations.Open(PptPath, msoFalse, msoTrue, msoFalse)

to:

'Load pptSet Ppt1 = PptApp.Presentations.Open(PptPath, msoFalse, msoTrue, msoTrue)

fixed the error.