PDA

View Full Version : [SOLVED] Excel VBA to open PPT file, update links, save as new & break links



Avi22
06-25-2017, 09:44 AM
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
06-25-2017, 01:24 PM
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
06-25-2017, 01:48 PM
Here is one example that I received

Where did you receive this ?

Avi22
06-25-2017, 03:03 PM
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
06-30-2017, 01:30 AM
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 :devil2:

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
07-01-2017, 04:51 AM
Hey Mancubus,

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