How do I copy an Excel chart into a PowerPoint slide?

Question

Here is the code I have so far.

'There is a bunch of other stuff defined.
' Just showing what I think are relevant definitions
Dim pptApp As PowerPoint.Application
Dim pptPres As PowerPoint.Presentation
Dim shp As PowerPoint.Shape
Dim sld As PowerPoint.Slide
Dim xlChrt As Excel.ChartObject

Set pptApp = CreateObject("PowerPoint.Application")

'This opens up my PowerPoint file
Set ppPres = pptApp.Presentations.Open(myPath & pptFile)

'This activates the worksheet where my chart is
Workbooks(wb2).Sheets("Sheet 1").Activate

    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.ChartArea.Copy

'I think that my copying works because after I run the module,
' I have the chart in my clipboard. 

'This takes me to slide 2 of my PowerPoint. 
Set sld = pptPres.Slides(2) 

sld.Shapes.Paste 'But nothing pastes to the slide

I am copying the chart because it is in my clipboard after the module runs.

I successfully reference the PowerPoint slide as later in the code, I edit text boxes on slide 2.

Change ppPres in the second Set statement to pptPres ... otherwise pptPres is not initialized anywhere. Suggestion is to always use Option Explicit in your code modules. — TechnoDabbler
That was a typo, sorry. It should actually be pptPres in my write-up — Bill_Richardson

TechnoDabbler TechnoDabbler · Accepted Answer · 2020-06-18T04:06:18

I believe your code should work if you correct the set pptPres statement. This example is a simplified example based on your code:

Option Explicit

Public Sub CopyChart()

    Const myPath = "c:\temp\"
    Const pptFile = "test.pptx"

    Dim pptApp As New PowerPoint.Application
    Dim pptPres As PowerPoint.Presentation
    Dim sld As PowerPoint.Slide

    ' Open the PowerPoint and reference the slide
    Set pptPres = pptApp.Presentations.Open(myPath & pptFile)
    Set sld = pptPres.Slides(2)

    ' Copy the Chart
    ActiveWorkbook.Sheets("Sheet 1").ChartObjects("Chart 1").Copy

    ' Paste it into the PowerPoint
    sld.Shapes.Paste

End Sub

And the result ... you can see the chart pasted onto slide 2:

UPDATED ANSWER

Chartsheets aren't as functional as embedded charts: https://docs.microsoft.com/en-us/office/vba/api/excel.chart(object)

Here is one option which is small variation of the above which works for chartsheets:

Option Explicit

Public Sub CopyChartSheet()

    Const myPath = "c:\temp\"
    Const pptFile = "test.pptx"

    Dim pptApp As New PowerPoint.Application
    Dim pptPres As PowerPoint.Presentation
    Dim myChart As Excel.Chart

    Dim sld As PowerPoint.Slide

    ' Open the PowerPoint and reference the slide
    Set pptPres = pptApp.Presentations.Open(myPath & pptFile)
    Set sld = pptPres.Slides(2)

    ' Copy the Chart
    Set myChart = ActiveWorkbook.Charts("Chart 1")
    myChart.CopyPicture

    ' Paste it into the PowerPoint
    sld.Shapes.Paste

End Sub

How do I copy an Excel chart into a PowerPoint slide?

1 Answers