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

They are simply called Chart 1, Chart 2 etc.

I am trying to follow what is happening in your macro but i cannot work it out. I can get it to add one chart but not multiple. Also i still cannot get the template to work. Sorry

Answer 2

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

Answer 3

This code works great!! I was wondering if there was a way to paste as a link? My attempts have been in vain thus far. 

PPSlide.Shapes.PasteSpecial(Link:=True).Select

Answer 4

This code works great!! I was wondering if there was a way to paste as a link? My attempts have been in vain thus far. 

PPSlide.Shapes.PasteSpecial(Link:=True).Select

Hi,

To paste a linked picture that will NOT be editable as a chart within PowerPoint, but will be updated when the chart is changed in your ALREADY SAVED Excel file, you can use:

Sub PastingLinkedPictures()

Dim shp As Shape

Dim sld As Slide

Set sld = ActiveWindow.View.Slide

Set shp = sld.Shapes(1) 'this would be whatever placeholder you want the chart to appear

ActiveWindow.View.PasteSpecial DataType:=ppPasteMetafilePicture, Link:=msoTrue

ActiveWindow.Selection.Cut

shp.Select

ActiveWindow.View.Paste

End Sub

The extra lines of code are there to move the pasted linked picture into a specific placeholder.

If you want to paste as a linked chart, I think you just need to paste (ActiveWindow.View.Paste).

Hope that helps.

Cheers

Rich

Answer 5

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