Pages

Tuesday, June 28, 2011

Copy charts in excel and paste it to PowerPoint slide as picture

If you want to copy chart from excel and paste to PPT slide using VBA. Try this macro -

Method 1 -  Copy chart and paste as picture in PPT slide



Sub export_to_ppt()

'In tools Reference add Microsoft PowerPoint

Dim PPApp           As PowerPoint.Application
Dim PPPres          As PowerPoint.Presentation
Dim PPSlide         As PowerPoint.slide
Dim SlideCount      As Integer

    Set PPApp = New PowerPoint.Application
    PPApp.Visible = True
    'create new ppt
    Set PPPres = PPApp.Presentations.Add
    'count no of slides
    SlideCount = PPPres.Slides.Count
    'set layout of slide
    Set PPSlide = PPPres.Slides.Add(SlideCount + 1, ppLayoutTitleOnly)
    'add header
    PPSlide.Shapes(1).TextFrame.TextRange.Text = Sheets(1).ChartObjects("Chart 1").Chart.ChartTitle.Text ' add chart title as header
    'format header
    With PPSlide.Shapes(1).TextFrame.TextRange.Characters
        .Font.Size = 30
        .Font.Name = "Arial"
        .Font.Color = vbWhite
    End With
    
    With PPSlide.Shapes(1)
        .Fill.BackColor.RGB = RGB(79, 129, 189)
        .Height = 50
        .TextFrame.TextRange.ParagraphFormat.Alignment = ppAlignLeft ' left align the header text
    End With
   
    Sheets(1).ChartObjects("Chart 1").Chart.CopyPicture Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture ' copy chart
    PPApp.ActiveWindow.View.GotoSlide PPSlide.SlideIndex ' activate the slide no
    PPSlide.Shapes.Paste.Select ' paste chart
    'ALIGN THE chart
    PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
    PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
   
    Set PPSlide = Nothing
    Set PPPres = Nothing
    Set PPApp = Nothing
  
End Sub

Download Working File


Method 2 - Copy and paste as Linked Chart in PPT slide 


Sub export_to_ppt()

'In tools Reference add Microsoft PowerPoint

Dim PPApp           As PowerPoint.Application
Dim PPPres          As PowerPoint.Presentation
Dim PPSlide         As PowerPoint.slide
Dim SlideCount      As Integer

    Set PPApp = New PowerPoint.Application
    PPApp.Visible = True
    'create new ppt
    Set PPPres = PPApp.Presentations.Add
    'count no of slides
    SlideCount = PPPres.Slides.Count
    'set layout of slide
    PPPres.ApplyTemplate Filename:="C:\Program Files\Microsoft Office\Document Themes 12\Median.thmx" ' if you want to apply theme
    
    Set PPSlide = PPPres.Slides.Add(SlideCount + 1, ppLayoutTitleOnly)
    'add header
    PPSlide.Shapes(1).TextFrame.TextRange.Text = Sheets(1).ChartObjects("Chart 1").Chart.ChartTitle.Text ' add chart title as header
    'format header
    With PPSlide.Shapes(1).TextFrame.TextRange.Characters
        .Font.Size = 30
        .Font.Name = "Arial"
        .Font.Color = vbWhite
    End With
    
    With PPSlide.Shapes(1)
        .Fill.BackColor.RGB = RGB(79, 129, 189)
        .Height = 50
        .TextFrame.TextRange.ParagraphFormat.Alignment = ppAlignLeft ' left align the header text
    End With
   
    Sheets(1).ChartObjects("Chart 1").Chart.ChartArea.Copy ' copy chart
    PPApp.ActiveWindow.View.GotoSlide PPSlide.SlideIndex ' activate the slide no
    PPSlide.Shapes.Paste.Select ' paste chart
    'ALIGN THE chart
    PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
    PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
   
    
    Set PPSlide = Nothing
    Set PPPres = Nothing
    Set PPApp = Nothing
  
End Sub

1 comment:

  1. Thanks for this post. Just to add, besides pasting as an image, it is also possible to import a chart into powerpoint. Came across a post which explains the steps. Please check out http://www.goodly.co.in/copy-chart-from-excel-to-powerpoint-using-vba/

    ReplyDelete