macro to copy chart from excel to powerpoint

Dear Rich,

I have something similar to this and I have a question. Is there anyway to make my chart link to excel  instead of making it a picture so when I update any info. in excel the chart will also update along with it.

my concern is this section I think

'Copy the chart and paste it into the PowerPoint as a Metafile Picture

cht.Select

ActiveChart.ChartArea.Copy

activeSlide.Shapes.PasteSpecial(DataType:=ppPasteMetafilePicture).Select

Copy of my macro:

Sub CreatePowerPoint()

'Add a reference to the Microsoft PowerPoint Library by:

'1. Go to Tools in the VBA menu

'2. Click on Reference

'3. Scroll down to Microsoft PowerPoint X.0 Object Library, check the box, and press Okay

'First we declare the variables we will be using

Dim newPowerPoint As PowerPoint.Application

Dim activeSlide As PowerPoint.Slide

Dim cht As Excel.ChartObject

'Look for existing instance

On Error Resume Next

Set newPowerPoint = GetObject(, "PowerPoint.Application")

On Error GoTo 0

'Let's create a new PowerPoint

If newPowerPoint Is Nothing Then

Set newPowerPoint = New PowerPoint.Application

End If

'Make a presentation in PowerPoint

If newPowerPoint.Presentations.Count = 0 Then

newPowerPoint.Presentations.Add

End If

'Show the PowerPoint

newPowerPoint.Visible = True

'Loop through each chart in the Excel worksheet and paste them into the PowerPoint

For Each cht In ActiveSheet.ChartObjects

'Add a new slide where we will paste the chart

newPowerPoint.ActivePresentation.Slides.Add newPowerPoint.ActivePresentation.Slides.Count + 1, ppLayoutText

newPowerPoint.ActiveWindow.View.GotoSlide newPowerPoint.ActivePresentation.Slides.Count

Set activeSlide = newPowerPoint.ActivePresentation.Slides(newPowerPoint.ActivePresentation.Slides.Count)

'Copy the chart and paste it into the PowerPoint as a Metafile Picture

cht.Select

ActiveChart.ChartArea.Copy

activeSlide.Shapes.PasteSpecial(DataType:=ppPasteMetafilePicture).Select

'Set the title of the slide the same as the title of the chart

activeSlide.Shapes(1).TextFrame.TextRange.Text = cht.Chart.ChartTitle.Text

'Adjust the positioning of the Chart on Powerpoint Slide

newPowerPoint.ActiveWindow.Selection.ShapeRange.Left = 15

newPowerPoint.ActiveWindow.Selection.ShapeRange.Top = 125

activeSlide.Shapes(2).Width = 200

activeSlide.Shapes(2).Left = 505

'If the chart is the "US" consumption chart, then enter the appropriate comments

If InStr(activeSlide.Shapes(1).TextFrame.TextRange.Text, "US") Then

activeSlide.Shapes(2).TextFrame.TextRange.Text = Range("J7").Value & vbNewLine

activeSlide.Shapes(2).TextFrame.TextRange.InsertAfter (Range("J8").Value & vbNewLine)

'Else if the chart is the "Renewable" consumption chart, then enter the appropriate comments

ElseIf InStr(activeSlide.Shapes(1).TextFrame.TextRange.Text, "Renewable") Then

activeSlide.Shapes(2).TextFrame.TextRange.Text = Range("J27").Value & vbNewLine

activeSlide.Shapes(2).TextFrame.TextRange.InsertAfter (Range("J28").Value & vbNewLine)

activeSlide.Shapes(2).TextFrame.TextRange.InsertAfter (Range("J29").Value & vbNewLine)

End If

'Now let's change the font size of the callouts box

activeSlide.Shapes(2).TextFrame.TextRange.Font.Size = 16

Next

AppActivate ("Microsoft PowerPoint")

Set activeSlide = Nothing

Set newPowerPoint = Nothing

End Sub

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

What is the name of the Worksheet that the charts are embedded in?

What is the path of the template you want to use?

What is the name of the Custom Layout in the template that you want to be used for the new slides?

If there is more than one Placeholder for Objects in the Custom Layout, what is the position of the placeholder where you want each chart to go?

In PowerPoint, press Alt+F11 then Insert > Module.  Paste the code below and run it with your slide selected.  Please copy the text from the Immediate window in the PowerPoint VBE and paste it back here so I can see the layout of your slide/custom layout.

Sub PrintPlaceholderLocations()

    Dim sld As Slide

    Dim shp As Shape

    Set sld = ActiveWindow.View.Slide

    For Each shp In sld.Shapes.Placeholders

        Debug.Print "-------------"

        Debug.Print "Name: " & shp.Name

        Debug.Print "  PHF = " & shp.PlaceholderFormat.Type

        Debug.Print "  Top = " & shp.Top

        Debug.Print "  Left = " & shp.Left

        Debug.Print "  Height = " & shp.Height

        Debug.Print "  Width = " & shp.Width

    Next shp

End Sub

Cheers

Rich

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.

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