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

Hi Rich,

Thanks for your help. This code is working absolutely fine but i need to copy specific charts to specific slides in powerpoint template.

For example i need to chart 1 (from chart tools >Layout >Chart name) to slide 5,chart 2 to slide 8 etc.

Right now this code is adding all the charts at the end of all slides.

Thanks

Answer 2

Dear Rich,

I read all previous conversations and codes which are very helpful by the way, many thanks for this.

I used your code :

Sub PushChartsToPPT()

    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 strPptTemplatePath As String

     Dim cht As Chart

     Dim ws As Worksheet

     Dim i As Long

strPptTemplatePath = "C:\Users\mdehauwe\REPORTING\template.potx"

      'Get the PowerPoint Application object:

    Set ppt = CreateObject("PowerPoint.Application")

     ppt.Visible = msoTrue

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

      '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

which works great but I have the same issue as S.Sharma  ;

For example i need to chart 1 (from chart tools >Layout >Chart name) to slide 5,chart 2 to slide 8 etc.

Also, is it possible to add two, three, four charts to one single slide?

Thanks a lot already for your time and precious advises

Answer 3

Hi rich007a,

I have implemented your code which works perfectly fine but my requirements are bit different. I have around 10 sheets and each sheet has few charts or tables.

Firstly I need to put all the charts in a sheet into a single slide. Ideally there would be four charts in each slide.

Secondly I need to copy tables instead of charts into some slides. e.g I need to pick the charts and tables in each sheets. I have made tables (from insert tables) and not the data range of cells. So like charts is there any objects for tables.

Third, I have a template for which I saw your code. But in my template there is a separator slide (which should contain only the title or subtitle) after every couple of slides. So I would like to define which sheet (all the charts in the sheet) will be copied to which slide.

Please let me know if it is possible and if yes help me with the code. Thanks.

Regards,

Jithin

Answer 4

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

Hi how to tweak this macro to copy only charts from worksheet.

Answer 5

Rich.. YOU ROCK MAN!! :-) This helped me tremendously!