Copying a range in Excel and pasting it "Keep Source Formatting" into slide 2 of a PowerPoint presentation

I'm trying to adapt a code I have that copies an Excel chart and pastes it into a PowerPoint presentation as 'Keep Source Formatting' but I don't know the syntax for line 10. It may not be as simple as replacing that one line, but if you can tell me the simplest way to write it that would be great.
Sub RangeToPresentation()
Dim PPApp  As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
Set rng = ThisWorkbook.ActiveSheet.Range([B1], [G13].End(xlUp))
    rng.Copy
    Set PPApp = GetObject(, "Powerpoint.Application")
    Set PPPres = PPApp.ActivePresentation
    PPApp.ActiveWindow.ViewType = ppViewSlide
    With PPPres.Slides(2).Shapes.Paste
        .top = 74
        .Left = 20
    End With
End Sub

Open in new window

Thanks,
John
LVL 1
John CarneyReliability Business Tools Analyst IIAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
HI,

pls try

Sub RangeToPresentation()
Dim PPApp  As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
Set Rng = ThisWorkbook.ActiveSheet.Range([B1], [G13].End(xlUp))
    Rng.Copy
    Set PPApp = GetObject(, "Powerpoint.Application")
    Set PPPres = PPApp.ActivePresentation
    PPApp.ActiveWindow.ViewType = ppViewSlide
    With PPPres
        .Windows(1).Activate
        .Windows(1).View.GotoSlide 2
        .Application.CommandBars.ExecuteMso ("PasteSourceFormatting")
        With .Slides(2).Shapes(.Slides(2).Shapes.Count)
            .Top = 74
            .Left = 20
        End With
    End With
End Sub

Open in new window

Regards

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
John CarneyReliability Business Tools Analyst IIAuthor Commented:
This is great, Rgonzo, thanks. The only problem is that there are already 2 tables, 1 rectangle and one textbox in the slide, and it moves one or more of them to the Top:74, Left:20 location, while leaving the table we just added in the middle of the slide. How do i modify it so that it leaves the existing objects where they are and moves the new one to the desired location?

Thanks,
John.
Rgonzo1971Commented:
Could you send a dummy?
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

John CarneyReliability Business Tools Analyst IIAuthor Commented:
Here are the two files. All I need is one line to replace "With .Slides(2).Shapes(.Slides(2).Shapes.Count)" so that PowerPoint positions the currently active table that was just placed there the line before.

Thanks,
John
CopyAndPasteRangeInPPT.xlsm
CopyAndPasteFromExcel.pptx
John CarneyReliability Business Tools Analyst IIAuthor Commented:
Okay, here's the PPT onboard code to do exactly what I want. It works perfectly when initiated within PowerPoint, but it won't run when appended to my Excel code.
Sub MoveObject()
Set PPApp = GetObject(, "Powerpoint.Application")
With PPApp.ActiveWindow.Selection.ShapeRange(1)
 .Left = 25
 .Top = 74
 .Height = 192
End With
End Sub

Open in new window

So how do I trick it into thinking that the code above is being initiated by PowerPoint when in fact it's being "called" from within my Excel code?

Thanks,
John
John CarneyReliability Business Tools Analyst IIAuthor Commented:
One solution I imagine would be to call the Powerpoint macro "MoveObject" at the end of my Excel code once on the Powerpoint slide. What would the syntax to do that be?
Rgonzo1971Commented:
then try

    With PPApp.ActiveWindow.Selection.ShapeRange(1)
     .Name = "DarkFlightTable"
     .Left = 40
     .top = 70
    End With

Open in new window

Regards
John CarneyReliability Business Tools Analyst IIAuthor Commented:
I tried it but it still doesn't work. As far as I know you have to load some Add-In to your Powerpoint in order to be able to call a Powerpoint macro as part of an Excel code. If that's true, what Add-In is required for that and where would I find it to download?

Thanks, John
Rgonzo1971Commented:
Is Powerpoint already open when you run the XL macro

pls try (Shaperange without Index)
    With PPApp.ActiveWindow.Selection.ShapeRange
     .Name = "DarkFlightTable"
     .Left = 40
     .top = 70
    End With

Open in new window

John CarneyReliability Business Tools Analyst IIAuthor Commented:
Thanks for giving me a solution that adheres to one of my top criteria which is simple, concise and elegant, I really appreciate it. I've kind of given up on being able to re-position the table via Excel VBA. If you come across a way to do it without additional infrastructure adaptations (Add-Ins which i can't seem to do at work here) please let me know .  

~ John
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.