Copy paste charts from excel to Powerpoint slide in VBA | debugvba.com

Save charts from excel to ppt slide VBA

Objective

To copy the charts from excel file to PowerPoint slide.

Approach

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.

Steps

  1. Add the library reference as shown in the image below
  2. Open the PowerPoint presentation using dialog box
  3. Loop through each worksheet in the macro workbook
  4. Copy the charts from each of the worksheet, and paste it in the PowerPoint slide
  5. Change the position from left and top. Change the height and width.
  6. When all the charts are copied, move to next worksheet
  7. Charts from new worksheet will be pasted on the next slide

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

Save charts from excel to ppt slide VBA

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.

Save charts from excel to ppt slide VBA

Once we click on the command button, we shall select the PowerPoint file (.pptx), we can see that in the image below.

Save charts from excel to ppt slide VBA

After selecting the PowerPoint file, all the charts from each of the worksheets will be pasted on PowerPoint slide.

Save charts from excel to ppt slide VBA

Note

  1. We can create similar excel automation tools which can copy other objects like shapes or table from excel to PowerPoint slide.
  2. We can also define the path of source file in a variable, but by using dialog box it provides more flexibility in the code.

Reference

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

Post you may like

Copy excel ranges and paste in PowerPoint slide | debugvba.com