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

onthegreen03

Board Regular
Joined
Jun 30, 2016
Messages
61
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
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Colorations

Board Regular
Joined
Jun 13, 2016
Messages
61
Your myPresentation variable isn't set to anything. I'm not familiar with powerpoint vba but maybe replace
Code:
Set mySlide = myPresentation.Slides.Add(1, 11) '11 = ppLayoutTitleOnly
with
Code:
Set mySlide = Application.ActivePresentation.Slides.Add(1, 11) '11 = ppLayoutTitleOnly
 

onthegreen03

Board Regular
Joined
Jun 30, 2016
Messages
61
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

Board Regular
Joined
Jun 13, 2016
Messages
61
It seems none of your variables have been instantiated so that's probably why this error and errors further down occur
 
Last edited:

onthegreen03

Board Regular
Joined
Jun 30, 2016
Messages
61
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

Well-known Member
Joined
Oct 30, 2011
Messages
4,002
Like this:

Code:
' 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

Board Regular
Joined
Jun 30, 2016
Messages
61
Thanks. I will try this and let you know if it works. Appreciate the help!
 

onthegreen03

Board Regular
Joined
Jun 30, 2016
Messages
61
THANK YOU Worf! You fixed it and it works all the way through. I realy appreciate the assistance on that one!
 

onthegreen03

Board Regular
Joined
Jun 30, 2016
Messages
61
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,101,759
Messages
5,482,704
Members
407,359
Latest member
aaawww

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top