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

This is certainly doable, but first:

1. Are your charts on their own Chart Sheets or are they embedded in a Worksheet?
2. Do you want the macro to live in Excel, and "push" the graphs to PowerPoint?  Or do you want the macro to live in PowerPoint and "pull" the graphs from Excel?

Cheers

Rich

Answer 2

Great. Hopefully it won't be to complicated.

1. The chart is on the worksheet. I have simply clicked to insert a chart. I can change this if needed
2. Preferably i would like the macro to be based on excel.

Answer 3

OK, well this one copies ALL charts from ALL Chart Sheets, and then ALL charts embedded in EACH worksheet.

You need to set a reference to 'Microsoft PowerPoint 12.0 Object Library' in the VBE via Tools > References...

Paste the code below into a code module in Excel.

Sub PushChartsToPPT()

'Set reference to 'Microsoft PowerPoint 12.0 Object Library''in the VBE via Tools > References...'    Dim ppt As PowerPoint.Application

    Dim pptPres As PowerPoint.Presentation

    Dim pptSld As PowerPoint.Slide

    Dim pptCL As PowerPoint.CustomLayout

    Dim pptShp As PowerPoint.Shape

    Dim cht As Chart

    Dim ws As Worksheet

    Dim i As Long

     '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

   'Copy ALL charts in Chart Sheets:    For Each cht In ActiveWorkbook.Charts

        Set pptSld = pptPres.Slides.AddSlide(pptPres.Slides.Count + 1, pptCL)

        pptSld.Select

        For Each pptShp In pptSld.Shapes.Placeholders

            If pptShp.PlaceholderFormat.Type = ppPlaceholderObject Then Exit For

        Next pptShp

        If pptShp Is Nothing Then Stop

        cht.ChartArea.Copy

        ppt.Activate

        pptShp.Select

        ppt.Windows(1).View.Paste

    Next cht

   'Copy ALL charts embedded in EACH WorkSheet:    For Each ws In ActiveWorkbook.Worksheets

        For i = 1 To ws.ChartObjects.Count

            Set pptSld = pptPres.Slides.AddSlide(pptPres.Slides.Count + 1, pptCL)

            pptSld.Select

            For Each pptShp In pptSld.Shapes.Placeholders

                If pptShp.PlaceholderFormat.Type = ppPlaceholderObject Then Exit For

            Next pptShp

            Set cht = ws.ChartObjects(i).Chart

            cht.ChartArea.Copy

            ppt.Activate

            pptShp.Select

            ppt.Windows(1).View.Paste

        Next i

    Next ws

End Sub

Hope that helps.

Cheers

Rich

Answer 4

Thats great. Thanks.

1. Is there a way to copy the charts into a template form instead of a new power point presentation
2. Can i reference the charts so they copy into a certain part of the form? When copying to word i was told to use bookmarks to show where to paste the information

Answer 5

Here’s how to use a template:

    Dim strPptTemplatePath As String

---

strPptTemplatePath = "C:\temp\My PowerPoint Template.potx"

'Get the PowerPoint Application object:

Set ppt = CreateObject("PowerPoint.Application")

ppt.Visible = msoTrue

Set pptPres = ppt.Presentations**.Open(strPptTemplatePath, untitled:=msoTrue)**

Yes, you can make charts copy into a certain part of the slide, but not like in Word.

There are two ways to do this.  Either you just paste the charts on to the slide and then reposition and re-size in VBA, or you set up / use a Custom Layout in your template – this would be my preference.

The code as is finds a Custom Layout named “Title and Content”.  That should have a Placeholder for objects (as well as a Title placeholder).

Then the code looks through all the placeholders on the new slide to find the Placeholder for objects.  If you have more than one Placeholder for objects, you’d need to do a test to see if the position is correct, for example:

'Find the right placeholder to paste into:

For Each pptShp In pptSld.Shapes.Placeholders

If pptShp.PlaceholderFormat.Type = ppPlaceholderObject Then

If pptShp.Left = 50 Then Exit For

  End If

Next pptShp

Do you need to put specific charts in a specific order of slides?  In which case, you’ll need to give me some examples of chart names (as shown on thethe right of the Chart Tools > Layout tab when your chart is selected), and where you want them to be pasted.

Cheers

Rich