How to paste charts from Excel in the active PowerPoint slide?

[slavrenz]

I am trying to run through a sequence in Excel where I generate a chart and then copy and paste it into an active PowerPoint slide. The code I currently have (inherited from someone else) is pasted below. I know the change I need to make is on the "objPPT.ActiveWindow.View.GotoSlide" line, but I am having trouble with the syntax - can anyone help?

Here's the code:
Sub Chart2PPT()

Dim objPPT As Object
Dim objPrs As Object
Dim objSld As Object
Dim shtTemp As Object
Dim chtTemp As ChartObject
Dim objShape As Shape
Dim objGShape As Shape

Set objPPT = CreateObject("Powerpoint.application")
objPPT.Visible = True
objPPT.Presentations.Add
objPPT.ActiveWindow.ViewType = 1 'ppViewSlide

Dim PPGHeight As Double 'not using here...
Dim PPGWidth As Double

PPGWidth = 72 '720/10

Dim PPVPos(8) As Integer
Dim PPHPos(8) As Integer


PPVPos(1) = 12 * 72
PPVPos(2) = 8 * 72
PPVPos(3) = 12 * 72
PPVPos(4) = 8 * 72
PPVPos(5) = 4 * 72
PPVPos(6) = 0 * 72
PPVPos(7) = 4 * 72
PPVPos(8) = 0 * 72
PPHPos(1) = 0 * 72
PPHPos(2) = 0 * 72
PPHPos(3) = 6 * 72
PPHPos(4) = 6 * 72
PPHPos(5) = 0 * 72
PPHPos(6) = 0 * 72
PPHPos(7) = 6 * 72
PPHPos(8) = 6 * 72

' new slide
objPPT.ActiveWindow.View.GotoSlide Index:=objPPT.ActivePresentation.Slides.Add(Index:=objPPT.ActivePresentatio n.Slides.Count + 1, Layout:=12).SlideIndex

For i = 1 To 8

If Sheets("Control").Cells(34 + i, 9) = 1 Then

Sheets("Control").Cells(1, 2) = Sheets("Control").Cells(34 + i, 4)

Application.Run "ChangeQuery"

Application.Calculate

Set shtTemp = ThisWorkbook.Sheets("PCD")

Sheets("Control").ChartObjects("Chart 1").CopyPicture

objPPT.ActiveWindow.View.PasteSpecial DataType:=ppPasteEnhancedMetafile

With objPPT.ActiveWindow.Selection.ShapeRange
.Fill.Transparency = 0#
.Left = PPHPos(i)
.Top = PPVPos(i)
End With
End If
Next i

Set objPrs = Nothing
Set objPPT = Nothing
End Sub

[John Wilson]

Before you start you need to say which version of Office. Some versions select the pasted object and some don't so your method is likely to be unreliable. There's also a bug in 2013 for PasteSpecial as EMF.

Also the Array for Top values does not look correct 12*72 is 12 inches from the top of the slide (almost certainly off slide)

[slavrenz]

Before you start you need to say which version of Office. Some versions select the pasted object and some don't so your method is likely to be unreliable. There's also a bug in 2013 for PasteSpecial as EMF.

Also the Array for Top values does not look correct 12*72 is 12 inches from the top of the slide (almost certainly off slide)

Hi John,

Sorry, I am using Office 2013 Professional Plus x86 (on 64-bit Windows 8.1 Update 1). The values for the image placement are correct. What happened is that the previous user of this code was running this every single day for a set of images, each day's images pasted in a new slide. The PPT file was growing super large (since he was pasting as EMF), so he altered this macro to create giant PNG files that he would then paste into a separate PPT file.

So, yes, the images from Excel would be way bigger than the PPT slide created by the macro, but he just selected them all and cut/pasted onto a PPT slide within his ongoing file that was sized large enough to fit them all.

I hope this makes sense.

[slavrenz]

Seriously, nobody can help? I don't think that this is that difficult of a problem...