Macro to open Powerpoint and paste ranges from Excel - Troubleshoot error in code

[onthegreen03]

Hi -

I have some code that I copied from other users on line which opens PP (Procedure1) and then copies and pastes ranges from Excel (Procedure2) into that PP. When I run the code below it opens the Powerpoint no problem but it breaks down in Procedure2. Can someone help me figure out what needs to be fixed in Procedure 2 so that it copies/pastes the selected range into the PP opened in Procedure1? The code breaks at the "Add a slide to the presentation" step. Hopefully this makes sense. Many thanks for your help!

---------------------------------------------------------------------

Sub RunAllMacros()
Procedure1
Procedure2
End Sub

Sub Procedure1()

Dim objPPT As Object

Set objPPT = CreateObject("PowerPoint.Application")
objPPT.Visible = True

objPPT.Presentations.Open "C:\users\migreen\AppData\Roaming\Microsoft\Templates\Blank.potx"

End Sub

Sub Procedure2()

Dim rng As Range
Dim PowerPointApp As Object
Dim myPresentation As Object
Dim mySlide As Object
Dim myShape As Object

'Copy Range from Excel
Set rng = ThisWorkbook.ActiveSheet.Range("A1:C12")

'Add a slide to the Presentation
Set mySlide = myPresentation.Slides.Add(1, 11) '11 = ppLayoutTitleOnly

'Copy Excel Range
rng.Copy

'Paste to PowerPoint and position
mySlide.Shapes.PasteSpecial DataType:=2 '2 = ppPasteEnhancedMetafile
Set myShape = mySlide.Shapes(mySlide.Shapes.Count)

'Set position:
myShape.Left = 66
myShape.Top = 152

'Make PowerPoint Visible and Active
PowerPointApp.Visible = True
PowerPointApp.Activate

'Clear The Clipboard
Application.CutCopyMode = False

End Sub

 

[Colorations]

Your myPresentation variable isn't set to anything. I'm not familiar with powerpoint vba but maybe replace

Set mySlide = myPresentation.Slides.Add(1, 11) '11 = ppLayoutTitleOnly

with

Set mySlide = Application.ActivePresentation.Slides.Add(1, 11) '11 = ppLayoutTitleOnly

 

[onthegreen03]

Thanks for the reply Colorations. I tried your suggestion and it didn't work. Still stopping at that line of code. If you have any other recommendations I would love to hear them.

 

[Colorations]

It seems none of your variables have been instantiated so that's probably why this error and errors further down occur

 

[onthegreen03]

Yeah. If there are any VBA freaks of nature out there willing to re-write the Procedure2 code so that it works with Procedure1 I would be very grateful. Seems like I am close but I don't have the VBA chops to link the two procedures together.

 

[Worf]

Like this:

' Excel module
Dim objppt As PowerPoint.Application


Sub RunAllMacros()
Procedure1
Procedure2
MsgBox "Sub successfully executed!", 64
End Sub


Sub Procedure1()
Set objppt = CreateObject("PowerPoint.Application")
objppt.Visible = True
objppt.Presentations.Open "C:\Users\Eddie\Documents\PowerPoint\dt.potx"
End Sub


Sub Procedure2()
Dim rng As Range, mypres As PowerPoint.Presentation, mySlide As Object, myShape As Object
'Copy Range from Excel
Set rng = ThisWorkbook.ActiveSheet.[A1:C12]
Set mypres = objppt.ActivePresentation
Set mySlide = mypres.Slides.Add(1, 11) '11 = ppLayoutTitleOnly
'Copy Excel Range
rng.Copy
'Paste to PowerPoint and position
mySlide.Shapes.PasteSpecial DataType:=2 '2 = ppPasteEnhancedMetafile
Set myShape = mySlide.Shapes(mySlide.Shapes.Count)
'Set position:
myShape.Left = 66
myShape.Top = 152
'Make PowerPoint Visible and Active
objppt.Visible = 1
objppt.Activate
'Clear The Clipboard
Application.CutCopyMode = False
End Sub

 

[onthegreen03]

Thanks. I will try this and let you know if it works. Appreciate the help!

 

[onthegreen03]

THANK YOU Worf! You fixed it and it works all the way through. I realy appreciate the assistance on that one!

 

[Worf]

You are welcome!

 

[onthegreen03]

Hey Worf - I was wondering if you could help me one more time with this code? Let me know if you have some time to hear my plea. Thanks!