Home » Blogs » Vishesh's blog

Export Excel Charts to PowerPoint

Vishesh's picture
Submitted by Vishesh on 28 August, 2010 - 19:51

 Here is the code to export all charts in a sheet to powerpoint. Just pass the worksheet object as function parameter and it will export all charts in it onto Powerpoint. Alternatively, you can also download the attached file and see how the code works.

 

Option Explicit
 
 
 
Function getPPPres() As PowerPoint.Presentation
 
    Dim PPApp As PowerPoint.Application
 
 
 
    'Reference instance of PowerPoint

    On Error Resume Next
 
    'Check whether PowerPoint is running

    Set PPApp = GetObject(, "PowerPoint.Application")
 
    If PPApp Is Nothing Then
 
        'PowerPoint is not running, create new instance

        Set PPApp = CreateObject("PowerPoint.Application")
 
        'For automation to work, PowerPoint must be visible

        PPApp.Visible = True
 
    End If
 
    On Error GoTo 0
 
 
 
    'Reference presentation and slide

    On Error Resume Next
 
    If PPApp.Windows.Count > 0 Then
 
        'There is at least one presentation

        'Use existing presentation

        Set getPPPres = PPApp.ActivePresentation
 
    Else
 
        'There are no presentations

        'Create New Presentation

        Set getPPPres = PPApp.Presentations.Add
 
    End If
 
    Set PPApp = Nothing
 
End Function
 
 
 
Function getNewSlide(PPPres As PowerPoint.Presentation) As PowerPoint.Slide
 
    Set getNewSlide = PPPres.Slides.Add(PPPres.Slides.Count + 1, ppLayoutBlank)
 
End Function
 
 
 
Sub ExportChartsToPPT(wksChartsFromSheet As Worksheet)
 
    Dim PPPres          As PowerPoint.Presentation
 
    Dim PPSlide         As PowerPoint.Slide
 
    Dim cht             As ChartObject
 
 
 
    If wksChartsFromSheet.ChartObjects.Count = 0 Then
 
        MsgBox "No Chart to Export to Powerpoint", vbInformation, ""
 
        Exit Sub
 
    End If
 
 
 
    Set PPPres = getPPPres
 
 
 
'    If PPPres.Slides.Count = 0 Then

'        Set PPSlide = getNewSlide(PPPres)

'    End If

 
 
    For Each cht In wksChartsFromSheet.ChartObjects
 
        Set PPSlide = getNewSlide(PPPres)
 
        cht.CopyPicture
 
        PPSlide.Select
 
        PPSlide.Shapes.Paste.Select
 
        PPSlide.Application.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
 
        PPSlide.Application.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
 
        PPSlide.Select
 
    Next cht
 
 
 
    Set cht = Nothing
 
    Set PPSlide = Nothing
 
    Set PPPres = Nothing
 
End Sub
 
 
 
Sub TestExecute()
 
    Call ExportChartsToPPT(Sheet2)
 
End Sub
 
 
 

 

 

Export Charts To PPT

AttachmentSize
ChartToPPT.xls35 KB
»

Linked Charts

Submitted by Anonymous on 10 February, 2012 - 13:19.

How can I copy charts from Excel to a power point presentation but without ultimately have the charts linked to the Excel file?

I mean, I can export them and have them in the slides, but I want to lose the link with the Excel, I need all the info to be in the slide itself as a part of it.

I have tried the link:=msoFalse etc and nothing seems to work.

Thanks

Regards

Manu

»

Me Too!!

Submitted by Anonymous on 26 March, 2012 - 18:20.

I've spent the day trying to export charts to Excel but without the data link back to Excel. I've read that it can be done, but can't seem to get it to work.

»

copy the chart, and paste

Submitted by Anonymous on 26 March, 2012 - 20:46.

copy the chart, and paste special as an enhanced metafile

»

Many thanks

Submitted by Anonymous on 5 March, 2012 - 10:01.

Many thanks for this very useful code. Probably just worth mentioning that the appropriate MS Powerpoint Object Library needs to be activated under Tools -> References. In my case (Excel 2007) it was inactive by default.

»
Vishesh's picture

Yes, my mistake. I should

Submitted by Vishesh on 27 March, 2012 - 14:59.

Yes, my mistake. I should have mentioned. However, here is the modified code that doesn't require the PowerPoint library to be added.

Function getPPPres() As Object
Dim PPApp As Object

'Reference instance of PowerPoint
On Error Resume Next
'Check whether PowerPoint is running
Set PPApp = GetObject(, "PowerPoint.Application")
If PPApp Is Nothing Then
'PowerPoint is not running, create new instance
Set PPApp = CreateObject("PowerPoint.Application")
'For automation to work, PowerPoint must be visible
PPApp.Visible = True
End If
On Error GoTo 0

'Reference presentation and slide
On Error Resume Next
If PPApp.Windows.Count > 0 Then
'There is at least one presentation
'Use existing presentation
Set getPPPres = PPApp.ActivePresentation
Else
'There are no presentations
'Create New Presentation
Set getPPPres = PPApp.Presentations.Add
End If
Set PPApp = Nothing
End Function

Function getNewSlide(PPPres As Object) As Object
Set getNewSlide = PPPres.Slides.Add(PPPres.Slides.Count + 1, 12)
End Function

Sub ExportChartsToPPT(wksChartsFromSheet As Worksheet)
Dim PPPres As Object
Dim PPSlide As Object
Dim cht As ChartObject

If wksChartsFromSheet.ChartObjects.Count = 0 Then
MsgBox "No Chart to Export to Powerpoint", vbInformation, ""
Exit Sub
End If

Set PPPres = getPPPres

' If PPPres.Slides.Count = 0 Then
' Set PPSlide = getNewSlide(PPPres)
' End If

For Each cht In wksChartsFromSheet.ChartObjects
Set PPSlide = getNewSlide(PPPres)
cht.CopyPicture
PPSlide.Select
PPSlide.Shapes.Paste.Select
PPSlide.Application.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
PPSlide.Application.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
PPSlide.Select
Next cht

Set cht = Nothing
Set PPSlide = Nothing
Set PPPres = Nothing
End Sub

»