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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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
 
Upvote 0
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 :)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top