SubscribeCreate PowerPoint Presentations Automatically using VBA
This is a guest post by Drew Kesler. Drew is the founder of Topo.ly, which enables users to easily map spreadsheet data and perform visual analysis.
You’ve been there before. It’s almost 5:00, and you are going crazy trying to finish the presentation due for a monthly performance meeting the next morning. The model is refreshed, and now it just takes a LOT of copying, pasting, and positioning to get the PowerPoint ready. Finally, the slides are finished…, until you read a new message from your boss requesting a minor change. But of course her change means you have to start all over with the copy and pastes…
There is always a better way! In the Oil and Gas industry, I constantly have monthly reports to assess the performance of our operating assets. Excel VBA makes it a cinch to automate the entire process. So when a simple change is requested, the presentation is automatically generated with the click of a button. No more wasting time!
So, here it is – How to Save TONS of Time by Using an Excel VBA Macro to Build Your Presentation:
1. Build your charts in Excel
2. Create a new worksheet and paste in all the charts you need for the presentation.
3. Open VBA. To do this, you can either press ALT + F11, or you can take the following steps:
a. To show the developer tab, click on the Microsoft Office Button and click Excel Options.
b. Click Popular and then select the Show Developer tab in the Ribbon.
c. Click on the Developer tab in the ribbon and click Visual Basic.
4. In your VBA Editor window, click File => Insert => Module.
5. Paste the following code into the module (I included comments so you can customize it to your liking).
6. Click Tools => References.
Add the Microsoft PowerPoint Library.
7. Now all you need to do is go to Excel and run the CreatePowerPoint macro! To make this easy, draw a rectangle shape in your Excel worksheet which contains all the charts you want to export to PowerPoint.
8. Right click the rectangle and click Assign Macro.
9. Click on the CreatePowerPoint macro and press Okay.
10. That’s it! Just click your rectangle button then sit back and watch it run! You’ll have your presentation in no time!
Download the Example Workbook & Play with this Macro
Click here to download the example workbook and play with the macro.
Thanks Drew
Thank you so much Drew for writing this insightful article and showing us how to automate PPT Creation thru Excel VBA. I have really enjoyed playing this idea. And I am sure our readers will also like it.
If you like this technique, say thanks to Drew.
How do you Automate PPT Creation?
During my day job, I used to make a lot of presentations. But each one was different. So I used to spend hours crafting them.
And nowadays, I hardly make a presentation. But I know many of you make PPTs day in day out. And this technique presented by Drew is a very powerful way to save time.
Do you use macros to automate creation of presentations? What are your favorite tricks & ideas? Please share using comments.
Learn More VBA – Sign-up for our VBA Class Waiting List
Chandoo.org runs a VBA Class that teaches you from scratch, how to build macros to save time & automate your work. We opened our first batch in May this year and had an excellent response. More than 650 students signed up and are now learning VBA each day. I will be opening VBA class for enrollment again on September 5th (just one more month).
If you want to learn VBA & advanced Excel, this is a very good class to join. Please enter your name & email address below and I will update you as soon as our class opens for enrollment.
About the Author:
Drew Kesler specializes in process automation and data visualization. He currently performs analytics and modeling for the Oil and Gas industry. His most recent projects include using GIS mapping technology to visualize data and enhance interaction across organizations. Drew is the founder of Topo.ly , which enables users to easily map spreadsheet data and perform visual analysis.
| ||
|
Spread some love,
Posts & Navigation
Tags: advanced excel, Automation, charting, downloads, guest posts, Learn Excel, macros, powerpoint |
At Chandoo.org, I have one goal, "to make you awesome in excel and charting". This blog is started in 2007 and today has 450+ articles and tutorials on using excel, making better charts. 
55 Responses to “Create PowerPoint Presentations Automatically using VBA”
Hmmm…whilst that’d be very handy sometimes, I’ve often found it’s quicker and easier to simply link the charts to a PowerPoint, meaning that any time you update the chart, you update the PPT too.
Hey
here is a “cool” VBA-Code to create on the first slide thumbnails of all slides !
Regards
Stef@n
Sub thumbnails()
Dim strPath As String
Dim i As Integer
Dim n As Integer
Dim sld As Slide
strPath = ActivePresentation.Path
n = ActivePresentation.Slides.Count
ActivePresentation.SaveAs FileName:=strPath & “\Test.png”, _
FileFormat:=ppSaveAsPNG, EmbedTrueTypeFonts:=msoFalse
Set sld = ActivePresentation.Slides.Add(1, ppLayoutBlank)
For i = 1 To n
sld.Shapes.AddPicture FileName:=strPath & “\Test\slide” & i & _
“.PNG”, LinkToFile:=msoFalse, SaveWithDocument:=msoTrue, Left:=i * 30, _
Top:=i * 30, Width:=144, Height:=108
Next i
End Sub
Hi,
I can’t make it work.
I get the error 424: Object required.
I suppose this is because of an object library is not properly referenced, but which?
Thanks!
@ steve
i agree !
regards Stef@n
On occasions I have to create a large number of photo sheets for inclusion in a report. That is, a page with one or two photos and a description. Using a list in an excel spreadsheet that gives the file name for the photo and a description I written a macro to generate a powerpoint presentation that can be saved as a pdf or printed out. Any changes to photo or description are simple to do in the list.
Datapig had a similar method.
http://datapigtechnologies.com/blog/index.php/creating-a-powepoint-deck-in-excel/
This works in 2003
I downloaded the example spreadsheet in Create PowerPoint Presentations Automatically using VBA. Excel says this file is not in Excel format even though it has an .xls extension. I am using Excel2003. Any ideas?
How do you copy charts from excel into PowerPoint as ChartObjects (not metafile or linked image)?
In excel 2003 we had the graph engine which enabled us to paste the chart data into Graph.DataSheet. Whats the equivalent in Office 2010??
I have a few large presentations (~300 slides). My approach was to make a chart for every possible permutation, then link them all into PowerPoint. The powerPoint works like a website, so the user can click around to get to the chart they are interested in within 1-2 clicks.
Unfortunately, this approach doesn’t seem to work in Office 2007. First, it takes forever to update the links, if it does it at all. Second, once they are updated, when you go into slide show view in PowerPoint the fonts are all fuzzy (I’ve looked into this and it has something to do with the way PP07 scales the slides). There appears to be no solution to this so now I need a new approach…
I’ve tested the following approach a little and it seems to work. I have a sub that exports all the charts out as images:
Sub Export2Image()
Dim oCht As ChartObject
Dim flPath As String, fName As String
Dim ws As Worksheet
Dim cs As Chart
Dim i As Long
flPath = “C:\Excel\Exports” ‘Put files in this folder
ChDir flPath
For Each cs In ActiveWorkbook.Charts
i = i + 1
fName = cs.Name & i & “.jpg”
cs.Export Filename:=fName, FilterName:=”jpg”
Next
For Each ws In ActiveWorkbook.Worksheets
For Each oCht In ws.ChartObjects
i = i + 1
fName = ws.Name & i & “.jpg”
oCht.Chart.Export Filename:=fName, FilterName:=”jpg”
Next
Next
‘MsgBox (“All workbook charts have been exported as images to “) & flPath & “.”, vbOKOnly
End Sub
Then in PowerPoint I link to the image on the file server. The only problem I’ve noticed is some of the charts have the little red “X” in the corner, but it goes away after a second or two. Though this approach seems to be viable, I am open to other suggestions. I tried the code above, but it doesn’t really work for me because I have set slides with set text, I only need to update the chart.
@GrahamG : Can you share a file and procedure as you mentioned on your comment?
@Drew: Wow thanks for sharing the info…you’re a life saver.
Guys,
Can someone post on how to modify the VB script for the case below:
If you have a mixture of Chart and Picture in your workbook or if you have pictures only.
Meaning the presentation should be created using the pictures from excel the excel workbook, with comments as on the example sheet ofcourse.
Please help!!!
Thanks Drew, Its really useful code to work on powerpoint. If we can export it to excel again it may be awesome.
My choice is steve’s because most ofthen than not you have made other customization on the powerpoint slide/chart.
But it is great to know new technique.
@Donald: Use the CopyPicture method. For example, if you go into drew’s code, you will see the line:
ActiveChart.ChartArea.Copy
Change that to the following line:
Sheet1.Range(“A1:I19″).CopyPicture xlScreen, xlPicture
——————————————————————————
Note that when you run the program it copies the contents of A1:I19 onto your presentation from Sheet1. Hope that helps!
Here’s a link to the CopyPicture method: http://msdn.microsoft.com/en-us/library/bb148266.aspx.
Also, I’ve used and much prefer Drew’s method overall. If your PowerPoint presentation is a report, then linking to the data source isn’t always best when you need to send the presentation to your client or to another office branch, both of which might be outside of your network. Furthermore, generating a new presentation upon changes effectively creates an historical record. And finally, creating a presentation that isn’t linked to the data source “walls off” your actual data such that nefarious and reckless can’t harm it!
Drew,
nice post. I have done something similar on my blog:
http://www.clearlyandsimply.com/clearly_and_simply/2010/03/export-microsoft-excel-dashboards-to-powerpoint.html
I thought you might be interested.
—
Along the same lines: I also had an article describing how to export a Microsoft Project Gantt chartto PowerPoint.
The interesting part of the Project VBA export procedure is the fact that it does not export the Gantt as a picture. It rather creates a project plan in PowerPoint consisting of PPT objects like textboxes, rectangles, triangles and diamonds. You can format, align, rearrange, group and resize them, add annotations, delete single items, etc. in PowerPoint.
This may be a bit off topic, because Chandoo.org is a Microsoft Excel blog, but if you are using Microsoft Project, too, you may want to have a look:
http://www.clearlyandsimply.com/clearly_and_simply/2009/03/gantt-charts-are-learning-to-fly.html
thanks for sharing the trick. However, is it possible to export in a chart format instead of picture format?
@ drew, THANK YOU for sharing, and starting this thread.
@ everyone else, yes there are different ways of doing things. Sometimes your solutions would be better than drew’s and sometimes drew’s will be a better method.
thanks for sharing your solutions
@chandoo/hui
can you make it easy to understand each option by expanding on this thread?
@Vikas
Select your chart in Excel, Go to PowerPoint and do Paste Special> Choose PasteLink> Choose Microsoft Excel Chart Object. You are done.
Now whenever you change your excel, just update link of Powerpoint. Your Chart will instantly updated.
hay its cool but it uses blank PPT tamplate what about using Companys own PPt Tamplet
[...] link [...]
@FINCRIBE
create and save the PPT-Template as a POT-file
Regards Stef@n
is it possible to modify the VBA to copy all charts from all worksheets into 1 single powerpoint file?
reason is that multiple charts are scattered across few worksheets and it’d be easier (or lazier) to copy ALL charts
btw, any chance to perform similar copy-n-paste to Outlook Email (HTML)?
as u know, bosses hate to open attachments and would rather browse the charts via Blackberry!!
I’ve used this post to great effect already – many thanks for sharing!
I was wondering if the code could be modified so that rather than exporting charts to powerpoint, you could export a table?
noticed there is a bug/limitation.
if a chart is smaller than a powerpoint slide size, it works.
however, if a chart (or combo grouped-charts) is large, the VBA will not run.
error box: “Run-time error -2147418113 (8000ffff)’: Method ‘Copy’ of object ‘ChartArea’ failed”
if i Debug, it will point to “ActiveChart.ChartArea.Copy”
@Davidlim: If you look on the earlier comments, robert posted a similar tehnique and on his example he has the chart/pic on different worksheets. When you execure the VB script it gives you option to open your existing template/file where the slide will be added last, meaning your presetation backround remains. or if you click cancel it creates a completely new presentation. on his Excel file he uses the names. Go to this link for more info and for the file download.
It’s very interesting.
Thanks to all that contribute to these comments and I’m glad that through chandoo we get help.
http://www.clearlyandsimply.com/clearly_and_simply/2010/03/export-microsoft-excel-dashboards-to-powerpoint.html
Hi Chandoo,
It works perfectly. Is there a way out to export tables from excel 2007 to PPT using Macros & the exported tables shld be in editable form in PPT.
Does anyone know how you would adjust the code for this to pick up a camera tool snapshot instead?
How doe we chnage the data dynamically in the PPT iteslef so that the graphs get modifed .Becuase currently it gor imported as an image .To chnage i have to go back to excel chnage teh data and again export .
Are there any way to chnage the values in the PPT and create the graph then and there in the PPT itself
Love it – thanks for the tip – owe you a beer
How can we do it for pictures (map objects)? Anone can help thanks in advance
Great tip and is very helpful – I have a standard company template and would like to automatically place the graphs and text into this could any one please advise how this can be done with adding to the VBA code supplied.
Many Thanks
If any body can demonstrate as I have not got my answer i.e. how to automatically place the picture (bitmap) and text associated with picture in ppt.
Drew and Chandoo please help
How can use this same methond to copy range of cells?
***William – First, Use this function:
Function CopyPaste(slide, selection, aheight, awidth, atop, aleft)
Set PPApp = GetObject(, “Powerpoint.Application”)
Set PPPres = PPApp.ActivePresentation
PPApp.Activate
Set PPSlide = PPPres.Slides(PPApp.ActiveWindow.selection.SlideRange.SlideIndex)
PPApp.ActiveWindow.View.GotoSlide (PPPres.Slides.Count)
PPApp.Activate
PPApp.ActiveWindow.View.GotoSlide (slide)
‘ Reference LAST slide
Set PPSlide = PPPres.Slides(PPApp.ActiveWindow.selection.SlideRange.SlideIndex)
selection.CopyPicture Appearance:=xlScreen, _
Format:=xlBitmap
PPSlide.Shapes.Paste.Select
Set sr = PPApp.ActiveWindow.selection.ShapeRange
‘ Resize:
sr.Height = aheight
sr.Width = awidth
If sr.Width > 700 Then
sr.Width = 700
End If
If sr.Height > 420 Then
sr.Height = 420
End If
‘ Realign:
sr.Align msoAlignCenters, True
‘sr.Align msoAlignMiddles, True
sr.Top = atop ‘
If aleft 0 Then
sr.Left = aleft ’50
End If
If Not IsMissing(drawBorder) Then
‘Draw border for the shape range
With sr.Line
.Style = msoLineThinThin
.Weight = 0.1
.DashStyle = msoLineSolid
.ForeColor.RGB = RGB(0, 0, 0)
End With
End If
‘ Clean up
Set PPSlide = Nothing
Set PPPres = Nothing
Set PPApp = Nothing
End Function
THEN, this line in your code:
CopyPaste slide, selection, 200, 700, 82, 10 ‘this copies the Selected Range
*** IF you want a working file – please let me know if you want to know how to make this dynamic, please let me know so that I can email you the working file..
Hello,
I need to export a few hundred graphs from excel and put 5 to a page in powerpoint. The graphs need to be a specified size with a black border. Can anyone provide the visual basic code to accomplish this?
Best,
Amy
Hi. I am working with a project where we create several summary reports and graphs based on a set of Raw data. Up until recently we have been using a manual process to copy paste these in Powerpoint. Could someone tell me how to copy tables and graphs over several worksheets into one powerpoint presentation please? I have tried the Macro for charts and it works great but wondered if someone could show how to make it work for tables and other data on excel.
Is it also possible for the presentation to change dynamically as the raw data chagnes?
Thanks,
Swetha
I have the same issue as Pankaj – how can we update the code to paste it as a Chart object that can be edited in PowerPoint (linked or the Excel file or unlinked, doesn’t matter). I tried replacing “ppPasteMetafilePicture” with “ppPasteOLEObject” but it’s still pasting the charts as pictures. Thanks!
Hi sai swaroop i am interested in working file pls email at chander.shekhar@kuehne-nagel.com
hey Chander,
Sent you the file.
Sai
Can you send me the working file thanks
Hi Sai,
Can you please send me the working file as I am bumping up with same errors.
Ajay
Chandar &Sai,
Pls send the working file as I am bumping with invalid shapes error
san201083@gmail.com
Hi, I was able to paste the Excel chart into the PowerPoint as a chart object, but I’m having trouble editing it within the presentation. PowerPoint VBA does not seem to allow me to refer to the chart and edit the axis font, etc., but instead edits the axis font size, axis font color of the chart in Excel. I was wondering if anyone could help. This is what I have so far. Thank you!
cht.Select
ActiveChart.ChartArea.Copy
activeSlide.Shapes.PasteSpecial(DataType:=ppPasteChartObject).Select
‘Adjust the positioning of the Chart on Powerpoint Slide
newPowerPoint.ActiveWindow.Selection.ShapeRange.Width = 9 * 72
newPowerPoint.ActiveWindow.Selection.ShapeRange.Height = 5 * 72
newPowerPoint.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
newPowerPoint.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
i have 5 graphs which should pasted on the PPT in single slide…can we do it automatically?
I dont know VB scripting or macros, but from what i understand its taking a chart as a object, but i have sheet with a lot of field names & respective numeric values from formuales.
But this code does not work on that, do we need to update this code ?
Or can you provide a new code ? [that would be of gr8 help] or if its there on your website wats the link coz i was unable to find it.
I am into software testing so we deal with a lot of data & numeric values & less of charts……….plz assist
And request you to post entries specific to the filed of software testing.
We are always on the look out of process enhancements which helps improve efficiency specially if its saving time for the project.
Here is how to copy past the chart as an actual chart rather than the picture. The pasted chart will be linked to the excel sheet, so any change in the excel sheet will be reflected on the chart.
‘Copy the chart and paste it into the PowerPoint as linked charts
cht.Select
ActiveChart.ChartArea.Copy
activeSlide.Shapes.Paste ‘ This new pasted chart is actually linked to the excel sheet
With activeSlide.Shapes(activeSlide.Shapes.Count) ‘The chart that was just pasted
.Left = 15
.Top = 125
End With
really cool….this website has wonderful tips and tricks
. Thank you a ton!
Can I put several graphs on one slide
How can this be done using Excel 2003? I have tried but keep getting the error: Missing:Microsoft Powerpoint 12.0 Object Library. How can I fix this?
Hey Brian. You’ll need to reference the correct Powerpoint library. Like in the example above, you’ll first, go into the VBA editor. From there you’ll select the Tools menu item and click “References….”
Now, you should see something like “MISSING: Missing:Microsoft Powerpoint 12.0 Object Library” in the list box. De-select it. Now scroll down and look for something like “Microsoft Powerpoint ## Object Library” (where the # is a number). Most likely, if you’re using Excel 2003, it will be “Microsoft PowerPoint 9.0 Object Library.”
@ graham
I am interested to do the same.
Can you share the file/code with me?
Thanks!
Hi I need to loop through all the sheets in a work book and copy all the charts from one sheet to one Slide. Could u help??
see above
Sub thumbnails()
Dim strPath As String
Dim i As Integer
Dim n As Integer
Dim sld As Slide
strPath = ActivePresentation.Path
n = ActivePresentation.Slides.Count
ActivePresentation.SaveAs FileName:=strPath & “\Test.png”, _
FileFormat:=ppSaveAsPNG, EmbedTrueTypeFonts:=msoFalse
Set sld = ActivePresentation.Slides.Add(1, ppLayoutBlank)
For i = 1 To n
sld.Shapes.AddPicture FileName:=strPath & “\Test\slide” & i & _
“.PNG”, LinkToFile:=msoFalse, SaveWithDocument:=msoTrue, Left:=i * 30, _
Top:=i * 30, Width:=144, Height:=108
Next i
End Sub
hi,
im using a excel for mac 2011, and I can’t get it to work – i keep getting this error:
compile method or data member not found
and it highlights the PasteSpecial in the code!
can someone please let me know how to fix this?
any help would be much appreciated
thanks,
Paul