asked on

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

ASKER CERTIFIED SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

John Carney

ASKER

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.

Rgonzo1971

Could you send a dummy?

John Carney

ASKER

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 Carney

ASKER

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 Carney

ASKER

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?

Rgonzo1971

then try

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

Open in new window

Regards

John Carney

ASKER

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

Rgonzo1971

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 Carney

ASKER

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

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

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