delete pictures from slides in powerpoint from excel

Question

I tried to figured out why the coding is not working, but the following code was supposed to open from excel a powerpoint and clear the existing slides in order to replace with new picture - however I'm getting the following:

error 91: Object variable or with block variable not set.

I tried several others code from the Stack but cannot make it work.. any help please? The deck contains slide 2 to slide 9 to cleared out.

Sub ppt_export()
Dim DestinationPPT As String
Dim ppApp As PowerPoint.Application
Dim ppPres As PowerPoint.Presentation
Dim objApp As Object, objSlide As Object, ObjShp As Object, objTable As` Object

DestinationPPT = "C:\Users\\Desktop\Summary.pptx"
Set ppApp = CreateObject("PowerPoint.Application")
Set ppPres = ppApp.Presentations.Open(DestinationPPT)
'delete the shapes from the renew the template

 For i = ppSlide.Shapes.Count To 1 Step -1
Set ppShape = ppSlide.Shapes(p)
If ppShape.Type = msoPicture Then ppShape.Delete
Next
End Sub

I'd like to know how to correct the code in order to continue the coding with copying excel worksheets as pictures into the respective slide.

Add Option Explicit to the top of the module and it would make your problem apparent. The For statement uses i, but then you attempt to use p inside the loop. — BigBen
@BigBen thank you, I switched the p to the i but it loop to the same error. — Guillaume

BigBen BigBen · Accepted Answer · 2019-03-30T03:38:36

First and most importantly, add Option Explicit to the top of the code module, and it will flag the various undeclared variables you have: p, i, ppSlide, and ppShape.

Then the code might look something like this:

Option Explicit

Sub ExportToPPT()
    Dim ppApp As PowerPoint.Application
    Set ppApp = New PowerPoint.Application

    Dim ppFileName As String
    ppFileName = "C:\Users\\Desktop\Summary.pptx"

    Dim ppPres As PowerPoint.Presentation
    Set ppPres = ppApp.Presentations.Open(Filename:=ppFileName)

    Dim ppSlide As PowerPoint.Slide

    Dim i As Integer
    For i = 2 To 9
        Set ppSlide = ppPres.Slides(i)

        Dim j As Integer
        For j = ppSlide.Shapes.Count To 1 Step -1
            If ppSlide.Shapes(j).Type = msoPicture Then
                ppSlide.Shapes(j).Delete
            End If
        Next j
    Next i
End Sub

delete pictures from slides in powerpoint from excel

1 Answers