Excel VBA 2010 issue with selecting and moving table after pasting into Powerpoint

Question

I have the code below which I found online to copy paste a tble from excel to powerpoint. After pasting the table into the slide, it fails on PPSlide.Shapes(1).Select with a Run-time error '-2147188160 (80048240)': Shape.Select : Invalide request. To select a shape, its view must be active. I've been searching and trying different things but can't seem to figure it out.. I thought after the paste that the table would be active and the code would jsut continue but it doesn't unless I activate/select the table in the slide and then click Run. Any help is appreciated. Thanks.

Dim pp As Object
Dim PPPres As Object
Dim PPSlide As Object
Dim Rng As Range

DestinationPPT = "C:\Users\username\Desktop\Data_Display.pptx"
Set pp = CreateObject("PowerPoint.Application")
Set PPPres = pp.Presentations.Open(DestinationPPT)
pp.Visible = True
Set Rng = ActiveSheet.Range("CA1:CJ" & Count + 1)

Rng.Copy

SlideCount = PPPres.Slides.Count
Set PPSlide = PPPres.Slides.Add(SlideCount, 12)
pp.ActivePresentation.Application.CommandBars.ExecuteMso ("PasteSourceFormatting")

PPSlide.Shapes(1).Select
pp.ActiveWindow.Selection.ShapeRange.Align msoAlignTops, True
pp.ActiveWindow.Selection.ShapeRange.Top = 65
pp.ActiveWindow.Selection.ShapeRange.Left = 7.2
pp.ActiveWindow.Selection.ShapeRange.Width = 700

Set PPSlide = Nothing
Set PPPres = Nothing
Set pp = Nothing

In what format you want to copt the Excel's Range to the PowerPoint Slide ? A Picture ? as what ? — Shai Rado
As a table, keeping basically the same source formatting. That doesn't seem to be the issue, its the lines of code afterwards to move the table in the slide that fails. — jmeddy
Have you tried adding PPSlide.Select right before your PPSlide.Shapes(1).Select ? — Rich Holton
Hi Rich, I think that was the missing link. Your suggestion also worked - Thanks! — jmeddy

Shai Rado Shai Rado · Accepted Answer · 2017-05-03T13:25:21

Try the code below:

Set pp = CreateObject("PowerPoint.Application")
Set ppPres = pp.Presentations.Open(DestinationPPT)
pp.Visible = True

' first set the Slide and select it
SlideCount = ppPres.Slides.Count
Set ppSlide = ppPres.Slides.Add(SlideCount, 12)
ppSlide.Select

' have the Copy part right beofre youe Paste it to PowerPoint
Set Rng = ActiveSheet.Range("CA1:CJ" & Count + 1)
Rng.Copy

pp.ActivePresentation.Application.CommandBars.ExecuteMso ("PasteSourceFormatting")

Dim myShape As Object
' set up the Pasted shape to an object
Set myShape = ppSlide.Shapes(ppSlide.Shapes.Count)

With myShape
    ' set-up the shape properties

End With

Excel VBA 2010 issue with selecting and moving table after pasting into Powerpoint

1 Answers