How Do I Quickly Export My Charts to PowerPoint Slides?

Your articles on exporting Excel pivot tables to PowerPoint have come super handy but now I need to copy a bunch of charts… How do I tackle this?

Before you continue, you’ll need to keep a very important difference in mind – namely the one that VBA makes between chart sheets and chart objects

What is a chart sheet? – Any chart that is in a sheet on its own

What is a chart object? – Any chart that is embedded in a sheet

How do I export Chart Sheets?

The VBA code below will loop through all the chart sheets in the ActiveWorkbook, copy them and decide on which slide to paste them in PowerPoint on the basis of their position in the Excel workbook

Sub ExportFSCSlides()
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'written by Angelina Teneva, 2015
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dim PPApp As PowerPoint.Application
Dim PPpres As PowerPoint.Presentation
Dim pptx As String
pptx = ActiveWorkbook.Worksheets("calculated fields").Range("F2")
Dim Cht As Chart
Dim ChtObj As ChartObject
Dim i As Integer
'Create a PP application and make it visible
Set PPApp = New PowerPoint.Application
PPApp.Visible = msoCTrue
'Open the presentation you wish to copy to
Set PPpres = PPApp.Presentations.Open(pptx)
'************************************************************
'prevent PowerPoint 2013 from losing focus and returning
'"shapes (unknown member) invalid request. the specified data type is unavailable"
'- Run-time error -2147188160 (80048240):View (unknown member) error
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PPApp.Activate
PPApp.ActiveWindow.ViewType = ppViewNormal
PPApp.ActiveWindow.Panes(2).Activate 'standard ppt view
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'copy the chartsheets
For Each Cht In ActiveWorkbook.Charts
i = Cht.Index
Cht.ChartArea.Copy
Select Case i
Case 5: PPpres.Slides(2).Shapes.PasteSpecial ppPasteEnhancedMetafile
Case 6: PPpres.Slides(3).Shapes.PasteSpecial ppPasteEnhancedMetafile
Case 7: PPpres.Slides(15).Shapes.PasteSpecial ppPasteEnhancedMetafile
Case 8: PPpres.Slides(15).Shapes.PasteSpecial ppPasteEnhancedMetafile
End Select
Next Cht
PPpres.Save
End Sub

How do I export interactive Chart Objects?

The code below is especially handy if you need to deal with interactive charts that change depending on a value selected in a drop-down data validation menu

Sub ExportFSCSlides()
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'written by Angelina Teneva, 2015
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dim PPApp As PowerPoint.Application
Dim PPpres As PowerPoint.Presentation
Dim pptx As String
pptx = ActiveWorkbook.Worksheets("calculated fields").Range("F2")
Dim Cell As Range
Dim Country As Range
Dim ChtObj As ChartObject
Dim i As Integer
'Create a PP application and make it visible
Set PPApp = New PowerPoint.Application
PPApp.Visible = msoCTrue
'Open the presentation you wish to copy to
Set PPpres = PPApp.Presentations.Open(pptx)
'************************************************************
'prevent PowerPoint 2013 from losing focus and returning
'"shapes (unknown member) invalid request. the specified data type is unavailable"
'- Run-time error -2147188160 (80048240):View (unknown member) error
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PPApp.Activate
PPApp.ActiveWindow.ViewType = ppViewNormal
PPApp.ActiveWindow.Panes(2).Activate 'standard ppt view
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'copy HC charts from Dashboard tab (This is .ChartObjects collection and Object must always be activated first)
Worksheets("Dashboard").Activate
Set Country = ActiveSheet.Range("C8")
For Each Cell In ActiveSheet.Range("U17:AD17")
Country = Cell.Value
Set ChtObj = ActiveSheet.ChartObjects("HC")
ChtObj.Activate
ActiveChart.ChartArea.Copy
Select Case Country
Case "C": PPpres.Slides(4).Shapes.PasteSpecial ppPasteEnhancedMetafile
Case "F": PPpres.Slides(5).Shapes.PasteSpecial ppPasteEnhancedMetafile
Case "G": PPpres.Slides(6).Shapes.PasteSpecial ppPasteEnhancedMetafile
Case "GW": PPpres.Slides(7).Shapes.PasteSpecial ppPasteEnhancedMetafile
Case "IB": PPpres.Slides(8).Shapes.PasteSpecial ppPasteEnhancedMetafile
Case "IT": PPpres.Slides(9).Shapes.PasteSpecial ppPasteEnhancedMetafile
Case "M": PPpres.Slides(10).Shapes.PasteSpecial ppPasteEnhancedMetafile
Case "R": PPpres.Slides(11).Shapes.PasteSpecial ppPasteEnhancedMetafile
Case "U": PPpres.Slides(12).Shapes.PasteSpecial ppPasteEnhancedMetafile
Case "H": PPpres.Slides(13).Shapes.PasteSpecial ppPasteEnhancedMetafile
End Select
Next Cell
PPpres.Save
End Sub

Don’t forget that you’ll need the Microsoft PowerPoint Object Library enabled in the Excel VBA environment for the codes to work

Happy VBA coding!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.