Page 1 of 10 123 ... LastLast
Results 1 to 10 of 91

Thread: Macro to open Powerpoint and paste ranges from Excel - Troubleshoot error in code
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2016
    Posts
    56
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    Board Regular
    Join Date
    Jun 2016
    Posts
    61
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  3. #3
    Board Regular
    Join Date
    Jun 2016
    Posts
    56
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  4. #4
    Board Regular
    Join Date
    Jun 2016
    Posts
    61
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    It seems none of your variables have been instantiated so that's probably why this error and errors further down occur
    Last edited by Colorations; Jun 30th, 2016 at 04:53 PM.

  5. #5
    Board Regular
    Join Date
    Jun 2016
    Posts
    56
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  6. #6
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    3,726
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    2 Thread(s)

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

    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
    Excel 2013 / Windows 8.1 (home)
    Excel 2013 / windows 7 (work)


  7. #7
    Board Regular
    Join Date
    Jun 2016
    Posts
    56
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

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

  8. #8
    Board Regular
    Join Date
    Jun 2016
    Posts
    56
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

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

  9. #9
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    3,726
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    2 Thread(s)

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

    You are welcome!
    Excel 2013 / Windows 8.1 (home)
    Excel 2013 / windows 7 (work)


  10. #10
    Board Regular
    Join Date
    Jun 2016
    Posts
    56
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •