Saturday, March 2, 2013

Copy all Charts from activesheet and paste them in different slides of PPT

If you want to loop through all the charts on active-sheet and paste them on different slides of PPT. Try this macro -

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
Dim shp             As Shape

    Set PPApp = New PowerPoint.Application
    PPApp.Visible = True
    'create new ppt
    Set PPPres = PPApp.Presentations.Add
    'count no of slides
    'set layout of slide
    PPPres.ApplyTemplate Filename:="C:\Program Files\Microsoft Office\Document Themes 12\Median.thmx" ' if you want to apply theme
    'loop through all charts
    For Each shp In Sheets(1).Shapes
        If shp.Type = msoChart Then
             SlideCount = PPPres.Slides.Count
             Set PPSlide = PPPres.Slides.Add(SlideCount + 1, ppLayoutTitleOnly)
             'add header
             PPSlide.Shapes(1).TextFrame.TextRange.Text = shp.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
             shp.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
         End If
    Set PPSlide = Nothing
    Set PPPres = Nothing
    Set PPApp = Nothing
End Sub

No comments:

Post a Comment

Import data from SQL

Macro to import data from SQL using ADO connection string: Sub Import_data_from_SQL() ' Tools -> References -> Microsoft Active...