Copy or export chart from excel to Powerpoint in VBA Excel | debugvba.com

Copy chart excel powerpoint VBA

Objective

To copy or export a chart from excel worksheet to PowerPoint slide in VBA Excel.

Approach

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.

Steps

  1. Add the required libraries in VBE
  2. Open the PPT using dialog box
  3. Copy the chart from macro file
  4. Paste it in slide 1 of target ppt file
  5. Save and close the file

Code

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.

 

Copy chart excel powerpoint VBA

 

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).

 

Copy chart excel powerpoint VBA

 

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.

 

Copy chart excel powerpoint VBA

 

 

 

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.

 

Copy chart excel powerpoint VBA

 

 

(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.

 

Note

  1. We can also select multiple PowerPoint files using the similar approach.
  2. We can also format, crop or change paste type in the code as well.
  3. We can also copy multiple charts from excel to PowerPoint  slide.
  4. 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.

 

Reference

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

Post you may like 

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