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.
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.1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162Dim ppApp As PowerPoint.ApplicationDim ppPres As PowerPoint.PresentationDim ppSlide As PowerPoint.SlideDim Xchart As Excel.ChartObjectDim currentSheet As WorksheetDim slideCount As LongslideCount = 0'Start the PowerPoint presentation - Look for existing instanceOn Error Resume NextSet ppApp = GetObject(, "PowerPoint.Application")On Error GoTo 0If ppApp Is Nothing ThenSet ppApp = New PowerPoint.Application' Make it visible.ppApp.Visible = TrueEnd IfOn Error Resume Next'can add the full file path hereSet ppPres = ppApp.Presentations.Open("Sample.pptx")'If no file is found, create new PPT file namely 'Sample.pptx' and save itIf (ppPres Is Nothing) ThenSet ppPres = ppApp.Presentations.Add(msoTrue)ppPres.SaveAs ("Sample.pptx")End IfFor Each currentSheet In ThisWorkbook.Sheets'Locate Excel charts to paste into the new PowerPoint presentationFor Each Xchart In currentSheet.ChartObjectsslideCount = ppApp.ActivePresentation.Slides.CountppApp.ActivePresentation.Slides.Add slideCount + 1, ppLayoutBlankppApp.ActiveWindow.View.GotoSlide slideCount + 1Set ppSlide = ppApp.ActivePresentation.Slides(slideCount + 1)'Copy each Excel chart and paste it into PowerPoint as an ShapescurrentSheet.ActivateXchart.SelectActiveChart.ChartArea.Copy'paste the chartppSlide.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 likingppApp.ActiveWindow.Selection.ShapeRange.Left = 100ppApp.ActiveWindow.Selection.ShapeRange.Top = 150ppPres.SaveNextNextOn Error Resume NextAppActivate ("Microsoft PowerPoint")Set ppSlide = NothingSet ppApp = NothingSet 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.
Copy Excel Chart in Powerpoint – Macro VBA pastespecial