How to Copy Excel Chart in PowerPoint Slides using VBA Macro – Step by Step Tutorial
We need to copy several charts from Excel in Powerpoint slides. And you want to skip the manual part ? Yes, you are in right place and this simple vba macro automation will do the task for you. Supposedly if you’ve charts present in many worksheets in your Excel, we can easily copy each charts and it can be added as new Slides in Powerpoint.
Also Read: VBA Message Box Set No as Default Button
Add “Microsoft PowerPoint Object Library” through Tools->References to access PowerPoint related objects in VBA Macro.
VBA Code to Copy Excel Chart in PowerPoint Slides:
Copy the below code in the Excel containing Charts and Run it. You can see all the charts getting copied as new Slides in “Sample.pptx”. You will need to enable the macro first.
Dim ppApp As PowerPoint.Application Dim ppPres As PowerPoint.Presentation Dim ppSlide As PowerPoint.Slide Dim Xchart As Excel.ChartObject Dim currentSheet As Worksheet Dim slideCount As Long slideCount = 0 'Start the PowerPoint presentation - Look for existing instance On Error Resume Next Set ppApp = GetObject(, "PowerPoint.Application") On Error GoTo 0 If ppApp Is Nothing Then Set ppApp = New PowerPoint.Application ' Make it visible. ppApp.Visible = True End If On Error Resume Next 'can add the full file path here Set ppPres = ppApp.Presentations.Open("Sample.pptx") 'If no file is found, create new PPT file namely 'Sample.pptx' and save it If (ppPres Is Nothing) Then Set ppPres = ppApp.Presentations.Add(msoTrue) ppPres.SaveAs ("Sample.pptx") End If For Each currentSheet In ThisWorkbook.Sheets 'Locate Excel charts to paste into the new PowerPoint presentation For Each Xchart In currentSheet.ChartObjects slideCount = ppApp.ActivePresentation.Slides.Count ppApp.ActivePresentation.Slides.Add slideCount + 1, ppLayoutBlank ppApp.ActiveWindow.View.GotoSlide slideCount + 1 Set ppSlide = ppApp.ActivePresentation.Slides(slideCount + 1) 'Copy each Excel chart and paste it into PowerPoint as an Shapes currentSheet.Activate Xchart.Select ActiveChart.ChartArea.Copy 'paste the chart ppSlide.Shapes.PasteSpecial(DataType:=ppPasteShape).Select 'Adjust the slide position for each chart slide in PowerPoint. Note that you can adjust the values to position the chart on the slide to your liking ppApp.ActiveWindow.Selection.ShapeRange.Left = 100 ppApp.ActiveWindow.Selection.ShapeRange.Top = 150 ppPres.Save Next Next On Error Resume Next AppActivate ("Microsoft PowerPoint") Set ppSlide = Nothing Set ppApp = Nothing Set ppPres = Nothing
We majorly use 3 components here to copy Excel Chart in Powerpoint Slides in the below VBA Macro,
- VBA PasteSpecial
First lets understand the PowerPoint Application object and how to initialize it. You can check the code prior to For loop to sync it with description.
- It first checks for any existing Powerpoint instance and gets the handle to it as “PowerPoint.Application”
- If no instances are found, new Powerpoint instance is created and it is made visible. At this point, you can see the PowerPoint app running in your machine.
- Now try to open the file name ‘Sample.pptx’. If the file is present in the current folder of the Excel, it gets opened. And we get the object assigned to “PowerPoint.Presentation”.
- If no file is present, a new file is created and saved as “Sample.pptx” so that VBA macro can copy the Excel charts to this Powerpoint file.
We can access the chart objects defined as “Excel.ChartObject” and it can be derived from worksheet’s chart objects (currentSheet.ChartObjects ). We loop through each sheet and obtain the chart objects from it. Code Xchart.Select and ActiveChart.ChartArea.Copy does the copy magic for us.
Please re-read the For loop structure to understand this better.
Lets find out how to create new slide and activate it. And then we use VBA pastespecial to copy the charts from Excel to Powerpoint Slide.
- We first go to the end of powerpoint slide and add new slide using “ActivePresentation.Slides.Add” method. Please note that there are lot of layouts available for Powerpoint slide. And here, we are creating a blank layout using ‘ppLayoutBlank’.
- We have used “Shapes.PasteSpecial” method with argument as “ppPasteShape”. This way, charts gets pasted as charts and we can do any formatting related to charts like Pie charts, 2D-3D, color formatting, etc. You can explore the possible argument types and can play around with it.
- Finally we save the changes using Save method
We have successfully learnt how to copy Excel Chart in Powerpoint Slides through VBA Macro.