Selecting PPT slide with VBA keeps generating ActiveX error

[mjohnston0209]

I am trying to design a macro that will open up an existing PPT, delete all objects on a specific slide, and then copy updated objects from excel to the now blank slide. I have found several videos that are very helpful. However, I am running into a simple problem. I keep getting an "ActiveX component can't create object" error with defining the slide number. Below is the code that works perfectly in the sample video I saw. Would anyone know why I keep getting this error? I have one PPT open, and the file was selected before entering the VBA Editor to run the macro.

Sub SelectSlides()

'Declare the variables
Dim PPTSlide As Slide

'Create a reference to the slide we want to work with
Set PPTSlide = ActivePresentation.Slides(2)

End Sub

 

[smozgur]

Would anyone know why I keep getting this error?

That's because ActivePresentation property means nothing to Excel without the parent object, which is a PowerPoint Application pointer.

Although the following is not a complete implementation, it should work with this certain assumption: the PPT file should be opened in PowerPoint when this macro is executed. Otherwise, the macro will fail. Also, you mentioned opening an existing PPT file, however, your sample code looks like it is already opened prior to executing SelectSlides macro, so I just wanted to let you know what is wrong with your code.

You simply need PPTApp as shown below to access the ActivePresentation property which belongs to the PowerPoint application.

Sub SelectSlides()

'Declare the variables
Dim PPTApp As PowerPoint.Application
Dim PPTSlide As Slide

'Get the PowerPoint Application instance
Set PPTApp = GetObject(, "PowerPoint.Application")

'Create a reference to the slide we want to work with
Set PPTSlide = PPTApp.ActivePresentation.Slides(2)

End Sub

 

[mjohnston0209]

That's because ActivePresentation property means nothing to Excel without the parent object, which is a PowerPoint Application pointer.

Although the following is not a complete implementation, it should work with this certain assumption: the PPT file should be opened in PowerPoint when this macro is executed. Otherwise, the macro will fail. Also, you mentioned opening an existing PPT file, however, your sample code looks like it is already opened prior to executing SelectSlides macro, so I just wanted to let you know what is wrong with your code.

You simply need PPTApp as shown below to access the ActivePresentation property which belongs to the PowerPoint application.

Sub SelectSlides()

'Declare the variables
Dim PPTApp As PowerPoint.Application
Dim PPTSlide As Slide

'Get the PowerPoint Application instance
Set PPTApp = GetObject(, "PowerPoint.Application")

'Create a reference to the slide we want to work with
Set PPTSlide = PPTApp.ActivePresentation.Slides(2)

End Sub

Thank you! It works perfectly for that sample code.

If it wouldn't be too much to ask, here is the initial code for what I am doing. I wanted to try to carry over what you provided earlier, but it doesn't work. So far, all of my knowledge with vba has been limited to Excel, so this is a little over my head. Below is my coding for opening my PowerPoint presentation (file opens but not sure if it is the correct syntax because I cannot select the slide with what I have or what you provided above), selecting a specific slide, and deleting all the objects on that specific slide.

Sub DeleteObj()

'Declare PowerPoint Variables
Dim PPTApp As PowerPoint.Application
Dim PPTPres As PowerPoint.Presentation
Dim PPTSlide As PowerPoint.Slide
Dim PPTShape As PowerPoint.Shape

'Declare Excel Variables
Dim ExcObj, ObjType, ObjArray As Variant
Dim LefArray, TopArray, HgtArray, WidArray As Variant
Dim x As Integer

'Open Instance of PowerPoint
Set PPTApp = New PowerPoint.Application
    PPTApp.Visible = True
    PPTApp.Presentations.Open "C:\Users\mjohnston\Desktop\Presentation1.pptx"
    PPTApp.Activate

'Create a reference to the slide we want to work with
Set PPTSlide = ActivePresentation.Slides(2)

TotalShapes = PPTSlide.Shapes.count

    For i = TotalShapes To 1 Step -1
    PPTSlide.Shapes(i).Delete
    Next

