Share [Social9_Share]

Copy Chart From Excel To PowerPoint Using VBA

vba code to copy chart from excel to powerpoint

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:

  1. Open VBE from developer tab.
  2. Create a new module.
  3. Paste this code into that.
  4. Go to your worksheet.
  5. 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

 

Chandeep Chhabra

Popular Posts

© Goodly