Errors with Slide and Shape Objects in Excel VBA

Question

I am trying to retrieve the links in which a PowerPoint is connected to using VBA in Excel. I receive two different errors from the two different approaches in which I will attach below, both stemming from calling the Slide and Shape objects of PowerPoint. The first macro results in an "Object required" error starting with the first line of the For Loop.

Sub Macro1()
'Opening up the PowerPoint to retrieve the links
Dim PPTName As String
Dim PPTApp As Object

PPTName = Sheets("Sheet1").Range("G2").Value

Set PPTApp = CreateObject("PowerPoint.Application")
PPTApp.Presentations.Open PPTName

Dim i As Integer
Dim j As Long
Dim k As Long
i = 10

For j = 1 To PPT.ActivePresentation.Slides.Count
    For k = 1 To PPT.ActivePresentation.Slides(i).Shapes.Count
        If PPTShape.Type = msoLinkedPicture Or PPTShape.Type = msoLinkedOLEObject Then
            Sheets("Sheet1").Range("G" & CStr(i)) = PPTShape.LinkFormat.SourceFullName
            i = i + 1
        End If
        k = k + 1
    Next k
    
    j = j + 1
Next j

End Sub

The second macro results in a "Compile error" starting with the "Set PPTSlides = CreateObject("PowerPoint.Slides")."

Sub Macro2()

Dim PPTName As String
Dim PPTApp As Object

PPTName = Sheets("Sheet1").Range("G2").Value

Set PPTApp = CreateObject("PowerPoint.Application")
PPTApp.Presentations.Open PPTName

Dim PPTSlides As Object
Dim PPTShapes As Object
Set PPTSlides = CreateObject("PowerPoint.Slides")
Set PPTShapes = CreateObject("PowerPoint.Shapes")

For Each PPTSlides In PPT.ActivePresentation.Slides
    For Each PPTShapes In PPT.ActivePresentation.Shapes
        If PPTShape.Type = msoLinkedPicture Or PPTShape.Type = msoLinkedOLEObject Then
            Sheets("Sheet1").Range("G" & CStr(i)) = PPTShape.LinkFormat.SourceFullName
            i = i + 1
        End If
    Next PPTShapes
Next PPTSlides

End Sub

I have not used VBA in Excel to work with PowerPoint before, so this is a new learning curve for me. Because of these errors, I have not been able to check my For Loop for errors as well. Any help is appreciated on these issues. Thanks in advance!

Answer 1

Fortunately, that is only a minor issue: A wrong index is used:

i = 10

For j = 1 To PPT.ActivePresentation.Slides.Count
    For k = 1 To PPT.ActivePresentation.Slides(i).Shapes.Count

If you look closely, then you need to use j instead of i in the last row.

And for the second code listing, there you can just omit the lines

Set PPTSlides = CreateObject("PowerPoint.Slides")
Set PPTShapes = CreateObject("PowerPoint.Shapes")

Because down below the first variable will be set from ActivePresentation.Slides.

As you are using the for each loop it also make sense to rename these two variables from plural to singular, i.e. PPTSlide instead of PPTSlides.

Please note as well that For Each PPTShapes In PPT.ActivePresentation.Shapes does not work. You need to get the Shapes from For Each PPTShape in PPTSlide.Shapes.

All the best