I called over my friend, Puneet Gogia from www.excelchamps.com (once again) to teach us how can we use VBA to copy a Chart from Excel to a PowerPoint Presentation
Over to Puneet
Excel and PowerPoint are two closely related applications. Excel is the best place to analyze data, PowerPoint is the best way to present it. It’s like a step-1 and step-2 of a process, you create something (Excel) and then you present it (PowerPoint).
In this process of data analyzing and presenting, you need to copy your chart from Excel to PowerPoint which is sometimes can be a brainer.
There are several ways to copy a chart from Excel to PowerPoint, but if you want to do it quickly then using a VBA code can be very helpful.
In this post, you will learn to use VBA to copy a chart from excel and then add it to a PowerPoint slide.
Let’s get started.
What’s the Code?
Sub ChartX2P() Dim PowerPointApp As Object Dim myPresentation As Object Dim mySlide As Object Dim myShape As Object If ActiveChart Is Nothing Then MsgBox "Hey, please select a chart first." Exit Sub End If If PowerPointApp Is Nothing Then _ Set PowerPointApp = CreateObject(class:="PowerPoint.Application") On Error GoTo 0 Application.ScreenUpdating = False Set myPresentation = PowerPointApp.Presentations.Add Set mySlide = myPresentation.Slides.Add(1, 11) '11 = ppLayoutTitleOnly ActiveChart.ChartArea.Copy mySlide.Shapes.Paste Set myShape = mySlide.Shapes(mySlide.Shapes.Count) myShape.Left = 200 myShape.Top = 200 PowerPointApp.Visible = True PowerPointApp.Activate Application.CutCopyMode = False End Sub
How To Use It?
To use this code all you have to do:
- Open VBE from developer tab.
- Create a new module.
- Paste this code into that.
- Go to your worksheet.
- Select the chart and run the macro “ChartX2P”.
What Does it Do?
It will open PowerPoint, create a new slide and insert selected chart into that slide.
How To Customize It?
Alright, I know everyone out there has a different situation and they want to use it in a different way. So, I have listed here some customization tips for this code so that you can use it in your own way.
1. Export to an Existing File
If you want to use an existing file instead of a new one, please replace below old code with new one.
Old:
Set myPresentation = PowerPointApp.Presentations.Add
New:
Set myPresentation = PowerPointApp.Presentations.Open(Filename:="FileAddress")
2. Insert Chart as an Image
And, if you want to insert a chart as an image you need to make changes in the following code.
Old:
mySlide.Shapes.Paste
New:
mySlide.Shapes.PasteSpecial DataType = 2
Learn more about paste-special data type from here.
3. Change location of chart in Slide
If you want to change the location of the chart in the slide, you can change it from this part of the code.
myShape.Left = 200 myShape.Top = 200
This will code define the margin of the chart from left and top.
Wrap Up
Using this code you can export your charts from Excel to PowerPoint in a flash. And, the best part is you can use different formats to paste it.
Now, I want to hear from you. Do you have any other method which you use to copy a chart to PowerPoint? Please share with me in the comment section I would love to hear from you.
About The Author
Written By Puneet Gogia (ExcelChamps). You can find him online, tweeting about excel, on a running track, or sometimes hiking up a mountain. Check out his Excel Productivity Guide which is fully loaded with some most amazing excel tips.
Closing by Chandeep
Say a word of thanks to Puneet for taking out time for writing this post for us. If you have any questions, please feel free to post it in the comments section
