Export Excel 2007 Charts to Powerpoint 2007 Slides

Description: This excel macro will export all the charts from the activesheet of excel workbook to a new powerpoint presentation. If there are 16 charts, then they'll be exported to 4 slides (4 Charts per slide.).


Instructions:
How to use:
  1. Copy the code.
  2. Open the excel file it will be used in.
  3. Press Alt + F11 to open the VBE.
  4. From the menu bar > Insert > Module (Standard module).
  5. Paste the code in the open white window.
  6. Press Alt + Q to close the VBE and return to excel.
 
Test the code:
  1. If you dont have a chart on the sheet, make one.
  2. Press Alt + F8.
  3. Select "Export Chart" > Run.



Code:

Sub Export_Excel_Charts_to_PowerPoint()
' http://www.excelitems.com
' http://www.openexcel.com
' By Ashish Jain

     'Requires a reference to the Microsoft PowerPoint Library via the Tools - Reference menu in the VBE
    Dim ppApp As PowerPoint.Application
    Dim ppSlide As PowerPoint.Slide
    Dim myChart As Excel.ChartObject
    Dim cNum As Integer
    
     'Look for existing instance
    On Error Resume Next
    Set ppApp = GetObject(, "PowerPoint.Application")
    On Error GoTo 0
    
     'Create new instance if no instance exists
    If ppApp Is Nothing Then Set ppApp = New PowerPoint.Application
     'Add a presentation if none exists
    If ppApp.Presentations.Count = 0 Then ppApp.Presentations.Add
    
     'Make the instance visible
    ppApp.Visible = True
    
     'Check that a slide exits, if it doesn't add 1 slide. Else use the last slide for the paste operation
    If ppApp.ActivePresentation.Slides.Count = 0 Then
        Set ppSlide = ppApp.ActivePresentation.Slides.Add(1, ppLayoutBlank)
    Else
        If AddSlidesToEnd Then
             'Appends slides to end of presentation and makes last slide active
            ppApp.ActivePresentation.Slides.Add ppApp.ActivePresentation.Slides.Count + 1, ppLayoutBlank
            ppApp.ActiveWindow.View.GotoSlide ppApp.ActivePresentation.Slides.Count
            Set ppSlide = ppApp.ActivePresentation.Slides(ppApp.ActivePresentation.Slides.Count)
        Else
             'Sets current slide to active slide
            Set ppSlide = ppApp.ActiveWindow.View.Slide
        End If
    End If

     'Options for Copy & Paste Ranges and Charts

        cNum = 11
        ActiveSheet.Activate
        For Each myChart In ActiveSheet.ChartObjects
            myChart.Select
            ActiveChart.ChartArea.Copy
            ppSlide.Shapes.PasteSpecial(DataType:=ppPasteMetafilePicture).Select

            'Adjust the Chart on Powerpoint Slide
            Select Case (cNum + 10) Mod 4
            Case 1
                ppApp.ActiveWindow.Selection.ShapeRange.Align msoAlignLefts, msoTrue
                ppApp.ActiveWindow.Selection.ShapeRange.Align msoAlignTops, msoTrue
            Case 2
                ppApp.ActiveWindow.Selection.ShapeRange.Align msoAlignRights, msoTrue
                ppApp.ActiveWindow.Selection.ShapeRange.Align msoAlignTops, msoTrue
            Case 3
                ppApp.ActiveWindow.Selection.ShapeRange.Align msoAlignLefts, msoTrue
                ppApp.ActiveWindow.Selection.ShapeRange.Align msoAlignBottoms, msoTrue
            Case 0
                ppApp.ActiveWindow.Selection.ShapeRange.Align msoAlignRights, msoTrue
                ppApp.ActiveWindow.Selection.ShapeRange.Align msoAlignBottoms, msoTrue
            End Select
            If (cNum + 10) Mod 4 = 0 Then
                Set ppSlide = ppApp.ActivePresentation.Slides.Add(ppApp.ActivePresentation.Slides.Count + 1, ppLayoutBlank)
                ppSlide.Select
            End If
            cNum = cNum + 1
        Next
    
    AppActivate ("Microsoft PowerPoint")
    Set ppSlide = Nothing
    Set ppApp = Nothing
    
End Sub


Comments

Anonymous said…
Nice and useful article. Thanks for sharing
Anonymous said…
You are a lifesaver. Thank you. Do you know how I can put the charts in an existing powerpoint and at a certain position on a slide?
logucwa said…
Hi,

I am unable to use the above code, while running its throwing a erron saying.. Compile error: User defined type not defined @ ppApp As PowerPoint.Application

Kindly help in this asap...
Ashish Jain said…
Please provide the reference to the Microsoft PowerPoint Library via the Tools - Reference menu in the VBE.

Regards
Ashish Jain :)
Anonymous said…
Ashish,

It works great!!! Really good!

The only thing that's happening is that the charts are being copied one over the other in PPT. It's all messy at the PPT side. Can I some way state how I wan them to pasted ( resize, reposition and reorder )?

thanks
Anonymous said…
Very nice would be if this code export the charts each slide seperately not 4 in 1.