Create PowerPoint Presentations Automatically Using VBA _ Chandoo.org - Learn Microsoft Excel Online

reate PowerPoint PowerPoint Presentations Automatically using VBA | Chandoo.org... http://chandoo.org/wp/2011/08/ http://chandoo.org/wp/2011/08/03/create-powerpoi 03/create-powerpoint-presentations-us nt-presentations-using... ing...

Create PowerPoint Presentations Automatically using VBA Posted on August 3rd, 2011 in Charts and Graphs , Excel Howtos , VBA Macros - 46 comments

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 f inish 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.

Create PowerPoint PowerPoint Presentations Automatically using VBA | Chandoo.org... http://chandoo.org/wp/2011/08/ http://chandoo.org/wp/2011/08/03/create-powerpoi 03/create-powerpoint-presentations-us nt-presentations-using... ing...

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.

Create PowerPoint Presentations Automatically using VBA | Chandoo.org... http://chandoo.org/wp/2011/08/03/create-powerpoint-presentations-using...

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.

Sign up for VBA Newsletter

Your name

Email:

Your email address is safe. Its a p romise.

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.

46 Responses to “Create PowerPoint Presentations Automatically using VBA”

1.

Steve Rocliffe says: August 3, 2011 at 9:01 am


Hmmm…whilst that’d be very handy sometimes, I’ve often found i t’s quicker and easier to simply link the charts to a PowerPoint, meaning that any time you update the chart, you update the P PT too. Reply 2.

Stef@n says: August 3, 2011 at 9:55 am 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 Reply

3.

Stef@n says: August 3, 2011 at 9:56 am @ steve i agree ! regards Stef@n Reply

4.

GrahamG says: August 3, 2011 at 12:25 pm 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 t he 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. Reply

5.

Rich says: August 3, 2011 at 3:58 pm


Datapig had a similar method. http://datapigtechnologies.com/blog/index.php/creating-a-powepoint-deck-in-excel/ This works in 2003 Reply 6.

Jim Watson says: August 3, 2011 at 4:16 pm I downloaded the example spreadsheet in Create PowerPoint Presentations Automatically using VBA. Excel says this file is not in E xcel format even though it has an .xls extension. I am using Excel2003. Any ideas? Reply

7.

Akash says: August 3, 2011 at 4:24 pm 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?? Reply

8.

Jim says: August 3, 2011 at 4:28 pm I have a few large presentations (~300 slides). My approach was to make a chart for every possible permutation, then link them al l 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 n ew 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, Fi lterName:=”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, F ilterName:=”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 t he 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. Reply 9.

Donald says: August 3, 2011 at 5:24 pm @GrahamG : Can you share a file and procedure as you mentioned on your comment? Reply

10.

Donald says: August 3, 2011 at 5:32 pm @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!!! Reply

11.

Mustafa says: August 3, 2011 at 6:18 pm Thanks Drew, Its really useful code to work on powerpoint. If we can export it to excel again it may be awesome. Reply

12.

Fred says: August 3, 2011 at 6:48 pm 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.


Reply 13.

Jordan Goldmeier says: August 3, 2011 at 6:55 pm @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! Reply

14.

Jordan Goldmeier says: August 3, 2011 at 7:05 pm 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 th e 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! Reply

15.

Robert says: August 3, 2011 at 7:55 pm Drew, nice post. I have done something similar on my blog: http://www.clearlyandsimply.com/clearly_and_simply/2010/03/export-microsoft-exceldashboards-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-learningto-fly.html


Reply 16.

vikas says: August 4, 2011 at 2:10 am thanks for sharing the trick. However, is it possible to export in a chart format instead of picture format? Reply

17.

Stephen says: August 4, 2011 at 10:28 am @ 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? Reply

18.

Rohit1409 says: August 4, 2011 at 10:30 am @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. Reply

19.

FINCRIBE says: August 5, 2011 at 5:26 pm hay its cool but it uses blank PPT t amplate what about using Companys own PPt Tamplet

Reply 20. MogBlog » Blog Archive » Create PowerPoint Presentations Automatically using VBA says: August 6, 2011 at 7:56 am [...] link [...] Reply 21.

Stef@n says: August 6, 2011 at 12:24 pm @FINCRIBE create and save the PPT-Template as a POT-file Regards Stef@n Reply


22.

davidlim says: August 8, 2011 at 6:10 am 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 t o open attachments and would rather browse the charts via Blackberry!! Reply

23.

tom says: August 8, 2011 at 6:18 am 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? Reply

24.

davidlim says: August 8, 2011 at 7:01 am 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” Reply

25.

Donald says: August 8, 2011 at 8:26 am @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 t his 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-exceldashboards-to-powerpoint.html Reply


26.

sri says: August 16, 2011 at 6:22 am 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. Reply

27.

KJ says: August 19, 2011 at 3:43 pm Does anyone know how you would adjust the code for this to pick up a camera tool snapshot instead? Reply

28.

Pankaj says: August 25, 2011 at 4:28 am How doe we chnage the data dynamically in the PPT iteslef so that the graphs get modifed .Becuase currently it gor imported as an i mage .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 Reply

29.

Alan says: August 25, 2011 at 2:22 pm Love it – thanks for the tip – owe you a beer Reply

30.

chander says: September 12, 2011 at 11:02 am How can we do it for pictures (map objects)? Anone can help thanks in advance Reply

31.

Anthony says: September 12, 2011 at 5:20 pm 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 Reply

32.

chander says:


September 13, 2011 at 4:22 am 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 Reply 33.

William says: September 26, 2011 at 7:36 pm How can use this same methond to copy range of cells? Reply

34.

Sai Swaroop says: October 17, 2011 at 9:43 am ***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.. Reply 35.

Amy says: October 28, 2011 at 9:18 pm 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 Reply

36.

Swetha says: November 1, 2011 at 1:58 pm 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 an d 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 Reply

37.

Jenn says: November 11, 2011 at 10:59 pm 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! Reply


38.

chander says: November 14, 2011 at 5:50 am Hi sai swaroop i am interested in working file pls email at [email protected] Reply

39.

Sai Swaroop says: November 14, 2011 at 7:41 am hey Chander, Sent you the file. Sai Reply

40.

Ajay says: December 15, 2011 at 3:38 am Hi Sai, Can you please send me the working file as I am bumping up with same errors. Ajay Reply

41.

Ajay says: December 15, 2011 at 3:52 am Chandar &Sai, Pls send the working file as I am bumping with invalid shapes error [email protected] Reply

42.

Vikas says: December 15, 2011 at 4:30 pm Hi, I was able to paste the Excel chart int o 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 h ave 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


Reply 43.

LD says: January 25, 2012 at 3:22 pm i have 5 graphs which should pasted on the PPT in single slide…can we do it automatically? Reply

44.

Aashish says: February 13, 2012 at 1:42 am 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 fi nd 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. Reply

45.

ashkan says: February 16, 2012 at 12:26 am 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 l inked to the excel sheet With activeSlide.Shapes(activeSlide.Shapes.Count) ‘The chart that was just pasted .Left = 15 .Top = 125 End With Reply

46.

Jevi says: February 17, 2012 at 3:26 pm really cool….this website has wonderful tips and tricks Reply

Leave a Reply

. Thank you a ton!


Create PowerPoint Presentations Automatically Using VBA _ Chandoo.org - Learn Microsoft Excel Online

Recommend Documents