Export Multiple Charts from Excel Worksheet to PowerPoint using VBA

A PowerPoint (or PPT) presentation usually consists of many slides that show various types of data. The slides can have images, texts, art clips, videos etc. You can also show charts in the slides, imported from various sources, like an Excel worksheet.

You can copy and paste the charts to your PPT slides. However, if you have multiple charts across multiple worksheets in Excel, you can efficiently export all charts (or specific charts) in PPT, with the click of a button.

The Data for the Charts

I have some data in my Excel worksheet. The data is similar to one I have used in my previous post. You can add more data.

I am creating multiple charts with the above data, dynamically using VBA. You can create the charts, as you want.

The VBA Code

The procedure or code that exports these charts to PowerPoint is called when a user clicks a button. Therefore, insert an ActiveX button control in your worksheet.

Next, open the VBA editor.

To work with PowerPoint from Excel, you’ll have to first add a PowerPoint library reference to your VBA application.

1) From the top menu in your VBA editor, choose Tools – References. It will open the References dialog box.
2) In the dialog box, find Microsoft PowerPoint 12.0 Object Library and click OK. Or any version, which is installed in your computer. See the image.

Since, I have added a button control in my worksheet, I’ll write the code to call a procedure in the button’s click event.

Option Explicit

Private Sub CommandButton1_Click()
    exportCharts2Ppt
End Sub

Sub exportCharts2Ppt()
    
    ' Create a PowerPoint application object.
    Dim objPPT As PowerPoint.Application
    Set objPPT = New PowerPoint.Application
    objPPT.Visible = True           ' Make the PPT visible.
    objPPT.Activate
    
    ' Create a PowerPoint presentation object.
    Dim objPptPre As PowerPoint.Presentation
    Set objPptPre = objPPT.Presentations.Add
    
    ' We'll show different charts in different slides in our PowerPoint presentation.
    ' Therefore, create an object for PPT slides.
    Dim objPPTSlides As PowerPoint.Slide
    
    Dim iNdx As Integer     ' Index, or position of each slide.
    iNdx = 1
    
    Dim objChart As ChartObject
    Dim objWS As Worksheet
    
    For Each objWS In ActiveWorkbook.Worksheets     ' Loop through all the worksheets.
        For Each objChart In objWS.ChartObjects     ' Loop through all the Chart Objects.
           
            objChart.Chart.ChartArea.Copy       ' Copy all the charts to the Clipboard.
            ' Debug.Print objChart.Chart.Name
            
            Set objPPTSlides = objPptPre.Slides.Add(iNdx, ppLayoutBlank)       ' Create a new slide with a blank layout.
            objPPTSlides.Shapes.PasteSpecial ppPasteDefault, msoTrue           ' Extract the chart from the Clipboad and paste it.
            
            iNdx = iNdx + 1         ' Increment the slide index (or position).
        Next objChart
    Next objWS
    
End Sub

The macro has inline comments explaining the variables and the various objects that I have declared and used for exporting the charts to a PowerPoint application.

Inside the procedure exportCharts2Ppt(), I have first declared a PowerPoint “Application” object and initialized it. I am also making it visible, so you can see the creation of the slides, one by one.

Dim objPPT As PowerPoint.Application

The next objects are the PowerPoint Presentation object (to hold the slides with the charts) and the PowerPoint Slide.

Dim objPptPre As PowerPoint.Presentation
Dim objPPTSlides As PowerPoint.Slide

Using two For Each loops, the macro next iterates through all the worksheet in the file and looks for Charts. It copies the charts in a Clipboard, creates a PPT Slide each for the charts, extracts the chart one by one from the Clipboard and pastes it the slide.

For Each objWS In ActiveWorkbook.Worksheets
	For Each objChart In objWS.ChartObjects
		…
Next objChart
Next objWS

If you do everything right, then the above macro will create a PowerPoint application and add the charts in multiple slides.

Well, that’s it. Thanks.

← PreviousNext →