macro to copy chart from excel to powerpoint

Question

is there a way i can get a macro to copy a chart from excel into power point? I know how to do it for tables within the actual sheet, but cannot seem to get the chart to copy as i cannot use cell ranges.

Thanks

Answer 1

I was doing it backwards :)

But now I have a new question.  I am working with a file that is a dashboard with around 15 charts.  I want the user to be able to pick which charts to export then change the data and be able to export the new charts - and I  want them to have a button that will allow them to pick the chart & then export - but when I assign a macro to a shape (as the button) - and then pick the chart - then click the button - it doesn't work because it moves the selection to the shape and takes it off the chart.  Is there an easier way to do that?

I am using 2013 version.

Answer 2

Hi,

OK, don't use a shape for your button.  Go to the Developer tab > Insert > Form Control > Button.  You can assign your macro to the new button.  The selection will not change when you click the button.

But the second issue is that now you say you want to select multiple charts (by holding down Ctrl while clicking them).  When you do that, you are actually selecting multiple Shape objects.  So you would need to do something like:

Dim ppt As PowerPoint.ApplicationDim pptPres As PowerPoint.PresentationDim pptCL As PowerPoint.CustomLayout

Sub PushSelectedChartsToPpt()

' Add a reference to 'Microsoft PowerPoint xx.0 Object Library'' via VBE > Tools > References.'    Dim shpRng As ShapeRange

    Dim shp As Shape

    On Error Resume Next

    Set shpRng = Application.Selection.ShapeRange

    On Error GoTo 0

    If Not shpRng Is Nothing Then                               'At least one shape is selected....

        For Each shp In shpRng

            If shp.HasChart Then

                Call PushChartToPpt(shp.Chart.ChartArea)

            End If

        Next shp

        shpRng.Select

    ElseIf Not ActiveChart Is Nothing Then                   'Only 1 Chart is selected...

        Call PushChartToPpt(ActiveChart.ChartArea)

    Else

        MsgBox "Please select at least one chart before running this macro"

        Exit Sub

    End If

'Delete these last 3 lines if you want to add subsequent charts to the existing presentation...    Set pptCL = Nothing

    Set pptPres = Nothing

    Set ppt = Nothing

End Sub

---

Sub PushChartToPpt(chtArea As ChartArea)

    Dim pptSld As PowerPoint.Slide

    Dim pptShp As PowerPoint.Shape

    If ppt Is Nothing Or pptPres Is Nothing Or pptCL Is Nothing Then

'Get the PowerPoint Application object:        Set ppt = CreateObject("PowerPoint.Application")

        ppt.Visible = msoTrue

        Set pptPres = ppt.Presentations.Add

'Get a Custom Layout:        For Each pptCL In pptPres.SlideMaster.CustomLayouts

            If pptCL.Name = "Title and Content" Then Exit For

        Next pptCL

    End If

'Get a slide:    Set pptSld = pptPres.Slides.AddSlide(pptPres.Slides.Count + 1, pptCL)

    pptSld.Select

'Get a Placeholder on the slide:    For Each pptShp In pptSld.Shapes.Placeholders

        If pptShp.PlaceholderFormat.Type = ppPlaceholderObject Then Exit For

    Next pptShp

    If pptShp Is Nothing Then Stop

'Copy the parsed ChartArea:

    chtArea.Copy

     'Paste into Ppt: ppt.Activate

    pptPres.Windows(1).Activate

    pptShp.Select

    pptPres.Windows(1).View.Paste

End Sub

Hope that helps.

Cheers

Rich

Answer 3

Thanks Rich.  And thank you for being so patient. I have copied the code into my file, however, it is not showing up when I hit F5 or when I try to run the macro -- I am using v 2013 -- I cannot figure out what is going on - It worked before - now not at all.  Any help would be appreciated.

Thanks!

Julie

Answer 4

Hi,

So you copied ALL of the code above (3 "Dims" and 2 procedures), then did Alt+F11, and pasted the code into a module?

And you cleared out any previous incarnations of the code that was there?

And you closed the VBE and made sure your file was saved as .xlsm?

And you hit Alt+F8 and you can't see PushSelectedChartsToPpt in the list?  (doesn't F5 show the Go To dialog?)

Cheers

Rich

Answer 5

It worked that time - -thanks!!