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

lolxor

New Member
Joined
Jul 29, 2015
Messages
9
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:
Code:
'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
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,265
I don't know the definitive answer. I did see one code example use this syntax.

Code:
[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

New Member
Joined
Jul 29, 2015
Messages
9
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:
Code:
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

New Member
Joined
Jul 29, 2015
Messages
9
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:
Code:
'Load pptSet Ppt1 = PptApp.Presentations.Open(PptPath, msoFalse, msoTrue, msoFalse)
to:
Code:
'Load pptSet Ppt1 = PptApp.Presentations.Open(PptPath, msoFalse, msoTrue, msoTrue)
fixed the error.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,739
Messages
5,470,453
Members
406,700
Latest member
Mark Rob

This Week's Hot Topics

Top