To copy the charts from excel file to PowerPoint slide.
In this VBA Excel automation, we are copying the charts from excel macro file (.xlsm) and pasting them in the PowerPoint slide. We are keeping the source formatting for chart when it is pasted on PowerPoint slide.
- Add the library reference as shown in the image below
- Open the PowerPoint presentation using dialog box
- Loop through each worksheet in the macro workbook
- Copy the charts from each of the worksheet, and paste it in the PowerPoint slide
- Change the position from left and top. Change the height and width.
- When all the charts are copied, move to next worksheet
- Charts from new worksheet will be pasted on the next slide
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 obPptApp.Activate 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.PasteSpecial(ppPasteMetafilePicture, msoTrue) '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
Add the library reference in the macro file as shown below.
For demo purpose we have created a chart on the excel worksheet.We have created a command button on the worksheet as well. This command button is assigned the VBA code.
Once we click on the command button, we shall select the PowerPoint file (.pptx), we can see that in the image below.
After selecting the PowerPoint file, all the charts from each of the worksheets will be pasted on PowerPoint slide.
- We can create similar excel automation tools which can copy other objects like shapes or table from excel to PowerPoint slide.
- We can also define the path of source file in a variable, but by using dialog box it provides more flexibility in the code.
Post you may like