Copying and Pasting Graphs into Powerpoint

[acc5149]

Hey all,

I have a bunch of graphs that I want to export to a powerpoint presentation, then rearrange all the shapes within the slides. My original code was able to copy and paste everything, but I recently altered my syntax in order to keep source formatting. Now however, I'm unable to reference the shapes in the powerpoint slides in order to alter their size and position. Here is the original code, where cbx3 references a checkbox on a form to include the graph in the export:

If cbx3.Value = True Then
Worksheets("Inventory_Current").Activate
ActiveSheet.ChartObjects("Chart 41").Copy

Set ppshape = PPSlide.Shapes.PasteSpecial(DataType:=ppPasteEnhancedMetafile)
ppshape.Height = 183
ppshape.Top = 86
ppshape.Left = 633
ppshape.Width = 270

End If

Here's the new code that keeps source formatting:

If cbx3.Value = True Then
Worksheets("Inventory_Current").Activate
ActiveSheet.ChartObjects("Chart 41").Copy

With ppPres
.Windows(1).Activate
.Windows(1).View.GotoSlide 2
.Application.CommandBars.ExecuteMso ("PasteSourceFormatting")
End With

PPSlide.Shapes("Chart 41").Height = 183
PPSlide.Shapes("Chart 41").Top = 86
PPSlide.Shapes("Chart 41").Left = 633
PPSlide.Shapes("Chart 41").Width = 270

End if


In both pieces of code, I have:

Set ppApp = GetObject(, "Powerpoint.Application")
Set ppPres = ppApp.ActivePresentation
Set PPSlide = ppPres.Slides(2)


So my question is, how do I get the code to keep source formatting, but then also allow me to alter those shapes in powerpoint? Originally, I set the shape while I was pasting, and was able to reference it. Now with this different syntax, it fails when trying to reference the shapes to resize.


Bonus question: I have a mixture of charts and pivot charts. For some reason, I have to copypicture for the regular charts. Is there a way to copy those regularly? Basically I want to pull all charts in, keep source formatting, break the links, and keep the ability to alter the charts in powerpoint (i.e. chart elements, style, color, etc.)

Thanks in advance!!!!

Alex

 

[Domenic]

Okay, I've tested the following macro with Excel 2016...

Private Sub cmdExport_Click()

    Dim ppApp As PowerPoint.Application
    Dim ppPres As PowerPoint.Presentation
    Dim ppSlide As PowerPoint.Slide
    [COLOR=#ff0000]Dim ppShape As PowerPoint.Shape[/COLOR]
    
    Set ppApp = New PowerPoint.Application
    ppApp.Visible = True
    
    Set ppPres = ppApp.Presentations.Open("c:\users\domenic\desktop\presentation1.pptx", ReadOnly:=msoTrue)
    
    Set ppSlide = ppPres.Slides(2)
    
    If cbx2.Value = True Then
        Worksheets("Tab").Activate
        [COLOR=#ff0000]ActiveSheet.ChartObjects("Chart 41").Chart.ChartArea.Copy[/COLOR]
        [COLOR=#ff0000]Set ppShape = ppSlide.Shapes.PasteSpecial(DataType:=ppPasteOLEObject, Link:=msoFalse)(1)[/COLOR]
        [COLOR=#ff0000]With [/COLOR][COLOR=#ff0000]ppShape[/COLOR]
            .Height = 183
            .Top = 86
            .Left = 345
            .Width = 270
        End With
    End If

End Sub

Also, you can replace...

        Worksheets("Tab").Activate
        ActiveSheet.ChartObjects("Chart 41").Chart.ChartArea.Copy

with

        Worksheets("Tab").ChartObjects("Chart 41").Chart.ChartArea.Copy

Now you should be able to right-click the chart in your PowerPoint slide, etc.

Does this help?