End Sub

 

[mjohnston0209]

That's because ActivePresentation property means nothing to Excel without the parent object, which is a PowerPoint Application pointer.

Although the following is not a complete implementation, it should work with this certain assumption: the PPT file should be opened in PowerPoint when this macro is executed. Otherwise, the macro will fail. Also, you mentioned opening an existing PPT file, however, your sample code looks like it is already opened prior to executing SelectSlides macro, so I just wanted to let you know what is wrong with your code.

You simply need PPTApp as shown below to access the ActivePresentation property which belongs to the PowerPoint application.

Sub SelectSlides()

'Declare the variables
Dim PPTApp As PowerPoint.Application
Dim PPTSlide As Slide

'Get the PowerPoint Application instance
Set PPTApp = GetObject(, "PowerPoint.Application")

'Create a reference to the slide we want to work with
Set PPTSlide = PPTApp.ActivePresentation.Slides(2)

End Sub

Nevermind. I got it! Thanks again! I struggled on this for several hours yesterday.

 

[smozgur]

Thanks for the feedback.

It is always a good habit to work with objects. You can also get familiarized with the various object properties by using the code completion (typing dot after objects in the code).

Although your code is perfectly fine, you don't have to access all the shapes on the slide by looping through them. You can simply use the Shapes.Range property.

Sub DeleteObj()
'Declare PowerPoint Variables
Dim PPTApp As PowerPoint.Application
Dim PPTPres As PowerPoint.Presentation
Dim PPTSlide As PowerPoint.Slide

    'Open Instance of PowerPoint
    Set PPTApp = New PowerPoint.Application
    PPTApp.Visible = True
    Set PPTPres = PPTApp.Presentations.Open("C:\Users\mjohnston\Desktop\Presentation1.pptx")

    'Create a reference to the slide we want to work with
    Set PPTSlide = PPTPres.Slides(2)

    'Delete all the shapes on the slide
    PPTSlide.Shapes.Range.Delete
End Sub

Once you get familiarized with the objects and their methods/properties, you'll see even the following code would do the same job in fewer code lines.

Sub DeleteObj()
    With New PowerPoint.Application
        .Visible = True
        .Presentations.Open("C:\Users\mjohnston\Desktop\Presentation1.pptx").Slides(2).Shapes.Range.Delete
    End With
End Sub

However, in the last sample code above, you won't have any pointer to the PowerPoint Application. You can't even close the application without accessing the session other than doing that in the With...End With block. I just sent it to explain the relations and chaining between methods and properties.

 

[mjohnston0209]

Thanks for the feedback.

It is always a good habit to work with objects. You can also get familiarized with the various object properties by using the code completion (typing dot after objects in the code).

Although your code is perfectly fine, you don't have to access all the shapes on the slide by looping through them. You can simply use the Shapes.Range property.

Sub DeleteObj()
'Declare PowerPoint Variables
Dim PPTApp As PowerPoint.Application
Dim PPTPres As PowerPoint.Presentation
Dim PPTSlide As PowerPoint.Slide

    'Open Instance of PowerPoint
    Set PPTApp = New PowerPoint.Application
    PPTApp.Visible = True
    Set PPTPres = PPTApp.Presentations.Open("C:\Users\mjohnston\Desktop\Presentation1.pptx")

    'Create a reference to the slide we want to work with
    Set PPTSlide = PPTPres.Slides(2)

    'Delete all the shapes on the slide
    PPTSlide.Shapes.Range.Delete
End Sub

Once you get familiarized with the objects and their methods/properties, you'll see even the following code would do the same job in fewer code lines.

Sub DeleteObj()
    With New PowerPoint.Application
        .Visible = True
        .Presentations.Open("C:\Users\mjohnston\Desktop\Presentation1.pptx").Slides(2).Shapes.Range.Delete
    End With
End Sub

However, in the last sample code above, you won't have any pointer to the PowerPoint Application. You can't even close the application without accessing the session other than doing that in the With...End With block. I just sent it to explain the relations and chaining between methods and properties.

