To copy multiple charts from excel sheet to different slides in PowerPoint presentation.
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.
- Open the target .pptx file using dialog box
- Loop through each sheet in excel macro file
- Copy the charts from each sheet to slide 1
- Once all the charts of a sheet are copied to PowerPoint slide
- Change the position from top and left,and height and width of the chart
- Move to next worksheet
- For next worksheet, all the charts will be pasted to new slide
- Save the .pptx file and close it
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.
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]
In the sheet2, we had a trend chart [Refer image below]
In sheet3, we had a pie chart [Refer image below]
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.
- We can also modify this code to open a excel file and then copy and paste the charts from excel to PowerPoint slide
- We can also replace the functionality of dialog box by defining the path pf .pptx file in variables
Post you may like