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

It worked that time - -thanks!!

:o)

Happy Christmas.

Cheers

Rich

Answer 2

Hi Rich,

I have a different active sheet that I would like to use this excel to PPT.

I would like to use the Original macro that you posted where it only take the image and it's not linked to the excel.

Situation:

I would like to run the macro in the ANY active sheet and i would just and hit "Alt + F8" to run the EXCEL to PPT. (I have over 30 charts (jpeg) on this active side along with it's data (V:AG).

Ideally I would like to have the 30 charts transfer as JPEG onto the PPT and also have the data next to it at random # rows in Column V:AG on the next slide OR Behind the chart.

So I plug in this macro and I got Error issue "Compile Error: User-defined type not defined"

So I uploaded the xls named "example1xls-ppt.xls" on SkyDrive https://skydrive.live.com/embed?cid=1B7A1F7F35D8435F&resid=1B7A1F7F35D8435F%21181&authkey=ADajXsXRZZmon8Q&em=2

Original Macro:

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

Any help is much appreciated.

Ian

Answer 3

Hi

Did you set the reference to the PowerPoint object library?

Cheers

Rich

Answer 4

yes, I have checked it 'Microsoft PowerPoint 14.0 Object Library'

Answer 5

yes, I have checked it 'Microsoft PowerPoint 14.0 Object Library'

Hi,

The error you see “Compile error: User-defined type not defined” is an indication that the reference was not set correctly.  Please make sure that the excel file that contains your code (which could be your Personal Macro Workbook if you want it to be available to work with any file) is active within the VBE when you set the reference.

The only other time I’ve seen this error is if you use the first version of Office 2007 without any service packs installed.  That doesn’t sound like you.

Otherwise, there might be a typo in your code (but you shouldn’t have if you copy from here, because it compiled ok for me).  Which line of code is highlighted after you OK the error message?  If it’s one of the Dim… As PowerPoint. … lines then it is definitely the reference to the ppt object library.

There is an alternative that avoids the need to set the reference; we can use Late Binding.  The disadvantage here is that you won't see the Intellisense pop-up text as you type your code.  You’ll miss that when you want to experiment with different PasteSpecial options, so if you can get the reference issue sorted, it will be well worth it.  Many programmers recommend converting to Late Binding to roll out your code (it is more robust), but if it’s only ever going to be you using it, I’d keep with Early Binding with the reference if you can.

Sub PushChartsToPPT()

'Set reference to 'Microsoft PowerPoint 1x.0 Object Library'

'in the VBE via Tools > References...

'

'No need for the reference if we use Late Binding:

Dim ppt As Object 'PowerPoint.Application

Dim pptPres As Object 'PowerPoint.Presentation

Dim pptSld As Object 'PowerPoint.Slide

Dim pptCL As Object 'PowerPoint.CustomLayout

Dim pptShp As Object '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

DoEvents   'give the new presentation a chance to open...

DoEvents   'found this was necessary if there was already

DoEvents   'a PowerPoint presentation open!

'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 = 7 Then Exit For   '7 = ppPlaceholderObject

Next pptShp

If pptShp Is Nothing Then Stop

cht.ChartArea.Copy

ppt.Activate

pptShp.Select

'ppt.Windows(1).View.Paste

ppt.Windows(1).View.PasteSpecial ppPasteEnhancedMetafile

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 = 7 Then Exit For   '7 = ppPlaceholderObject

        Next pptShp

Set cht = ws.ChartObjects(i).Chart

cht.ChartArea.Copy

ppt.Activate

pptShp.Select

'ppt.Windows(1).View.Paste

ppt.Windows(1).View.PasteSpecial ppPasteEnhancedMetafile

Next i

Next ws

End Sub

Hope that helps.

Cheers

Rich