Let’s break this problem into a few different parts:
- Creating the PowerPoint Application
- Copying the Charts Pasting the
- Charts as the right format.
Now looking at your code, you are pretty much good to go on the first two. It’s pasting the object that is causing the problem. Let’s explore the different ways to paste.
USING THE EXECUTEMSO METHOD:
When we use this method it’s like we are right-clicking on the slide and pasting the object on to the slide. Now while this method is a completely valid way to paste, achieving this in VBA can be a little challenging. The reason why is because it is extremely volatile, and we must slow down our script to a snail’s pace!
To implement this method along with any of its different options, do the following:
'Create a new slide in the Presentation, set the layout to blank, and paste range on to the newly added slide.
Set PPTSlide = PPTPres.Slides.Add(1, ppLayoutBlank)
'WARNING THIS METHOD IS VERY VOLATILE, PAUSE THE APPLICATION TO SELECT THE SLIDE
For i = 1 To 5000: DoEvents: Next
PPTSlide.Select
'WARNING THIS METHOD IS VERY VOLATILE, PAUSE THE APPLICATION TO PASTE THE OBJECT
For i = 1 To 5000: DoEvents: Next
PPTApp.CommandBars.ExecuteMso "PasteSourceFormatting"
PPTApp.CommandBars.ReleaseFocus
'PASTE USING THE EXCUTEMSO METHOD - VERY VOLATILE
'Paste As Source Formatting
'PPTApp.CommandBars.ExecuteMso "PasteSourceFormatting"
'Paste as Destination Theme
'PPTApp.CommandBars.ExecuteMso "PasteDestinationTheme"
'Paste as Embedded Object
'PPTApp.CommandBars.ExecuteMso "PasteAsEmbedded"
'Paste Excel Table Source Formatting
'PPTApp.CommandBars.ExecuteMso "PasteExcelTableSourceFormatting"
'Paste Excel Table Destination Theme
'PPTApp.CommandBars.ExecuteMso "PasteExcelTableDestinationTableStyle"
Now if you look at my code, I had to pause it two different times to make sure it would work. This is because VBA will move way too fast otherwise and all that will happen is it will paste all the objects on the first slide! If we are only doing one paste we are usually safe without putting in the pauses, but the minute you want to go to a new slide put the pauses in!
USING THE REGULAR PASTE METHOD:
When we use this method, it’s like we are pressing Crtl+V and it will simply paste the object as a regular shape in PowerPoint. The regular shape means the default paste type in PowerPoint. Here is how we can implement a simple paste method:
'PASTE USING PASTE METHOD - NOT AS VOLATILE
'Use Paste method to Paste as Chart Object in PowerPoint
PPTSlide.Shapes.Paste
USING THE PASTE SPECIAL METHOD:
When we use this method it’s like we are pressing Ctrl+Alt+V on the keyboard and we get all sorts of different options of how to paste it. It ranges from a picture all the way to an embedded object that we can link back to the source workbook.
With the paste special method, sometimes we will still have to pause our scripts. The reason why is like the reason I mentioned above, VBA is volatile. Just because we copy it doesn’t mean it will make it to our clipboard. This problem can pop up and then disappear at the same time, so our best bet is to have a pause in our script to give VBA enough time to put the information in the clipboard. It usually doesn’t have to be a long pause but only a second or 2. Here is how we implement the paste special method with the different options we can use:
'PASTE USING PASTESPECIAL METHOD - NOT AS VOLATILE
'Paste as Bitmap
PPTSlide.Shapes.PasteSpecial DataType:=ppPasteBitmap
'Paste as Default
PPTSlide.Shapes.PasteSpecial DataType:=ppPasteDefault
'Paste as EnhancedMetafile
PPTSlide.Shapes.PasteSpecial DataType:=ppPasteEnhancedMetafile
'Paste as HTML - DOES NOT WORK WITH CHARTS
PPTSlide.Shapes.PasteSpecial DataType:=ppPasteHTML
'Paste as GIF
PPTSlide.Shapes.PasteSpecial DataType:=ppPasteGIF
'Paste as JPG
PPTSlide.Shapes.PasteSpecial DataType:=ppPasteJPG
'Paste as MetafilePicture
PPTSlide.Shapes.PasteSpecial DataType:=ppPasteMetafilePicture
'Paste as PNG
PPTSlide.Shapes.PasteSpecial DataType:=ppPastePNG
'Paste as Shape
PPTSlide.Shapes.PasteSpecial DataType:=ppPasteShape
'Paste as Shape, display it as an icon, change the icon label, and make it a linked icon.
PPTSlide.Shapes.PasteSpecial DataType:=ppPasteShape, DisplayAsIcon:=True, IconLabel:="Link to my Chart", Link:=msoTrue
'Paste as OLEObject and it is linked.
PPTSlide.Shapes.PasteSpecial DataType:=ppPasteOLEObject, Link:=msoFalse
With all that being said, if you paste an object as an OLEObject with a link most of the time the formatting comes over with it. Unless you have a special theme that only exist in Excel, that’s where you get into trouble. I ran into this problem when I was taking a chart from Excel To Word, but the Excel chart had a custom theme.
Here is your code, rewritten so that it will paste an object using the source format and setting the dimensions of it. I hope you don't mind me readjusting some of your code to make it a little more concise.
Sub PasteRangeIntoPowerPoint()
'Declare your variables
Dim oPPTApp As PowerPoint.Application
Dim oPPTFile As PowerPoint.Presentation
Dim oPPTShape As PowerPoint.Shape
Dim oPPTSlide As PowerPoint.Slide
Dim Rng As Range
'Get the PowerPoint Application, I am assuming it's already open.
Set oPPTApp = GetObject(, "PowerPoint.Application")
'Set a reference to the range you want to copy, and then copy it.
Set Rng = Worksheets("Sheet1").Range("B3:N9")
Rng.Copy
'Set a reference to the active presentation.
Set oPPTFile = oPPTApp.ActivePresentation
'Set a reference to the slide you want to paste it on.
Set oPPTSlide = oPPTFile.Slides(3)
'WARNING THIS METHOD IS VERY VOLATILE, PAUSE THE APPLICATION TO SELECT THE SLIDE
For i = 1 To 5000: DoEvents: Next
oPPTSlide.Select
'WARNING THIS METHOD IS VERY VOLATILE, PAUSE THE APPLICATION TO PASTE THE OBJECT
For i = 1 To 5000: DoEvents: Next
oPPTApp.CommandBars.ExecuteMso "PasteSourceFormatting"
oPPTApp.CommandBars.ReleaseFocus
For i = 1 To 5000: DoEvents: Next
'Set the dimensions of your shape.
With oPPTApp.ActiveWindow.Selection.ShapeRange
.Left = 35
.Top = 150
End With
End Sub
