Excel VBA to open PPT file, update links, save as new & break links

[Avi22]

Hey Guys,

So I have a large report on Excel that is updated every few days, with a PowerPoint presentation with graphs and worksheet objects that are linked to that specific Excel.

Now since different reports are generated from this Excel file, I'm looking for a way to do these following steps in order to create several un-linked presentations:
1. Open the specific PowerPoint file
2. Update all the charts & macro-enabled worksheet objects
3. Save as new/different file
4. Break all links (so the graphs won't update again + it won't affect the "template" file)

This is a thing I'm trying to find a solution to for a while and finding the solution will be more than amazing!

Many many thanks!!

[Avi22]

Here is one example that I received that does the 3 first parts (open PPT, update links, save as) but doesn't break the link at the end, does someone know what's missing there?

Function RefreshPPT2()
Set PPT = CreateObject("PowerPoint.Application")
PPT.Visible = True
PPT.Presentations.Open "C:\Users\Avi\Desktop\Avi\Report - Number1.pptm", Untitled:=msoTrue
PPT.ActivePresentation.UpdateLinks
PPT.ActivePresentation.SaveAs Filename:="C:\Users\John\Desktop\Breaked.pptx"
PPT.ActivePresentation.Slides(i).Shapes(s).LinkFormat.BreakLink
PPT.Quit
Set PPT = Nothing
End Function

[snb]

Here is one example that I received

Where did you receive this ?

[Avi22]

I think a friend of mine saw this on a forum, so I'm not sure that's exactly what I need and maybe there is a better solution...

The line that causes a "run-time error: Bad argument type. Index expected collection index (string or integer)" is

PPT.ActivePresentation.Slides(i).Shapes(s).LinkFormat.BreakLink

What do you think?

[mancubus]

if this code is all you have tell VBA what i and s is...

as i and s are index numbers, they are long integers and since they are not assigned values they are 0.

a quick Google search shows i and s are common counter variables to loop slides and shapes

here is something to play with.

Sub vbax_59884_ppt_update_break_links()
 
    Dim i As Long
    Dim s As Long
     
    On Error Resume Next
     
    With CreateObject("PowerPoint.Application")
        .Visible = True
        .Presentations.Open "C:\Users\Avi\Desktop\Avi\Report - Number1.pptm", Untitled:=msoTrue
        With .ActivePresentation
            .UpdateLinks
            .Save
            For i = 1 To .Slides.Count
                For s = 1 To .Slides(i).Shapes.Count
                    .Slides(i).Shapes(s).LinkFormat.BreakLink
                Next s
            Next i
            .SaveAs Filename:="C:\Users\John\Desktop\Breaked.pptx"
        End With
    End With
 
End Sub

delete below line if you dont want to update the orginal ppt file:
.Save

[Avi22]

Hey Mancubus,

That's amazing! Exactly what I was looking for!!
Thank you so much, extremely appreciated