To copy or export a chart from excel worksheet to PowerPoint slide in VBA Excel.
Here first we are opening a PowerPoint presentation using a dialog box, then we are copying a chart from the excel macro file, and paste it into the slide number 1 of opened presentation. We are also changing the alignment of chart from left and right, and additionally modifying height and width.
- Add the required libraries in VBE
- Open the PPT using dialog box
- Copy the chart from macro file
- Paste it in slide 1 of target ppt file
- Save and close the file
Sub CopyChartFromExcelToPpt() Application.ScreenUpdating = False Dim obPptApp As PowerPoint.Application Dim OpenPptDialogBox As Object Dim MyChart As Chart Dim MyShape As Object 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 'Copy the chart from excel macro file ActiveSheet.ChartObjects("Chart 1").Copy 'Paste the chart in slide 1 of PPT Set MyShape = obPptApp.ActiveWindow.Presentation.Slides(1).Shapes.Paste 'Change the position of shape and its height and width With MyShape .Left = 200 .Top = 80 .Height = 250 .Width = 350 End With 'Save and close the file obPptApp.ActivePresentation.Save obPptApp.Windows(1).Close End Sub
First we need to add library reference in the VBA editor as shown below.
In order to copy the chart from excel to PowerPoint we have added a command button.Once we click on it, a dialog box will open up,and we shall select a presentation(.pptx).
Once we click on the command button which is shown in the image above, a dialog box will pop up. We shall select the PowerPoint presentation by this dialog box. Refer the image below.
Once we select the .pptx file, “chart 1” will be copied from excel to PowerPoint presentation. In the below image we can see that chart which was existing on excel file, has been copied into slide 1 of PowerPoint presentation.
(1) Here we have used the FileDialog method, which can perform operations like “Open a file”, “Save As” a file , “Select Folder” etc.FileDialog property does not open the file, rather it allows us to select a particular file, and the name and the path of the selected file is stored in selecteditems(1) property.
(2) Expression “If OpenPptDialogBox.Show = -1 Then “, will open the dialog box, if we select the file then the value will be -1, if we don’t select a file , its value will be 0.
(3) Expression OpenPptDialogBox.SelectedItems(1) will have the selected file name along with its path. In “Open” method, we pass the path by using “OpenPptDialogBox.SelectedItems(1)”, it will open the .pptx file which is present at selected path.
- We can also select multiple PowerPoint files using the similar approach.
- We can also format, crop or change paste type in the code as well.
- We can also copy multiple charts from excel to PowerPoint slide.
- I have not added the code for error handling here to keep the length of the code short, you can add error handling code according to your need.
Post you may like