I wish I would have majored in this. It's fascinating. Thanks for all your help!

I also posted another thread that is a completion of this one (copies and pastes the objects from excel to PPT). Feel free to check it out...VBA Copying pivot table to powerpoint slide

 

[smozgur]

You're welcome.

Just one note about your project. As I can understand, you are trying to create a PowerPoint presentation slide that includes objects from the Excel workbook, such as range, table, chart.

Is there a specific reason that you want to transfer objects by using VBA? Why don't you just copy the objects one by one and paste special->paste link from Excel to PowerPoint? This way, all Excel objects on the slide will be linked to the Excel workbook, and you wouldn't need to copy & paste & locate & size anymore. Your presentation will be always opened with the updated Excel objects. If the source is always the same file, and the objects are always the same objects, then you might want to give it a try and likely be happier with the internal methods instead of developing the same thing in VBA.

 

[mjohnston0209]

You're welcome.

Just one note about your project. As I can understand, you are trying to create a PowerPoint presentation slide that includes objects from the Excel workbook, such as range, table, chart.

Is there a specific reason that you want to transfer objects by using VBA? Why don't you just copy the objects one by one and paste special->paste link from Excel to PowerPoint? This way, all Excel objects on the slide will be linked to the Excel workbook, and you wouldn't need to copy & paste & locate & size anymore. Your presentation will be always opened with the updated Excel objects. If the source is always the same file, and the objects are always the same objects, then you might want to give it a try and likely be happier with the internal methods instead of developing the same thing in VBA.

I would also like to include pivot tables in addition to the objects you mentioned above.

I originally began exploring that route...to see if I could have multiple objects from one excel file linked to a PPT. The only thing I could find was to have one object per file. If I saved the excel document with a different sheet selected, then the image on that sheet would be portrayed in the PPT.

I also looked into embedding a pivot table. I revised the data source information to see if the Pivot Table would update, but that didn't work as well.

However, after reading your comment/instructions, I believe your solution is the better option. I will use this going forward so thanks!

Lastly, are you good with graphs?. I am decent but struggling on one last item for my slide deck. It is a weird stacked/clustered bar graph I am trying to create. The last open thread I have is titled Creating a Stack/Clustered Bar Chart. I have several images uploaded to show what I am searching for, but I am not sure if it is possible.

Thanks again for all your help on this!

 

[AlexBackerZ]

Thanks for the feedback.

It is always a good habit to work with objects. You can also get familiarized with the various object properties by using the code completion (typing dot after objects in the code).

Although your code is perfectly fine, you don't have to access all the shapes on the slide by looping through them. You can simply use the Shapes.Range property.

Sub DeleteObj()
'Declare PowerPoint Variables
Dim PPTApp As PowerPoint.Application
Dim PPTPres As PowerPoint.Presentation
Dim PPTSlide As PowerPoint.Slide

    'Open Instance of PowerPoint
    Set PPTApp = New PowerPoint.Application
    PPTApp.Visible = True
    Set PPTPres = PPTApp.Presentations.Open("C:\Users\mjohnston\Desktop\Presentation1.pptx")

    'Create a reference to the slide we want to work with
    Set PPTSlide = PPTPres.Slides(2)

    'Delete all the shapes on the slide
    PPTSlide.Shapes.Range.Delete
End Sub

Once you get familiarized with the objects and their methods/properties, you'll see even the following code would do the same job in fewer code lines.

Sub DeleteObj()
    With New PowerPoint.Application
        .Visible = True
        .Presentations.Open("C:\Users\mjohnston\Desktop\Presentation1.pptx").Slides(2).Shapes.Range.Delete
    End With
End Sub

However, in the last sample code above, you won't have any pointer to the PowerPoint Application. You can't even close the application without accessing the session other than doing that in the With...End With block. I just sent it to explain the relations and chaining between methods and properties.

thank you for this piece of code

 

[smozgur]

thank you for this piece of code

Glad to hear it helps.