How to copy multiple charts from excel to different PowerPoint slide by VBA Excel

Copy multiple chart excel powerpoint VBA

Objective

To copy multiple charts from excel sheet to different slides in PowerPoint presentation.

Approach

Here we have got charts on 3 separate worksheets of a macro file (.xlsm) and we are copying the charts from each worksheets of this .xlsm file and pasting it on 3 separate slides of PowerPoint presentation.We are looping through each worksheet and then copying the charts from each sheet to an individual sheet in PowerPoint slide.

Steps

  1. Open the target .pptx file using dialog box
  2. Loop through each sheet in excel macro file
  3. Copy the charts from each sheet to slide 1
  4. Once all the charts of a sheet are copied to PowerPoint slide
  5. Change the position from top and left,and height and width of the chart
  6. Move to next worksheet
  7. For next worksheet, all the charts will be pasted to new slide
  8. Save the .pptx file and close it

 

Code

Sub CopyMultipleChartsFromExcelToPpt()
Application.ScreenUpdating = False

Dim obPptApp As PowerPoint.Application
Dim OpenPptDialogBox As Object
Dim MyChart As Chart
Dim MyShape As Object
Dim ws As Worksheet
Dim SlideIndex As Integer

Set obPptApp = CreateObject("PowerPoint.Application")
Set OpenPptDialogBox = obPptApp.FileDialog(msoFileDialogOpen)

'Open the target PPT using dialog box
If OpenPptDialogBox.Show = -1 Then
obPptApp.Presentations.Open (OpenPptDialogBox.SelectedItems(1))
End If

SlideIndex = 1
'We are copying the charts from 3 separate worksheets, and pasting it in a 3 separate slides
For Each ws In ThisWorkbook.Sheets
ws.Select
For Each shp In ActiveSheet.Shapes
    If shp.Type = msoChart Then
    shp.Copy
    Set MyShape = obPptApp.ActiveWindow.Presentation.Slides(SlideIndex).Shapes.Paste
    
'Change the position of shape and its height and width
  With MyShape
    .Left = 250
    .Top = 100
    .Height = 250
    .Width = 350
 End With
End If

Next shp
SlideIndex = SlideIndex + 1
Next ws
  
'Save and close the file
 obPptApp.ActivePresentation.Save
 obPptApp.Windows(1).Close
 
End Sub

 

First we shall add the libraries in VBE as shown below.

 

Copy multiple chart excel powerpoint VBA

 

In the image below we can see that we have added a command button, which is assigned the VBA macro code.Once we click on this command button, macro code shown above will execute.This .xlsm file had 3 sheets.

In the sheet1, we had a bar chart and command button. [Refer image below]

 

Copy multiple chart excel powerpoint VBA

 

In the sheet2, we had a trend chart  [Refer image below]

 

Copy multiple chart excel powerpoint VBA

 

In sheet3, we had a pie chart [Refer image below]

 

Copy multiple chart excel powerpoint VBA

 

Once we execute the code, all of these 3 charts from .xlsm file will be pasted in slide 1,2 and 3 respectively.We can see the 3 charts [bar chart, trend line chart , pie chart ] in the image below on left side in slide panel.

 

Copy multiple chart excel powerpoint VBA

 

Note

  1. We can also modify this code to open a excel file and then copy and paste the charts from excel to PowerPoint slide
  2. We can also replace the functionality of dialog box by defining the path pf .pptx file in variables


Reference

https://docs.microsoft.com/en-us/office/vba/api/powerpoint.shapes.paste
https://docs.microsoft.com/en-us/office/vba/api/office.filedialog.show

Post you may like

How to save or convert PowerPoint presentation (.pptx) as PDF file in VBA Excel