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]

Try...

    Set ppShapeRange = ppSlide.Shapes.PasteSpecial(DataType:=ppPasteOLEObject, Link:=msoFalse)
    
    With ppShapeRange
        .Height = 183
        .Top = 86
        .Left = 633
        .Width = 270
    End With

The object variable ppShapeRange can be declared as follows...

Dim ppShapeRange As Object

For early binding, it can be declared as follows...

Dim ppShapeRange As PowerPoint.ShapeRange

Hope this helps!

 

[acc5149]

Unfortunately that didn't keep the source formatting. It changes all the colors and font sizes when it gets moved to PowerPoint. Also, the link didn't break in that code. I was able to break the link by adding the following:

With ppShapeRange
.LinkFormat.BreakLink
.Height = 183
.Top = 86
.Left = 633
.Width = 270
End With

But yea, still can't keep source formatting here. It looks like I can do one or the other. Either it gets pasted and breaks the link and reshapes/repositions, but doesn't keep the formatting. Or it keeps formatting, and I can't reference the shape anymore in PowerPoint in order to reposition it. It's driving me crazy!

I did find one work around, however it is not at all efficient. In the code that keeps the source formatting, I included a msgbox. It can literally say anything, but basically by calling that msgbox then closing it and continuing with the code, I'm able to reference the shape in powerpoint again. It goes as follows:

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

MsgBox (PPSlide.Shapes("Chart 41").Name)

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


Any idea why breaking up the code with the msgbox would then make it work correctly? The crazy part is that I just stumbled upon this because I wanted to make sure I was referencing the correct chart name in powerpoint. This code pastes the chart into powerpoint with source formatting, then goes back to excel with the msgbox, have to manually close it, then it will reposition the chart correctly in powerpoint. This is obviously very inefficient as I have code to copy and paste up to 40 charts, so I don't want to have to close 40 msgboxes to execute everything properly.

 

[Domenic]

That's strange. When I tested it with Excel 2010, it copied the chart, it kept the source formatting, and it wasn't linked. Try posting the complete code and we'll see if there's something there we can spot.

 

[acc5149]

Private Sub cmdExport_Click()


Dim objppt As Object
Dim ppshaperange As Object


Set objppt = CreateObject("PowerPoint.Application")
objppt.Visible = True


'Change the directory path and file name to the location
'of your document




objppt.Presentations.Open "File Location", ReadOnly:=msoTrue






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




Set PPSlide = ppPres.Slides(2)




If cbx2.Value = True Then
    Worksheets("Tab").Activate
    ActiveSheet.ChartObjects("Chart 41").Copy
    
    Set ppshaperange = PPSlide.Shapes.PasteSpecial(DataType:=ppPasteOLEObject, Link:=msoFalse)
   
    
    With ppshaperange
        .Height = 183
        .Top = 86
        .Left = 345
        .Width = 270
    End With
    
    
End If

That's how it is right now and it neither keeps source formatting nor breaks the link. No idea why, especially if it seems to be working on your end. I'm in Office 2016 if that changes anything.

Thanks again for helping out!

 

[Domenic]

Okay, it looks like the reason it's behaving differently for you is that you're using late binding. I'm not sure why that makes a difference, but for some reason it does. Therefore, I would suggest that you use early binding instead. First, set a reference to the PowerPoint object library...

Visual Basic Editor > Tools > Reference > select/check PowerPoint Object Library > OK

Then try the following code (change the path and filename accordingly)...

Private Sub cmdExport_Click()

Dim ppApp As PowerPoint.Application
Dim ppPres As PowerPoint.Presentation
Dim ppSlide As PowerPoint.Slide
Dim ppShapeRange As PowerPoint.ShapeRange


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
    ActiveSheet.ChartObjects("Chart 41").Copy
    Set ppShapeRange = ppSlide.Shapes.PasteSpecial(DataType:=ppPasteOLEObject, Link:=msoFalse)
    With ppShapeRange
        .Height = 183
        .Top = 86
        .Left = 345
        .Width = 270
    End With
End If

End Sub

Does this help?

 

[acc5149]

I think I'm really close now! I'm getting a new error:

Run-time error '424':
Object required

This happens on the With ppShapeRange part. It looks like it pasted into Power Point correctly (finally!!!), but now is having trouble referencing the shape in order to resize it. I put everything in there the same way you had it.

 

[acc5149]

It also looks as though while the link is broken and the source formatting is maintained, I lose the ability to change chart elements or colors or style. Is there another paste special function that will keep that functionality?

 

[Domenic]

For the error, it looks like Excel 2016 doesn't like the reference to the ShapeRange object when setting the properties. Try...

With ppShapeRange[COLOR=#ff0000](1)[/COLOR]

To edit the chart, try...

right-click the chart > select Macro-Enabled Worksheet Object > and then select either Edit or Open

Does this help?

 

[acc5149]

I don't see anything with regards to right-click > select macro-enabled worksheet object. It looks like this is copying the chart and pasting it as a picture, whereas I would want to link it to keep the chart functionality, then break the link so that if the chart changes in Excel with slicers or other filters, it won't change in the PowerPoint as well. Honestly, this has caused enough headaches, so we might just nix that capability for now haha.

As for set ppshaperange, I'm now having a new problem that I wasn't having before. I'm getting this bug:

Run-time error '-2147188160 (80048240)':
Shapes (unknown member): Invalid request. The specified data type is unavailable.

This is due to the ppPasteOLEObject part of the code. When I change it to ppPasteEnhancedMetaFile, it doesn't have that problem.