You can use VBA to streamline a lot of your day-to-day practices, eliminate boring tasks, and reduce tedium. A great example of a VBA project that would save a lot of time is report generation in Word or PowerPoint based on Excel content. Of course, such a project is more advanced than simply recording and replaying macros would be. I’ll use the small task of exporting an Excel chart to PowerPoint to illustrate such inter-application automation, and I’ll follow up soon with more intricate routines that accomplish more tasks. Export an Excel Chart to Word shows a similar example that exports an Excel chart to Word.
There are a number of things you need to understand:
- Excel’s Object Model
- PowerPoint’s (Word’s, etc.) Object Model
- Early and Late Binding, References, Libraries
We can learn all about Excel’s object model by recording macros and examining the code, as well as using tools like IntelliSense and the Object Browser in the VB Editor. And there’s always Google at our disposal. Search on “Excel VBA” and the particular keyword that has you stumped, or type the exact error message into the search box.
We learn about another application’s object model through VB Editor tools and of course Google. PowerPoint has Charts, Shapes, and Tables like Excel does; PowerPoint’s charts and shapes behave much like Excel’s, but its tables are much different. And PowerPoint has Presentations and Slides instead of Workbooks and Worksheets.
I’ll cover Early and Late Binding briefly, so that you can get up and running.
Early and Late Binding
When you write code, you are making use of code libraries, which contain the objects, methods, properties, and other keywords for the language you are using. For example, if you are writing Excel VBA code, you are using the Visual Basic for Applications library, the Microsoft Excel Object Library, the Microsoft Office Object Library, and others. In the VBA Editor, these libraries enable IntelliSense, which are the smart tooltips and dropdown boxes that help you get the code written quickly.
Early Binding
Early Binding is when you ensure that all libraries used in your project are explicitly identified when you are writing the code (at “design time”), by setting a reference to the library in your project (“binding” it to the project).
Early Binding enables design tools (IntelliSense, the Object Browser, etc.) in the VBA Editor to access an external library, so you can write your code more quickly.
IntelliSense ToolTip Showing Relevant Syntax
IntelliSense Dropdown Listing Appropriate Options
Early Binding also saves time by telling the system what library to use, instead of making the system look up all the possible libraries and guess which one to use. In practice, I haven’t found this delay to be significant.
If I use Early Binding, and share my program with others, they must have all of the referenced libraries on their computer, or the language (i.e., VBA) has to be able to figure out which library to use instead. For example, if I program in Excel 2016, the project will automatically contain references to Excel 2016 and Office 2106. VBA is smart enough to use the relevant versions of the Excel and Office libraries if someone uses the project in an older or newer version of Excel. If I set a reference to PowerPoint 2016, however, VBA isn’t so smart. It will successfully use a newer version of the PowerPoint library, but if the user has an older version, he will get a compile error, and nothing will run.
Late Binding
Late Binding is when you don’t explicitly bind libraries to your project at design time, but instead let your program try to figure it out later (at “run time”).
Late Binding doesn’t help you find information via IntelliSense or the Object Browser, so development may be slower.
If a needed library is not present on the user’s computer, and you have used Late Binding, you still get an error if the system can’t find the library. However, it’s not a compile error; it’s a run-time error, and you can write code to handle it gracefully.
I believe there are some libraries which do not work unless they are specifically bound to the project that uses them, but everything I’ve needed for routine VBA automation has worked fine with Late Binding.
What Should I Use?
There are tradeoffs between Early and Late Binding. IntelliSense and other guidance that is made available by Early Binding is invaluable. On the other hand, the compile errors due to missing references are catastrophic. Many developers will use Early Binding while developing their code, setting the references they need; once the code is ready to ship, they switch to Late Binding, removing the references and making the other small changes to avoid compile errors in the code.
The Example Project
Here is a simple data set and chart in Excel.
Here is a blank slide in PowerPoint.
We’re going to export that chart from Excel onto that PowerPoint slide.
Early Binding – Setting References
To set a reference to an external library, go to the VB Editor and make sure the project you’re working on is the active project. Go to the Tools menu > References, and the References dialog appears.
This dialog lists each open workbook and add-in, as well as all libraries installed on your system. This includes many that are not appropriate for VBA development, and using these may result in spectacular system failures. But any of them may one day come in handy. For example, long ago I used the WinAmp library (remember that? probably not) to build a UserForm-driven music utility, in which I could set up playlists and play music files in Excel.
Several references are added by default: Visual Basic for Applications, Microsoft Excel 2016 Object Library, OLE Automation, and Microsoft Office 2016 Object Library; if you add a UserForm to your project, Microsoft Forms 2.0 Object Library is also added automatically. In this view you can see that the Microsoft PowerPoint 16.0 Object Library has also been checked. If it’s not checked and you haven’t used it recently, you’ll have to scroll down until you find it, then check it.
These library references are only set for the active VB project. Other projects may have different libraries referenced. For example, I used two workbooks while working on this tutorial: one with the PowerPoint library checked for Early Binding, the other without for Late Binding.
Outline of Our Procedure
Here is the entire VBA procedure that will export the active Excel chart to PowerPoint. The code is organized like this:
- We have declared several object variables at the top of the procedure.
- In Early Binding, these objects are declared as PowerPoint objects.
- In Late Binding, these objects are declared as generic objects.
- We prevent an error if the user has not selected a chart.
- We figure out where to paste the chart.
- We look first for a running instance of PowerPoint.
- If PowerPoint is not running, we start it up.
- We look for the active presentation.
- If there is no active presentation, we create one and insert a slide with the desired blank layout.
- In Early Binding, we use the PowerPoint named constant ppLayoutBlank.
- In Late Binding, we use its numerical equivalent (12).
- If there is no active presentation, we create one and insert a slide with the desired blank layout.
- We look for the active slide.
- If we can’t find the active slide, we insert one.
- (In older versions of PowerPoint, a new presentation started with no slides.)
- We look first for a running instance of PowerPoint.
- We copy the active chart.
- We paste the chart as a shape object in PowerPoint (it’s still a real chart).
- We align the chart/shape on the slide.
Early Binding – The VBA Code
Option Explicit
Sub SimpleActiveChartToPowerPoint_EarlyBinding()
Dim pptApp As PowerPoint.Application
Dim pptPres As PowerPoint.Presentation
Dim pptSlide As PowerPoint.Slide
Dim pptShape As PowerPoint.Shape
Dim pptShpRng As PowerPoint.ShapeRange
Dim lActiveSlideNo As Long
' bail out if no active chart
If ActiveChart Is Nothing Then
MsgBox "Select a chart and try again!", vbExclamation, "Export Chart To PowerPoint"
Exit Sub
End If
' figure out what slide to paste on
On Error Resume Next
Set pptApp = GetObject(, "PowerPoint.Application")
On Error Resume Next
If pptApp Is Nothing Then
Set pptApp = CreateObject("PowerPoint.Application")
Set pptPres = pptApp.Presentations.Add
Set pptSlide = pptPres.Slides.Add(1, ppLayoutBlank)
Else
If pptApp.Presentations.Count > 0 Then
Set pptPres = pptApp.ActivePresentation
If pptPres.Slides.Count > 0 Then
lActiveSlideNo = pptApp.ActiveWindow.View.Slide.SlideIndex
Set pptSlide = pptPres.Slides(lActiveSlideNo)
Else
Set pptSlide = pptPres.Slides.Add(1, ppLayoutBlank)
End If
Else
Set pptPres = pptApp.Presentations.Add
Set pptSlide = pptPres.Slides.Add(1, ppLayoutBlank)
End If
End If
' copy chart
ActiveChart.ChartArea.Copy
' paste chart
With pptSlide
.Shapes.Paste
Set pptShape = .Shapes(.Shapes.Count)
Set pptShpRng = .Shapes.Range(pptShape.Name)
End With
' align shape on slide
With pptShpRng
.Align msoAlignCenters, True ' left-right
.Align msoAlignMiddles, True ' top-bottom
End With
End SubLate Binding – The VBA Code
Option Explicit
Sub SimpleActiveChartToPowerPoint_LateBinding()
Dim pptApp As Object ' PowerPoint.Application
Dim pptPres As Object ' PowerPoint.Presentation
Dim pptSlide As Object ' PowerPoint.Slide
Dim pptShape As Object ' PowerPoint.Shape
Dim pptShpRng As Object ' PowerPoint.ShapeRange
Dim lActiveSlideNo As Long
' bail out if no active chart
If ActiveChart Is Nothing Then
MsgBox "Select a chart and try again!", vbExclamation, "Export Chart To PowerPoint"
Exit Sub
End If
' figure out what slide to paste on
On Error Resume Next
Set pptApp = GetObject(, "PowerPoint.Application")
On Error Resume Next
If pptApp Is Nothing Then
Set pptApp = CreateObject("PowerPoint.Application")
Set pptPres = pptApp.Presentations.Add
Set pptSlide = pptPres.Slides.Add(1, 12) ' 12=ppLayoutBlank
Else
If pptApp.Presentations.Count > 0 Then
Set pptPres = pptApp.ActivePresentation
If pptPres.Slides.Count > 0 Then
lActiveSlideNo = pptApp.ActiveWindow.View.Slide.SlideIndex
Set pptSlide = pptPres.Slides(lActiveSlideNo)
Else
Set pptSlide = pptPres.Slides.Add(1, 12) ' 12=ppLayoutBlank
End If
Else
Set pptPres = pptApp.Presentations.Add
Set pptSlide = pptPres.Slides.Add(1, 12) ' 12=ppLayoutBlank
End If
End If
' copy chart
ActiveChart.ChartArea.Copy
' paste chart
With pptSlide
.Shapes.Paste
Set pptShape = .Shapes(.Shapes.Count)
Set pptShpRng = .Shapes.Range(pptShape.Name)
End With
' align shape on slide
With pptShpRng
.Align msoAlignCenters, True ' left-right
.Align msoAlignMiddles, True ' top-bottom
End With
End SubBits and Pieces
The Objects
We need to use the PowerPoint Application (we use the Excel one all the time in Excel VBA, but usually get away with ignoring it), plus a presentation, and a slide. The chart is pasted as a Shape, and we make it a member of a ShapeRange to align it within the slide. I’ve shown the Early Binding PowerPoint object types below; in Late Binding, these are all plain old Objects.
Dim pptApp As PowerPoint.Application
Dim pptPres As PowerPoint.Presentation
Dim pptSlide As PowerPoint.Slide
Dim pptShape As PowerPoint.Shape
Dim pptShpRng As PowerPoint.ShapeRangeUser-Proof the Code
Here we test that the user has selected the chart. If he has not, we pop up a message reminding him to select one next time.
If ActiveChart Is Nothing Then
MsgBox "Select a chart and try again!", vbExclamation, "Export Chart To PowerPoint"
Exit Sub
End If
Get the PowerPoint Application
We use GetObject to find the running instance of PowerPoint. We wrap it in On Error statements so we don’t get an error if it’s not running. If it’s not running, pptApp is in fact Nothing, so we use CreateObject to start it up.
On Error Resume Next
Set pptApp = GetObject(, "PowerPoint.Application")
On Error Resume Next
If pptApp Is Nothing Then
Set pptApp = CreateObject("PowerPoint.Application")PowerPoint is a bit funny here, since it only supports one running instance at a time. We could be lazy and simply use CreateObject, and it will fetch the running instance or fire up a new one as needed. But what I have used is generally the proper way to do it, and it doesn’t hurt if we go through all the motions even if we don’t always need to.
At this point, it would be wise to test that we were able to start up PowerPoint. If we couldn’t start PowerPoint, for example, if PowerPoint were not installed on the computer, we would have to abandon the procedure without trying to use the various PowerPoint objects later in the code. We should also alert the user so he could rectify the situation.
Get the PowerPoint Presentation and Slide
ActivePresentation is probably obvious enough. If there is one, continue; otherwise create one first.
ActiveSlide is a tough one, because even though such an object would be useful, there is no ActiveSlide object in PowerPoint. The first thing you must check is whether the active presentation has any slides. Sounds dumb, but in prior versions of PowerPoint, when you created a new presentation, it started out with no slides, and you had to click in the window to insert the first slide. Presentations.Add also inserts a new presentation with no slides. So we need to add a slide to a new presentation and to an existing presentation that had no slides yet.
If the active presentation has at least one slide, we find the active slide with a convoluted line of code:
pptApp.ActiveWindow.View.Slide.SlideIndexI think this line of code assumes certain views, like Normal view (with Slide pane, Slide Sorter pane, and Notes pane) and Slide view (Slide pane only). A more rigorous procedure would check for this to make sure to avoid the associated error.
Again, we should check that we did successfully end up with a presentation and a slide, to avoid errors later in the code.
Copy, Paste, and Align
Copying is straightforward: we copy the chart’s chart area. We could instead copy the chart’s parent ChartObject, which works exactly the same for an embedded chart, but would crash for a chart sheet.
We use pptSlide.Shapes.Paste to paste the chart as a shape (a chart’s ChartObject is the Excel Shape that contains the chart), as a member of the slides collection of Shapes.
To align the Shape, we first have to define the shape as the latest Shape added to the Slide, and then define a ShapeRange, which is a funny way to say a bunch of Shapes which is different than a collection of Shapes. Finally we align this shape range in the center middle of the slide.
Set pptShape = pptSlide.Shapes(.Shapes.Count)
Set pptShpRng = pptSlide.Shapes.Range(pptShape.Name)
pptShpRng.Align msoAlignCenters, True ' left-right
pptShpRng.Align msoAlignMiddles, True ' top-bottomWe could get into trouble here if the chart was not successfully copied or pasted.
The Result
Whether we use Early or Late Binding, the result is the same. Below is our formerly blank PowerPoint slide, containing our Excel chart.
Summary
This tutorial showed how to use VBA to export an Excel chart to PowerPoint. Both Early and Late Binding were demonstrated, and reasons were provided for preferring Late Binding for most cases.
Export an Excel Chart to Word shows a similar approaches for exporting our Excel chart to Word.
Follow-up articles will cover exporting other content (i.e., worksheet ranges (“tables”)) to PowerPoint or Word, and exporting multiple charts in one procedure, which will really reduce your daily tedium. These articles will also describe some of the code errors you may encounter and suggest workarounds, many gleaned through hours of mind-numbing trial-and-error.
Yury Suturin says
Great piece of code, thanks for putting it here
Jeff Coulson says
Very helpful code, the icing on the cake …I would love to be able to call a Presentation with the default Theme|Layout on the slides.
Jon Peltier says
Jeffwhat exactly would you like to happen?
Jeff Coulson says
Is there a method to place the charts into a Power Point Slide with a particular Theme?
Jon Peltier says
Jeff –
Very good question, for which I don’t have a satisfying answer. A lot of stuff that you can do in PowerPoint’s user interface is not directly available in the VBA object model. It’s always been and will always be this way.
You can use :
where CommandName is one of these (used in quotes):
I have not tried implementing any of this. I can tell already that it’s not as flexible as using straight VBA, because you’ll have to make sure the appropriate PowerPoint slide is active, rather than being able to paste into the shapes collection of an arbitrary slide, which is not necessarily the active slide.
Jeff Coulson says
Thank you Jon: I echo your “not as flexible” … I’ve hunted for and tried different sets of code trying to accomplish this but all attempts have really been unsuccessful. Satisfying to have confirmation from an MVP that there is no ideal solution for this. Hey Microsoft Guys … can you hear this??
Ronald Moore says
What if the charts were all by themselves on a worksheet and run the code to call for that worksheet and run. This code only works if you highlight the chart.
I want the code to run the charts on a certain worksheet.
Jon Peltier says
Hi Ronald –
I have a draft of a follow-up article which goes into greater detail on larger projects, with the ability to export multiple charts to a single slide, or multiple charts to multiple slides, or multiple charts from multiple workbooks or from specified workbooks. It will be one of the next few articles I post, so hopefully it will be done within a few weeks.
Alex says
“I have a draft of a follow-up article which goes into greater detail on larger projects, with the ability to export multiple charts to a single slide, or multiple charts to multiple slides, or multiple charts from multiple workbooks or from specified workbooks. It will be one of the next few articles I post, so hopefully it will be done within a few weeks.”
This would be extremely helpful!
Steven says
Hi Alex,
You mention that you have the ability to export multiple charts to a single slide. Does anyone know how to get 4 charts onto 1 PowerPoint slide using VBA? I currently have a workbook containing multiple worksheets, and each worksheet has a chart that I need to paste in to PowerPoint. I am able to paste each chart on to its own slide, however I would like to be able to paste anywhere from 2 to 4 charts to each slide depending on the number of charts (so i need it to be dynamic).
Any help would be greatly appreciated.
Jon Peltier says
Steven –
Set up a counter, which increments for each chart pasted onto a slide. When the counter reaches the number of slides you want on a slide, insert a new slide and reset the counter.
It would look something like this:
You might want to use a more detailed scheme to arrange charts. You may also want to pass in NPerSlide instead of hardcoding it.
Steven says
Hi Jon,
You are a god send – The code works really well, I cant thank you enough. I did have 2 additional questions though – A) How can I paste the charts in to PP, using the same order that they are currently in in my workbook and B) How can I resize my charts?
Cheers – I owe you a cold one!!
Jon Peltier says
Steven –
Easy one first. You can resize the charts in Excel before you copy them, or in PowerPoint after you paste them.
Before copying: change this
to this:
After pasting: after this line
insert these:
Jon Peltier says
Harder one: Charts are listed in Excel by Z order, from back to front. You need to select each chart and use Send Backward or Bring Formard to nudge them into position, or go to the Home tab, click Find & Select at the far right, and choose Selection Pane, and adjust the order of charts in the selection pane. Then they will be copied in front to back order.
An interesting thing is that you can use a slightly different approach, select the charts first, then use code to loop not through the chart objects in the sheet but through the selected objects. The charts will be looped in the order in which you have selected them. But you can only select the charts on the active sheet, so you have to run the code once per worksheet.
Note that this pastes charts into PowerPoint first column top and bottom, then second column top and bottom. Switch .Left and .Right for Case 1 and Case 2 to paste first row left and right, then second row left and right.
Kirsten says
Hi Jon,
Thanks for the great bit of code; this in theory accomplishes exactly what I would like to do. I am however having some trouble executing it in Excel 2016. Can you help?
The code runs without errors but does nothing. If I comment out the first instance of ‘On Error Resume Next, I get an error on the following line:
Set pptApp = GetObject(, “PowerPoint.Application”)
The error message reads:
Run-time error ‘-2147417856 (80010100)’:
Automation error
System call failed.
I have ticked the boxes for Excel, Office, and PowerPoint in the VBA Project References.
Any ideas what I can try to get the code running?
Thanks!
Kirsten
Jon Peltier says
Hi Kirsten –
Is this Excel 2016 for Windows or Mac? This code may have issues on the Mac because of strict sandboxing of applications.
Is PowerPoint running before you run the code? This shouldn’t matter, but you never know.
If you comment out the GetObject line and just rely on CreateObject, does it work?
Kirsten says
Hi Jon –
Thanks for the reply. I am using Windows. I tried both the GetObject and CreateObject; both were giving similar errors.
However – I have resolved the issue now, and it was just as simple as MS Office needing to be updated (under File > Office Account > Update Options > Update Now). Everything works fine after that.
VBA code works like a charm. Thanks!
Kirsten
Jon Peltier says
Kristen –
That’s like turning the computer off and on again. Glad you got it working.
prahllad says
I am looking for VBA code opening the predefined template ( with company logo and size of ppt) and copy multiple ranges from different sheet and past in ppt
Possible to dynamically set slide number based on cell value or input value.
Jon Peltier says
Prahlad –
While this is an extension of the techniques in this tutorial, it is much more involved. You need a way to identify and open the template, all the pieces of Excel content, and all of the places in the PowerPoint presentation where they need to go.
Jay Arthur says
Code works fine in PC. Does not work in Mac Excel 2016. Will open PPT or Word, but then both apps crash.
Jon Peltier says
Jay –
The code worked fine in Mac Office 2011, but for Mac Office 2016, Microsoft started yielding to Apple’s requirements that everything be locked down, and programs couldn’t (easily) manipulate other programs. Instead of putting Office into a protective sandbox, it seems they put each Office app into its own sandbox. I simply haven’t had the time or especially patience to figure it out.
Erik says
I wanted to change things up a bit and copy the chart as a picture:
ActiveChart.CopyPicture(xlScreen,xlPicture,xlPrinter)
But I’m told that I have a compile error:
Expected: =
How would I actually copy the chart as a picture?
Jon Peltier says
Erik –
Try without the parentheses:
ActiveChart.CopyPicture xlScreen, xlPicture, xlPrinter
bs0d says
The approach I used to get the charts in the order I wanted for PPT was to names each chart, then build an array of chart names in the desired order, then loop through that array, using the array name for each chart reference.
With regard to using a template, you can tell PPT in VBA to open a file to work from. So just setup that file with the template that has the company logo and so forth. Like this:
Dim file As String
file = “C:\Users\username\Documents\Blank template.pptx”
Dim PPApp As Object
Set PPApp = CreateObject(“PowerPoint.Application”)
PPApp.Presentations.Open (file)
simple says
How can I use the strFileToOpen = Application.GetOpenFilename(FileFilter:=”Powerpoint Files *.pptx (*.pptx),”) and Set pptPres = pptApp.Presentations.Open(Filename:=strFileToOpen, ReadOnly:=msoFalse).
Basically, I want to use the code given by Jon on 3.-oct.2017. It works fine, but when i manipulate with above code , it wont work. so can you pls paste the complete code including the new feature (selecting a template or existing PPT) in your code. It will be helpful if I can use slidemaster with name ‘Template’ can be used for appending the slides with charts. Means, I have few static contents in the PPT and want to append the charts from each sheets to the existing PPT.
Jon Peltier says
Mary –
The code would be something like this using the lines as you have written them:
simple says
Thanks Jon, it worked fine….
Ebrahim says
Very Helpful, thanks for sharing
Mansh says
Hi Jon,
I have used this code to copy a number of charts from Excel into PP and all works well. I have also named each slide using vba code for ease of access. What I now need to do when I copy each chart is to print the current slide or a named slide . I have searched high & low and tried various options but just can’t get it to work.
Thanks for any help you can give.
Kev
Jon Peltier says
Kev –
Mansh –
This is untested, and written from PowerPoint, not Excel. The syntax you need is like this:
Presentation.PrintOut From, ToFromandToare the numbers of the slides to start and finish the print job. You need to figure out the slide number from the slide name. Something like this ought to do it:Dim SlideNum As LongSlideNum = ActivePresentation.Slides(SlideName).SlideIndex
ActivePresentation.PrintOut From:=SlideNum, To:=SlideNum
Mansh says
Thanks very much for you reply Jon.
I realise I didn’t mention that I am using late binding and after some further digging I realised I was missing the const ppPrintCurrent 3 in my code and is now working as expected. I always seem to get that lightbulb moment after I have asked a question…
With regard to the slide names, this particular project uses a static set of slides, which get updated with Excel charts, so I manually named each slide so that I can access it directly and issue ‘ppPrintCurrent’ when required. the code I use for naming the slides is below if anyone else would like to use it.
Again, thanks very much for you reply.
Mansh
——————————————————————
‘ NameSlide()
‘
‘ Renames the current slide so you can refer to this slide in
‘ VBA by name. This is not used as part of the application;
‘ it is for maintenance and for use only by developers of
‘ the PowerPoint presentation.
‘
‘ 1. In Normal view, click on the slide you wish to rename
‘ 2. ALT+F11 to VB Editor
‘ 3. F5 to run this subroutine
‘——————————————————————
Sub NameSlide()
Dim curName As String
curName = Application.ActiveWindow.View.Slide.name
Dim newName As String
retry:
newName = InputBox(“Enter the new name for slide ‘” + curName + “‘, or press Cancel to keep existing name.”, “Rename slide”)
If Trim(newName) = “” Then Exit Sub
Dim s As Slide
‘ check if this slide name already exists
On Error GoTo SlideNotFound
Set s = ActivePresentation.Slides(newName)
On Error GoTo 0
MsgBox “Slide with this name already exists!”
GoTo retry
Exit Sub
SlideNotFound:
On Error GoTo 0
Application.ActiveWindow.View.Slide.name = newName
MsgBox “Slide renamed to ‘” + newName + “‘.”
End Sub
Jon Peltier says
Hmmm, ppPrintCurrent, didn’t find that. PowerPoint VBA is trickier than Excel VBA, because Excel has the recorder to help, plus much more related web content, and the PowerPoint object model is not as easy to understand.
Quick note, use an ampersand, not a plus sign, to combine strings. Change
to