Create PowerPoint Presentations Automatically using VBA

Posted on August 3rd, 2011 in Charts and Graphs , Excel Howtos , VBA Macros - 168 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 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.
Excel to PowerPoint using VBA - Step 2

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.
Excel to PowerPoint using VBA - Step 3a

b. Click Popular and then select the Show Developer tab in the Ribbon.
Excel to PowerPoint using VBA - Step 3b

c. Click on the Developer tab in the ribbon and click Visual Basic.
Excel to PowerPoint using VBA - Step 3c

4. In your VBA Editor window, click File => Insert => Module.
Excel to PowerPoint using VBA - Step 4

5. Paste the following code into the module (I included comments so you can customize it to your liking).
Excel to PowerPoint using VBA - Step 5

6. Click Tools => References.
Excel to PowerPoint using VBA - Step 6a

Add the Microsoft PowerPoint Library.
Excel to PowerPoint using VBA - Step 6b

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.

Excel to PowerPoint using VBA - Step 7

8. Right click the rectangle and click Assign Macro.
Excel to PowerPoint using VBA - Step 8

9. Click on the CreatePowerPoint macro and press Okay.
Excel to PowerPoint using VBA - Step 9

10. That’s it! Just click your rectangle button then sit back and watch it run! You’ll have your presentation in no time!
Excel to PowerPoint using VBA - Step 10

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.

Written by Chandoo
Tags: , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

168 Responses to “Create PowerPoint Presentations Automatically using VBA”

  1. Steve Rocliffe says:

    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.

    • Ray says:

      Wondering if there is a way to actually use excel slicers while in PowerPoint Presentation mode. I figured out how to incorporate the slicers into the PPT but when you switch to presentation mode, you can't click on an fields to change the details. Anyone know if this can be done?

    • PRUDEON says:

      Hi there!

      we have prepared a simple and useful solution for that issue. Check the following video, where its presented: http://www.youtube.com/watch?v=inBBlpd9qQ4

      You will find the contact information and we will provide you that Add-in.

      Than really saves you a lot of time!!

      Good luck!

  2. Stef@n says:

    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

    • maria says:

      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!

      • Chris says:

        I too am getting this error and can get the sample file to work. This would be great for a project I am currently working on.

    • Glenn H says:

      It is not clear to me from the trailing posts if the problem with the above not working has been fixed. However, the slightly modified code below works with Office 2010 (Powerpoint), and produces a slide with thumbnails (iMaxSlidesHorizontal (8) to a row).

      Sub thumbnails()

      Dim iFullHeight As Integer
      iFullHeight = 0
      Dim iFullWidth As Integer
      iFullWidth = 0

      Dim iHeight As Integer
      iHeight = 108
      Dim iHSpacing As Integer
      iHSpacing = 10

      Dim iMaxSlidesHorizontal As Integer
      iMaxSlidesHorizontal = 8
      Dim iVSpacing As Integer
      iVSpacing = 10
      Dim iWidth As Integer
      iWidth = 144
      Dim n As Integer
      n = ActivePresentation.Slides.Count
      Dim strPath As String
      strPath = ActivePresentation.Path

      Dim sld As Slide
      Dim i As Integer

      Dim iSlidesHorizontal As Integer
      iSlidesHorizontal = 0
      Dim iSlidesVertical As Integer
      iSlidesVertical = 0

      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:=iSlidesHorizontal * (iWidth + iHSpacing), Top:=iSlidesVertical * (iHeight + iVSpacing), _
      Width:=iWidth, _
      Height:=iHeight

      iSlidesHorizontal = iSlidesHorizontal + 1

      If iSlidesHorizontal >= iMaxSlideHorizontal Then

      iSlidesHorizontal = 0
      iSlidesVertical = iSlidesVertical + 1

      End If

      Next i

      End Sub

  3. Stef@n says:

    @ steve
    i agree !
    regards Stef@n

  4. GrahamG says:

    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.

  5. Jim Watson says:

    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?

  6. Akash says:

    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??

  7. Jim says:

    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.

  8. Donald says:

    @GrahamG : Can you share a file and procedure as you mentioned on your comment?

  9. Donald says:

    @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!!!

  10. Mustafa says:

    Thanks Drew, Its really useful code to work on powerpoint. If we can export it to excel again it may be awesome.

  11. Fred says:

    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.

  12. @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!

  13. 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!

  14. Robert says:

    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

  15. vikas says:

    thanks for sharing the trick. However, is it possible to export in a chart format instead of picture format?

  16. Stephen says:

    @ 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?

  17. Rohit1409 says:

    @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.

  18. FINCRIBE says:

    hay its cool but it uses blank PPT tamplate what about using Companys own PPt Tamplet

  19. Stef@n says:

    @FINCRIBE
    create and save the PPT-Template as a POT-file
    Regards Stef@n

  20. davidlim says:

    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!!

  21. tom says:

    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?

  22. davidlim says:

    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"

  23. Donald says:

    @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

  24. sri says:

    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.

  25. KJ says:

    Does anyone know how you would adjust the code for this to pick up a camera tool snapshot instead?

  26. Pankaj says:

    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

    • Dolphin Godfred says:

      Hi Pankaj,
      I might be too late to respond ... By now you might have got your answer as well.
      Modify the below:
      activeSlide.Shapes.PasteSpecial(DataType:=ppPasteMetafilePicture).Select
      to:
      activeSlide.Shapes.PasteSpecial(DataType:=ppPasteMetafilePicture, Link:=msoTrue).Select
      -DG.

  27. Alan says:

    Love it - thanks for the tip - owe you a beer 😉

  28. chander says:

    How can we do it for pictures (map objects)? Anone can help thanks in advance

  29. Anthony says:

    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

  30. chander says:

    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

  31. William says:

    How can use this same methond to copy range of cells?

  32. Sai Swaroop says:

    ***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..

  33. Amy says:

    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

  34. Swetha says:

    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

  35. Jenn says:

    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!

  36. chander says:

    Hi sai swaroop i am interested in working file pls email at chander.shekhar@kuehne-nagel.com

  37. Sai Swaroop says:

    hey Chander,

    Sent you the file.

    Sai

  38. Ajay says:

    Hi Sai,

    Can you please send me the working file as I am bumping up with same errors.

    Ajay

  39. Ajay says:

    Chandar &Sai,

    Pls send the working file as I am bumping with invalid shapes error

    san201083@gmail.com

  40. Vikas says:

    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

  41. LD says:

    i have 5 graphs which should pasted on the PPT in single slide...can we do it automatically?

  42. Aashish says:

    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.

  43. ashkan says:

    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

  44. Jevi says:

    really cool....this website has wonderful tips and tricks :). Thank you a ton!

  45. Lora says:

    Can I put several graphs on one slide

  46. Brian says:

    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?

  47. 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."

  48. Nate says:

    @ graham
    I am interested to do the same.
    Can you share the file/code with me?
    Thanks!

  49. Rohan says:

    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??

    • Stef@n says:

      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

  50. Paul Smith says:

    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 

  51. Aaron says:

    i would like to add a code to use a particular template shown in the following:
      PowerPoint.Application.ActivePresentation.ApplyTemplate "C:\Documents and Settings\myfile\Application Data\Microsoft\Templates\ShortTitle.pot"

    But i'm getting a 429 error, claiming the ActiveX component cannot create the object.

    What else can i do please?

    • Jenn says:

      Hi Aaron,

      Below is the code I use to open up a PP template. Also, Under Tools > References, you need to make sure the Microsoft PowerPoint 14.0 Object Library is checked. 

       Dim newPowerPoint As PowerPoint.Application
              Dim pptPres As PowerPoint.Presentation
              Dim activeSlide As PowerPoint.Slide
              Dim cht As Excel.ChartObject
              Dim file As String
              file = "C:\Users\jbain\Documents\PowerPoint template_Span.pptx"
              Dim pptcht As PowerPoint.Chart
              
           'Look for existing instance
              On Error Resume Next
              Set newPowerPoint = GetObject(, "PowerPoint.Application")
              On Error GoTo 0
           
          'Let's create a new PowerPoint
              If newPowerPoint Is Nothing Then
                  Set newPowerPoint = New PowerPoint.Application
              End If
          'Make a presentation in PowerPoint
              If newPowerPoint.Presentations.Count = 0 Then
              Set pptPres = newPowerPoint.Presentations.Open(file)
              End If
           
          'Show the PowerPoint
              newPowerPoint.Visible = True 

      • Aaron says:

        hi Jenn,
        thank you for your reply.
        in the Tools > Reference, i only find Microsoft PowerPoint 12.0 Object Library. How do i get hold of Microsoft PowerPoint 14.0 Object Library please?

        i'm using Office 2007.

        • Dolphin Godfred says:

          Hi Aaron,
          I believe you are using Office 2007 and Jenn's using 2010. Hence the difference in Object Library version. You can try using Microsoft PowerPoint 12.0 Object Library and try.
          Please modify:
          PowerPoint.Application.ActivePresentation.ApplyTemplate “C:\Documents and Settings\myfile\Application Data\Microsoft\Templates\ShortTitle.pot”
          to:
          PowerPoint.ActivePresentation.ApplyTemplate “C:\Documents and Settings\myfile\Application Data\Microsoft\Templates\ShortTitle.pot”
          -DG.

          • Aaron says:

            Hi DG,

            thank you for your suggestion. i tried the modification, and now the error claims:
            '429' error.
            ActiveX component cannot create object.

            highlighsts the code: PowerPoint.ActivePresentation.ApplyTemplate “C:\Documents and Settings\myfile\Application Data\Microsoft\Templates\ShortTitle.pot”

            Any other suggestions i can try please?

          • Subodh says:

            Sub PushChartsToPPT()
            'Set reference to 'Microsoft PowerPoint 12.0 Object Library'
            'in the VBE via Tools > References...
            '
            Dim ppt As PowerPoint.Application
            Dim pptPres As PowerPoint.Presentation
            Dim pptSld As PowerPoint.Slide
            Dim pptCL As PowerPoint.CustomLayout
            Dim pptShp As PowerPoint.Shape

            Dim cht As Chart
            Dim ws As Worksheet
            Dim i As Long

            Dim strPptTemplatePath As String

            strPptTemplatePath = "E:\DC++ Downloads\Intern\Ormax\Macro\demo template.pptx"

            'Get the PowerPoint Application object:
            Set ppt = CreateObject("PowerPoint.Application")
            ppt.Visible = msoTrue
            Set pptPres = ppt.Presentations.Open(strPptTemplatePath, untitled:=msoTrue)

            'Get a Custom Layout:
            For Each pptCL In pptPres.SlideMaster.CustomLayouts
            If pptCL.Name = "Title and Content" Then Exit For
            Next pptCL

            'Copy ALL charts in Chart Sheets:
            For Each cht In ActiveWorkbook.Charts
            Set pptSld = pptPres.Slides.AddSlide(pptPres.Slides.Count + 1, pptCL)
            pptSld.Select

            For Each pptShp In pptSld.Shapes.Placeholders
            If pptShp.PlaceholderFormat.Type = ppPlaceholderObject Then Exit For
            Next pptShp
            If pptShp Is Nothing Then Stop

            cht.ChartArea.Copy
            ppt.Activate
            pptShp.Select
            ppt.Windows(1).View.Paste
            Next cht

            'Copy ALL charts embedded in EACH WorkSheet:
            For Each ws In ActiveWorkbook.Worksheets
            For i = 1 To ws.ChartObjects.Count
            Set pptSld = pptPres.Slides.AddSlide(pptPres.Slides.Count + 1, pptCL)
            pptSld.Select

            For Each pptShp In pptSld.Shapes.Placeholders
            If pptShp.PlaceholderFormat.Type = ppPlaceholderObject Then Exit For
            Next pptShp

            Set cht = ws.ChartObjects(i).Chart
            cht.ChartArea.Copy
            ppt.Activate
            pptShp.Select
            ppt.Windows(1).View.Paste
            Next i
            Next ws
            End Sub

            I am using this code to link charts from excel to powerpoint. But this is inserting charts to last slide.
            Can anyone suggest me the changes so that i get charts to link with ppt to custom slide number and in mid of some saved template.
            Thanks in advance.

        • Jenn says:

          Hi Aaron,

          I have Microsoft 2010, which may be why mine is 14.0. Your version should work as well. The main thing is that the PowerPoint object library is references because the VBA code includes references to PowerPoint objects. 

        • Dolphin Godfred says:

          Hi Aaron,
           
          Apologies for the delay in response.
          Please try:
          newPowerPoint.ActivePresentation.ApplyTemplate "C:\Documents and Settings\myfile\Application Data\Microsoft\Templates\ShortTitle.pot"

          -DG 

          • Aaron says:

            Hi DG,

            thanks for your reply. i've tried it with the suggestion, but this time, error msg is:
            Operating error: -2147188160 (80048240)': Presentation (unknown member): Invalid request. PowerPoint could not open the file.

            highlights the code:
            newPowerPoint.ActivePresentation.ApplyTemplate “C:\Documents and Settings\myfile\Application Data\Microsoft\Templates\ShortTitle.pot”

            My codes as below. Is it possible you can find some error in the coding please?:
            'First we declare the variables we will be using
                    Dim newPowerPoint As PowerPoint.Application
                    Dim activeSlide As PowerPoint.Slide
                    Dim cht As Excel.ChartObject
                    Dim trfnum As String 'Variable to obtain Report #
                    Dim trfname As String 'Variable to obtain Report Title
                   
                        
                         trfnum = Range("K5").Value 'Assign/Obtain Report# from Cell K5
                         trfname = Range("K4").Value
                         trfprojnum = Range("K11").Value
                         trfpartnum = Range("K12").Value
                         trfsnnum = Range("K13").Value
                         trfmodelnum = Range("K14").Value
                        
                 'Look for existing instance
                    On Error Resume Next
                    Set newPowerPoint = GetObject(, "PowerPoint.Application")
                    On Error GoTo 0
                
                'Let's create a new PowerPoint
                    If newPowerPoint Is Nothing Then
                        Set newPowerPoint = New PowerPoint.Application
               
                    End If
               
               
                'Make a presentation in PowerPoint
                    If newPowerPoint.Presentations.Count = 0 Then
                        newPowerPoint.Presentations.Add
                    End If
               
                
                'Show the PowerPoint
                    newPowerPoint.Visible = True
               
                ' apply a slide template
                newPowerPoint.ActivePresentation.ApplyTemplate "C:\Documents and Settings\myfile\Application Data\Microsoft\Templates\ShortTitle.pot"
               
            regards,
            Aaron

  52. Aaron says:

    Hi Jenn,
    thanks for your response.

    i tried with the code you shared, but VBA has prompt me the following error:
    Presentations (unknown member) : Invalid request. The PowerPoint Frame window does not exist.

    It then highlights the code line:
       Set pptPres = newPowerPoint.Presentations.Open(file)

    What else can i try to resolve this error please?

  53. Anne says:

    Is there a way to insert the graph in a previously saved powerpoint  instead of creating a new powerpoint?

    Thanks

  54. Anne says:

    Scratch the last question.

    Does anyone know how to automatically insert the chart into the middle of a saved powerpoint instead of the end?

    Thanks

  55. Kim says:

    Thanks for the code; it worked great until I upgraded my Outlook from 2007 to 2010 (did not upgrade PP or Excel, they are still 2007). Now I get a runtime 430 error- Class does not support Automation or does not support expected interface. Any ideas on how to fix? Thanks!
     

    • Dolphin Godfred says:

      Hi Kim,
      Not sure if you are using any ADOs (ActiveX Data Objects) in your code. Please do the following steps:
      In the VBE (Visual Basic Editor), select Tools > References ... > Microsoft
      ActiveX Data Objects x.x Library
      Take the latest version. I have seen many versions of the same.
      Below link gives you an explanation about why the error occurs:
      http://makebarcode.com/info/appnote/app_017.html

      -DG 

  56. Vincent Ng says:

    I do have one question, from the initial source code :
    How to modify the code to have the idea but with pictures. no charts
    So each time the code detect an image on the sheet it will create a new slide?
     
    thanks in advance.
     

  57. Jon says:

    I've tried everything I can think of...how is it (with the code presented) that you are getting the words to the right of the charts to go into PowerPoint as a separate object?

    It works perfectly with your charts in your spreadsheet, but for the life of me I have not been able to replicate the behavior in my own spreadsheets.

  58. Dolphin Godfred says:

    @Chandoo: I am not able to see the updated comments in this page. Is there an issue from my side? Please let me know. Thank you.
    @Aaron: I believe you are using Office 2007. So, not sure about the extension of the template file. Please check the extension and change the same. Here, in your case, instead of "ShortTitle.pot" it may be "ShortTitle.potx".
    -DG

    WebRep

     
    Overall rating
     
     
    This site has no rating
    (not enough votes)
     
     

  59. Al says:

    Hello. I was trying to modify the supplied code for my purposes but kept hitting snags. As such, I am seeking some help with the following: I have an Excel workbook with various named worksheets and want to copy and paste the print ranges from each worksheet into an existing PowerPoint template using VBA. So, if worksheet “A”s print range is set to print on one page and worksheet “B”s print range is set to print on 3 pages, the PowerPoint presentation should have a total of 4 slides. I could end up with 10 worksheets in total representing 10+ slides needed in the presentation. Am new to VBA and would appreciate the help.

  60. RyanO says:

    How can I change the code so it copy and pastes only one specific chart, instead of all the charts?

    • Dolphin Godfred says:

      Hi Ryan,
       
      `'Declare the variable`
      `Dim ObjChartObject As Excel.ChartObject`
       
      `Set ObjChartObject = Worksheets("WorksheetName").ChartObjects("ChartName")`
      `ObjChartObject.Chart.ChartArea.Copy`
      `.`
      `.`
      `.`
      `.`
      `Continue your code`
       
      Hope this helps.
       
      -DG

  61. edoptx says:

    That is a very handy tool.... saved my@$$ 

    does anyone know how to modify to always open a new power point .  If ran a second time charts are tacked to the previously created presentation.

    thanks 

    • Dolphin Godfred says:

      Hi,
      The code that was provided in this example does the same.
      Please see point 5, you have the entire code.
      In the code, look for the section - 'Make a presentation in PowerPoint'

      Revert back if you are not able to understand.

      -DG 

  62. edoptx says:

    is there a way to lock out any user input while code is running. if i scroll while charts are moving to the power point the code stops.

    • Dolphin Godfred says:

      Hi,

      Please try the below:
      'Type this at the beginning of your code.
      Application.Interactive = False
      ... <Your code here>
      ... <Your code here>
      ... <Your code here>
      'Type this at the end of your code.
      Application.Interactive = True

      Hope this helps.

      -DG 

      • edoptx says:

        nope. user input still kills the macro. is there a way to not have screenupdating with powerpoint? or soemthing similar?

        • edoptx says:

          it only happens with the transfer from excel to power point. is there a way to have power point come up in a minimized state to at least minimize user interaction?

          • Dolphin Godfred says:

            Hi,

            In the code presented in this example, goto:
            'Show the PowerPoint

            After the line:
            newPowerPoint.Visible = True

            Type:
            newPowerPoint.WindowState = ppWindowMinimized

            This should keep the PowerPoint application in minimized.

            Hope this helps.

            -DG 

  63. Sam F says:

    I have (somewhat) of a reverse situation:

    I had to create and access an Excel file from PowerPoint.

    The first part (not shown) successfully creates a .CSV file containing several lines of data. E.g.:

         12345,John,8009991212,123 Main Street
         58145,Mary,3215551212,666 Mockingbird Lane

    ... etc. ...

    The last part of the macro (shown below) successfully opens the .CSV and Personal.XLS (which contains a macro to format the Excel file), saves as an .XLS in XLS format, then runs the macro "CTI_Format_B" to format the .XLS file (freeze header, autofit columns, etc.).

         'PowerPoint: Open Excel .CSV and save to .XLS, run Macro "FileFormatB"
         Dim oXL As Excel.Application  ' Excel Application Object
         Dim oWB As Excel.Workbook     ' Excel Workbook Object
         Dim FileXLS As String, FileCSV as String
         FileCSV = Environ("USERPROFILE") & "\" & "SamplePop.CSV"
         FileXLS = Left(FileCSV, Len(FileCSV) - 4) & ".xls"
     
         Set oXL = New Excel.Application
         oXL.Visible = True

         Set oWB = oXL.Workbooks.Open(oXL.StartupPath & "\Personal.xls")

         Set oWB = oXL.Workbooks.Open(FileCSV)   'open CSV file

         oWB.SaveAs FileName:=FileXLS, FileFormat:=xlNormal
         oXL.Run ("Personal.xls!FileFormatB")
         oWB.Save
         oXL.Visible = True

    The macro "FileFormatB" in Personal.xls contains formatting for the newly saved .XLS:

        'Excel macro to format header, etc.
        Range("A1:L1").Select   'format header
        With Selection
            .Font.Bold = True
            .Interior.ColorIndex = 6
            .Interior.Pattern = xlSolid
            .Font.ColorIndex = 5
        End With
        Rows("2:2").Select
        ActiveWindow.FreezePanes = True
        Cells.Select
        Cells.EntireColumn.AutoFit

    What I would like to do is instead of having a separate macro in Personal.xls to format the file and having to open Personal.xls (which is otherwise invisible when run here), I would like to run the same formatting from the original PowerPoint macro which created the file.

    How do I run the formatting from the PowerPoint macro to the opened Excel file?

  64. Re says:

    I would love to use this, but I get error messages even when I try the code on my file or run the downloaded file.  It says user defined type not defined...Any suggestions?  How do I define: Dim newPowerPoint As PowerPoint.Application

  65. Dee says:

    This is a great post!  

    My problem is that I would like to copy all charts on every worksheet in Excel to PowerPoint (2010).

    There is one chart per worksheet, with certain cells providing the title and axis label text and, of course, the range of cells that the chart is based upon.

    My problem is that I ONLY want to include that data when I paste as an embedded (not linked or picture) object into PowerPoint.

    Other data that is not directly related to the chart is included in PowerPoint. This is a problem, as I don't want users of PowerPoint to see that data.

    Is there any way to ONLY include the cells that the chart is directly based upon?

    Thanks! 

  66. Mike says:

    I am using similar code (see below) with Excel 14, Powerpoint 14 and Windows XP. I use this to copy/paste about 100 images from Excel to Powerpoint. Sometimes I get this error: "Shapes(unkown member) : Invalid request. The specified data type is unavailable."
    I can run the exact same code and it works, then run it again and I get this error. And the error happens in different places of the code execution each time (although always on a PasteSpecial line). Sometimes in first loop, sometimes in 15th loop, etc.
    Sheets("RoleSummary").Range("RoleSummaryTable").Copy

    PPApp.ActiveWindow.View.GotoSlide PPApp.ActivePresentation.Slides.Count

    PPSlide.Shapes.PasteSpecial (ppPasteEnhancedMetafile)

    • Joao ACK says:

      Mike, did you find a solution for this problem? I am having the same!

      • Mike says:

        Have no solution, but it seems to be less prevalent if you close all other Microsoft programs while it is running, including File Explorer.

  67. marvin says:

    Is there a way to force the program to arrange charts by order from which they are located?  If you cut and paste the first chart and execute the macro, it is now the second chart (based on it's last active postion).  If not by modifying the program, is there a way to change the active arrangements of the charts some other way.  Cheers

     

  68. Coco says:

    When I tried to run this macro in Excel 2007, I received this error message:

    Run-time error '429':

    ActiveX component can't create object

    I have a range of cells (formatted as tables) that need to be copied from a named worksheet (for this post - the worksheet name is 'Summary') and then pasted (with formatting) into an existing powerpoint presentation on a new slide that will allow me to edit.  Can the code be modified to accomplish this task and get rid of the error?

  69. Hello, I have a excel workbook with multiple sheets that I want to put into PowerPoint that when runs will loop through all sheets so it can be displayed on a hallway monitor. I would like the PPT to change as the information changes in excel sheet. The sheets are updated at beginning of every shift (x3 shifts). This will allow clients to see this information. I have a no budget limit so I am trying to get it done using excel and PowerPoint. At this point I do not have anything other than data in the sheets but will be adding pictures and charts as needed in the near future. Is it possible to just link the sheets, in current order in the workbook to show the sheet and its contents: data, picture and or chart full screen one sheet per slide?  I have read this entire post and the knowledge here is staggering. Any help with this would be greatly appreciated by all those entering them and printing them out each day and shift.
    John

  70. hеy i гead thrоugh thіs anԁ i am neω to asp.
    nеt... i аm tгying to dеvеlоp my
    fiгst аpp in іt and this is veгy helpful.

    Thank уοu foг the tіme уou spent to
    write this chеcklіst. your аwesomе.

  71. satya says:

    Instead of this we can use a paste link option on the paste special... If it is a regular report.

  72. sanjay kumar says:

    i want 5 charts in slide
    How can we do this?
    Give VBA Code for this.
    It's very important for me. Plz help

  73. Carlos says:

    GREAT POST..

    Works like a charm!

  74. Rune says:

    Can you please send me the working file?

    Regards

  75. John says:

    I have that particular project being worked on by another GURU at this time. I do however have another project that has to do with dynamic arrays and print macro that is just as mind boggling to me if your interested. I have it posted on may 17th. If your interested I have the working file for it and I would be glad to get help on.

    John

  76. Jaggu says:

    Hello,

    Nice article and nice Q&A along with nice solution. Here is my question(s)

    1. I am preparing sales collaterals. One common requirement I have from engineering team is case studies. Case study data changes from time to time (as projects progress.) Asking engineering team to prepare a new slide on case study everytime a customer presentation is to be made is waste of their time.

    2. I have a template for case study in power point. (Basically empty shapes and to be filled with bullted text.) Number of shapes and which shape should contain what text and what data is fixed.

    3. I have a excel template to capture the engineering projects. This template is extended version of their project review template. Hence engineering team populates it as part of their review meeting.

    4. What I want to do is
    a. Filter and select the case studies I want to include.
    b. Run a macro such that using the selected case studies, the shapes in the case study template are populated and a stack of slides for case study is generated.

    Question:
    1. Is it possible to fill in shapes (mainly text boxes in a powerpoint) slide) using VBA macros?
    2. has anyone attempted it and a solution is published?
    3. Can anyone help?

    regards,

    Jaggu

  77. Dolphin Godfred says:

    Jaggu,

    Hope I am not late for you.

    I have worked on similar kind of thing, but with PivotTables. When you mention filter I assume it is just a Data > Filter on Excel and not PivotTable filter. You can enter text into shapes (textboxes) using VBA. Below is the code:

    Dim SlideTitle As String
    SlideTitle = "Your Title Goes Here"
    ActiveSlide.Shapes(1).TextFrame.TextRange.Text = SlideTitle

    The shape number, in this case, 1, will change based on which shape (textbox) you want to enter. Note that to get a shape number of the desired textbox can take some time. You try this on a trial and error method.

    Let me know if you require further more help on this.

    - DG

    • jen says:

      Hi. I'm trying to figure out how to do the same thing in the tutorial but with pivot tables. I have a problem selecting and copying/pasting to the powerpoint. Every time I try selecting, I get a runtime error 458. Can you please help? Thank you1

      • Dolphin Godfred says:

        Hi Jen,

        Can you let me know where exactly you are having this error? Also, would it be possible to share your code? Please let me know.

        - DG.

        • jen says:

          Hi DG,

          I actually solved the issue. I will post the code anyway.

          I have a question that's unrelated to this tutorial though. I want to create several pivot tables based on the values from three comboboxes. The comboboxes act like the pivot table filters and also a counter for how many pivot tables to make. I just want to know how I would go about programming this.

          Code:
          Sub CreatePowerPoint()
          Cells.Select
          Range("D47").Activate
          Selection.Columns.AutoFit
          'Add a reference to the Microsoft PowerPoint Library by:
          '1. Go to Tools in the VBA menu
          '2. Click on Reference
          '3. Scroll down to Microsoft PowerPoint X.0 Object Library, check the box, and press Okay

          'First we declare the variables we will be using
          Dim newPowerPoint As PowerPoint.Application
          Dim activeSlide As PowerPoint.Slide
          Dim cht As Excel.PivotTable

          'Look for existing instance
          On Error Resume Next
          Set newPowerPoint = GetObject(, "PowerPoint.Application")
          On Error GoTo 0

          'Let's create a new PowerPoint
          If newPowerPoint Is Nothing Then
          Set newPowerPoint = New PowerPoint.Application
          End If
          'Make a presentation in PowerPoint
          If newPowerPoint.Presentations.Count = 0 Then
          newPowerPoint.Presentations.Add
          End If

          'Show the PowerPoint
          newPowerPoint.Visible = True

          i = 1

          'Loop through each chart in the Excel worksheet and paste them into the PowerPoint
          For Each cht In ActiveSheet.PivotTables

          'Add a new slide where we will paste the chart
          newPowerPoint.ActivePresentation.Slides.Add newPowerPoint.ActivePresentation.Slides.Count + 1, ppLayoutText
          newPowerPoint.ActiveWindow.View.GotoSlide newPowerPoint.ActivePresentation.Slides.Count
          Set activeSlide = newPowerPoint.ActivePresentation.Slides(newPowerPoint.ActivePresentation.Slides.Count)

          'Copy the chart and paste it into the PowerPoint as a Metafile Picture

          Cells(i, 7).Interior.Color = 44
          i = i + 1
          cht.PivotSelect "", xlDataAndLabel
          Selection.Copy
          activeSlide.Shapes.PasteSpecial(DataType:=ppPasteMetafilePicture).Select

          'Set the title of the slide the same as the title of the chart
          activeSlide.Shapes(1).TextFrame.TextRange.Text = cht.Name

          'Adjust the positioning of the Chart on Powerpoint Slide
          newPowerPoint.ActiveWindow.Selection.ShapeRange.Left = 15
          newPowerPoint.ActiveWindow.Selection.ShapeRange.Top = 125

          activeSlide.Shapes(2).Width = 200
          activeSlide.Shapes(2).Left = 505

          Next

          AppActivate ("Microsoft PowerPoint")
          Set activeSlide = Nothing
          Set newPowerPoint = Nothing

          End Sub

  78. Andy says:

    Hi,

    this is great, but when
    I open excell file on my Mac, I'm gettint error and presentation cannot be done 🙁

    Could you please help me?

    Thank you,
    Andy

  79. Clarence says:

    This is EXACTLY what I am needed. I have been stumbling on trying to create PPT slides with specific ranges based on user input in Excel. I have a Sub to find the list of ranges to copy but have not been able to get them into PPT. I've tried a few other blogs with not much help. This one works PERFECTLY!
    Thank you for sharing!

  80. GD says:

    Hi,
    I am new to VBA. I am unable to plot more charts. If i add a new tab, it copies the chart however fail to copty the comments. I repleated the code and edited. But, Its shwoing error. Pleas ehelp!!

  81. Manas P says:

    It copies just the first chart and then it stops giving the following error - Getting Error code 424: Object not found.

    Debug points out fail at this statement -

    activeSlide.Shapes.PasteSpecial(DataType:=ppPasteMetafilePicture).Select

    • Dolphin Godfred says:

      Hi Manas,

      Try this:
      Before the line
      (CODE=VB)activeSlide.Shapes.PasteSpecial(DataType:=ppPasteMetafilePicture).Select(/CODE)

      type the following
      (CODE=VB)activeSlide.Select(/CODE)

      Let me know if this works.

      -DG

  82. Geliedee says:

    Hi all.. Im working on how to export pivot tablesfrom excel to powerpoint. Any one can help?

    Im using this code but in this part Set oPPTShape = oPPTFile.Slides(SlideNum).Shapes("PivotTable6")"it says that pivot table is not part of shapes? Please help.....

    Sub PPTableMacro()

    Dim strPresPath As String, strExcelFilePath As String, strNewPresPath As String
    strPresPath = "C:\Users\angeline.descalsota\Desktop\AUTOMATION\TransferFailurePPFile.pptx"
    strNewPresPath = "C:\Users\angeline.descalsota\Desktop\AUTOMATION\TransferFailurePPFile.pptx"

    Dim oPPTShape As DataTable

    Set oPPTApp = CreateObject("PowerPoint.Application")
    oPPTApp.Visible = msoTrue
    Set oPPTFile = oPPTApp.Presentations.Open(strPresPath)
    SlideNum = 1
    oPPTFile.Slides(SlideNum).Select

    Set oPPTShape = oPPTFile.Slides(SlideNum).Shapes("PivotTable6")

    Sheets("Sheet1").Activate
    With oPPTShape.Table
    .Cell(1, 1).Shape.TextFrame.TextRange.Text = Cells(1, 1).Text
    .Cell(1, 2).Shape.TextFrame.TextRange.Text = Cells(1, 2).Text
    .Cell(1, 3).Shape.TextFrame.TextRange.Text = Cells(1, 3).Text
    .Cell(2, 1).Shape.TextFrame.TextRange.Text = Cells(2, 1).Text
    .Cell(2, 2).Shape.TextFrame.TextRange.Text = Cells(2, 2).Text
    .Cell(2, 3).Shape.TextFrame.TextRange.Text = Cells(2, 3).Text
    End With

    oPPTFile.SaveAs strNewPresPath
    oPPTFile.Close
    oPPTApp.Quit

    Set oPPTShape = Nothing
    Set oPPTFile = Nothing
    Set oPPTApp = Nothing

    MsgBox "Presentation Created", vbOKOnly + vbInformation
    End Sub

  83. Daniel says:

    Hi!
    Works like a charm! However, I'd like to make a small adjustment and need some help. My goal is to use the same code but clicking the chart itself instead of pushing a button. The point is to only export the chart(s) selected by clicking them one at a time (each slide contains alot of charts).

    This part should probably be deleted if possible. I would be annoying to have to switch windows after each click:
    'Show the PowerPoint
    newPowerPoint.Visible = True

    I know nothing of VBA but learned some basic programming about 15 years ago so I understand to broad strokes. Please help 🙂

    • Blank says:

      Hi Daniel,

      I did a small bit of playing around and found that there is no simple solution to your problem given Microsoft’s limited capability with their software. The only solution I have found so far that works in a way of how you want it is the following.

      1) Each chart cannot be an object on a sheet it needs to be in a chart by itself.

      2) Copy and paste the following code into each charts code (rename the variable if you desire)

      Dim ClassMod As New ChartEvents

      Private Sub Chart_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
      Set ClassMod.Excht = ActiveChart
      End Sub

      3) Create a class named ChartEvents (unless you are going to change the variables) and then copy and paste the following modified code (Originally the code Drew posted)

      Public WithEvents Excht As Chart

      Private Sub Excht_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)

      'Add a reference to the Microsoft PowerPoint Library by:
      '1. Go to Tools in the VBA menu
      '2. Click on Reference
      '3. Scroll down to Microsoft PowerPoint X.0 Object Library, check the box, and press Okay

      'First we declare the variables we will be using
      Dim newPowerPoint As PowerPoint.Application
      Dim activeSlide As PowerPoint.Slide
      Dim cht As Application

      'Look for existing instance
      On Error Resume Next
      Set newPowerPoint = GetObject(, "PowerPoint.Application")
      On Error GoTo 0

      'Let's create a new PowerPoint
      If newPowerPoint Is Nothing Then
      Set newPowerPoint = New PowerPoint.Application
      End If
      'Make a presentation in PowerPoint
      If newPowerPoint.Presentations.Count = 0 Then
      newPowerPoint.Presentations.Add
      End If

      'Show the PowerPoint
      newPowerPoint.Visible = True

      'Add a new slide where we will paste the chart
      newPowerPoint.ActivePresentation.Slides.Add newPowerPoint.ActivePresentation.Slides.Count + 1, ppLayoutBlank
      newPowerPoint.ActiveWindow.View.GotoSlide newPowerPoint.ActivePresentation.Slides.Count
      Set activeSlide = newPowerPoint.ActivePresentation.Slides(newPowerPoint.ActivePresentation.Slides.Count)

      'Copy the chart and paste it into the PowerPoint as a Metafile Picture
      ActiveChart.ChartArea.Copy
      activeSlide.Shapes.PasteSpecial(DataType:=ppPasteMetafilePicture).Select

      'Adjust the positioning of the Chart on Powerpoint Slide
      newPowerPoint.ActiveWindow.Selection.ShapeRange.Left = 15
      newPowerPoint.ActiveWindow.Selection.ShapeRange.Top = 100

      activeSlide.Shapes(1).Width = 500
      activeSlide.Shapes(1).Left = 115

      AppActivate ("Microsoft PowerPoint")
      Set activeSlide = Nothing
      Set newPowerPoint = Nothing
      End Sub

      4) Make sure to save your workbook as a macro enabled excel file or else you will have to do it all over again.

      When you change your chart and then click on the chart the first time it will create a new presentation with the chart centered for the most part (fine tune the size and location as desired). Presently this is the only way I have discovered to accomplish this. If there was a way to create a custom handle for objects on a excel sheet this would have been easier.

      I hope this helps.
      Blank

  84. Blank says:

    Hi Daniel,

    I did a small bit of playing around and found that there is no simple solution to your problem given Microsoft’s limited capability with their software. The only solution I have found so far that works in a way of how you want it is the following.

    1) Each chart cannot be an object on a sheet it needs to be in a chart by itself.

    2) Copy and paste the following code into each charts code (rename the variable if you desire)

    Dim ClassMod As New ChartEvents

    Private Sub Chart_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
    Set ClassMod.Excht = ActiveChart
    End Sub

    3) Create a class named ChartEvents (unless you are going to change the variables) and then copy and paste the following modified code (Originally the code Drew posted)

    Public WithEvents Excht As Chart

    Private Sub Excht_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)

    'Add a reference to the Microsoft PowerPoint Library by:
    '1. Go to Tools in the VBA menu
    '2. Click on Reference
    '3. Scroll down to Microsoft PowerPoint X.0 Object Library, check the box, and press Okay

    'First we declare the variables we will be using
    Dim newPowerPoint As PowerPoint.Application
    Dim activeSlide As PowerPoint.Slide
    Dim cht As Application

    'Look for existing instance
    On Error Resume Next
    Set newPowerPoint = GetObject(, "PowerPoint.Application")
    On Error GoTo 0

    'Let's create a new PowerPoint
    If newPowerPoint Is Nothing Then
    Set newPowerPoint = New PowerPoint.Application
    End If
    'Make a presentation in PowerPoint
    If newPowerPoint.Presentations.Count = 0 Then
    newPowerPoint.Presentations.Add
    End If

    'Show the PowerPoint
    newPowerPoint.Visible = True

    'Add a new slide where we will paste the chart
    newPowerPoint.ActivePresentation.Slides.Add newPowerPoint.ActivePresentation.Slides.Count + 1, ppLayoutBlank
    newPowerPoint.ActiveWindow.View.GotoSlide newPowerPoint.ActivePresentation.Slides.Count
    Set activeSlide = newPowerPoint.ActivePresentation.Slides(newPowerPoint.ActivePresentation.Slides.Count)

    'Copy the chart and paste it into the PowerPoint as a Metafile Picture
    ActiveChart.ChartArea.Copy
    activeSlide.Shapes.PasteSpecial(DataType:=ppPasteMetafilePicture).Select

    'Adjust the positioning of the Chart on Powerpoint Slide
    newPowerPoint.ActiveWindow.Selection.ShapeRange.Left = 15
    newPowerPoint.ActiveWindow.Selection.ShapeRange.Top = 100

    activeSlide.Shapes(1).Width = 500
    activeSlide.Shapes(1).Left = 115

    AppActivate ("Microsoft PowerPoint")
    Set activeSlide = Nothing
    Set newPowerPoint = Nothing
    End Sub

    4) Make sure to save your workbook as a macro enabled excel file or else you will have to do it all over again.

    When you change your chart and then click on the chart the first time it will create a new presentation with the chart centered for the most part (fine tune the size and location as desired). Presently this is the only way I have discovered to accomplish this. If there was a way to create a custom handle for objects on a excel sheet this would have been easier.

    I hope this helps.
    Blank

  85. Julian says:

    Very interesting approach. This article is old but looks interesting to me even today. I envision to create dashboards automatically in PowerPoint using this method and getting the data from Excel but with one of our PowerPoint templates.

    Julian @ SlideModel

  86. Vicky says:

    Hi,

    This really a very nice post and saved me a lot of time.

    Thanksss sooo much guys for posting this!!.

    I have 32 graphs, to be pasted on 16 slides ( 2 slide each) and some static introductory slides. in total 19 slides.

    Please help me on this one.

    Thanks in advance
    Vicky

  87. Juanjo says:

    hi, i having problems with callout text , if I try to add more text this doesnt take in account, how you deal with this or wich is the trick?

    regards

  88. Wouter says:

    Wonderfull trick, will save me a lot of effort 🙂

    I would like to name the generated ppt depending on the chart title.
    can anybody tell me how?

    • Dolphin Godfred says:

      @Wouter: Check if the below code works for you:

      [code]
      If Worksheets("Sheet1").ChartObjects(1).Chart.HasTitle Then
      strChartTitle = Worksheets("Sheet1").ChartObjects(1).Chart.ChartTitle.Text
      Else
      strChartTitle = "My Chart Title"
      End If
      [/code]

      Change the name of the Sheet where you have the chart. Let me know if this works.

      - DG

  89. Wouter says:

    hm I want to change the name of the PPT not the sheet, will this do the trick?

    • Dolphin Godfred says:

      Hi Wouter,

      Please see below:
      Assuming you have Test folder in C Drive,

      For 97-2003 PowerPoint file, use
      newPowerPoint.ActivePresentation.SaveAs "C:\Test\Test.ppt"

      For 2007 and above PowerPoint file, use
      newPowerPoint.ActivePresentation.SaveAs "C:\Test\Test.pptx"

      Hope this helps.

      - DG

  90. Karthik says:

    i want this method in ms access vba can u help me Please?

  91. Subodh says:

    Thanks Drew for the code but i am getting error at line "Dim newPowerPoint As PowerPoint.Application". Error box showing message as Complie error Useer-defined type not defined.
    Can you tell me whats the problem ?

    • Dolphin Godfred says:

      Hi Subodh,

      I may be late. Probably you may need to add the reference of Microsoft PowerPoint XX.0 (whichever version you are having). To add, do the following:
      In Visual Basic Editor, Click on Tools > References then look for Microsoft PowerPoint under the list in Available References. Hope this helps.

      Regards,
      DG

  92. Steve F says:

    Chandoo,

    Thanks for your work. Just upgraded from 2007 to 2010 at the office and the macros did not work. Turns out copying and pasting in VBA IS different between 2007 and 2010. I was pasting both tables first copied as pictures in excel and then charts copied to PP. File had about 50 images, and was blowing up from 2M to 13M! This gave me some insight into how to address this. The same copy and paste command does not work for copying table ranges and chart images. Furthermore, your pastespecial command for pasting into PP enabled me to research other data types and find one that got the file size back down.

    Thanks,
    Steve

  93. Vishwanath says:

    Hi,

    I have a excel sheet named "Graph" containing charts in matrix form
    30 rows and 5 columns(ie 5 charts in first row, 5 charts in 2nd row and so on).
    I want to make a powerpoint with 9 charts in each slide(5 charts from first row and remaining 4 charts from 2nd row).
    Somebody please help me.

  94. MEDA says:

    how to get this pattern using macro vb code
    please help me with code,thanks in advance
    1
    12
    123
    1234
    12345
    123456
    12345
    1234
    123
    12
    1

    • Hui... says:

      Sub Make_No_Pyramid()
      Dim MaxNo
      Dim i As Integer, j As Integer

      MaxNo = InputBox("What is the Maximum Number", "What is teh Maximum Number")

      Range("A:A").ClearContents
      For i = 1 To MaxNo - 1
      For j = i To MaxNo - 1
      Cells(j, 1) = Cells(j, 1).Text & CStr(i)
      Cells((2 * MaxNo) - j, 1) = Cells((2 * MaxNo) - j, 1).Text & CStr(i)
      Next j
      Next i
      For i = 1 To MaxNo
      Cells(MaxNo, 1) = Cells(MaxNo, 1).Text & CStr(i)
      Next i
      Columns("A:A").HorizontalAlignment = xlLeft

      End Sub

  95. Grace says:

    Hi,

    My excel charts are generated when I select respective countries from a drop-down list in my data sheet. Is there a way I can incorporate this into the dashboard, i.e., create a button for country and when I select "Singapore", all charts are showing Singapore data and I can export to PPT?

    Similarly, how to create the button for month? i.e., select "Jan 15" and charts is generated with Jan 15 as the last data point?

    Appreciate all help/answers! 🙂

    Thanks,
    Grace

    • Jesus says:

      If possible I would suggest using slicers to change the country selection and then a mcaro to control the slicer.

    • Jesus says:

      After I migrated to Office 2013 the code has been having trouble due to Powor Point freezing at some point while pasting charts. I've tried using Application.Wait in multiple parts of the code to allow Power Point enough time to copy and paste. However it is still crashing. Does any one have this same issue? How can I fix it?

  96. jim says:

    using Drew's post in excel 2013 ... and getting error 429
    the GetObject is failed line ...
    'Look for existing instance
    On Error Resume Next
    Set newPowerPoint = GetObject(, "PowerPoint.Application")
    On Error GoTo 0

    so, launched PP manually and got around the 429.

    Now getting 424 error here :
    'Copy the chart and paste it into the PowerPoint as a Metafile Picture
    cht.Select
    ActiveChart.ChartArea.Copy
    activeSlide.Shapes.PasteSpecial(DataType:=ppPasteMetafilePicture).Select

  97. lonestardave says:

    I got this to work and it is GREAT but the text boxes for the powerpoint are linked directly to certain cells in excel. Is there a way to add the text to the slides in an easier manner? I want to create a template that any user could add into an excel workbook and it would add text located next to the charts no matter how many slides there were if there is text next to the chart. Or could someone point me in the right direction or website to figure out how to do this? Thank you in advance.

    • Dolphin G says:

      @lonestardave: If you have a textbox/textframe on the slide, then the below code should work (if it is PowerPoint 2007/2010):
      [CODE]ActiveSlide.Shapes(2).TextFrame.TextRange.Text = "Test line 1" & vbcr & "Test line 2"[/CODE]

      The number 2 in Shapes(2) can change based on the number of objects you placed in the slide. The number 1 can/will be the header of the slide and post that how many ever textboxes/frames you have. You have to check this on trial and error basis.

      Let me know if this works.

      -DG.

  98. Mohamed says:

    This has been incredibly useful, I have implemented this in many powerpoints and saved roughly 4 hours a month i reckon. I am not quite done yet though, im using excel 2003 and when i try to resize the pasted image via :

    mySlide.Shapes.PasteSpecial DataType:=ppPasteEnhancedMetafile
    Set myShapeRange = mySlide.Shapes(mySlide.Shapes.Count)
    'Set position:
    myShapeRange.Left = 234
    myShapeRange.Top = 186

    , I don't get an error or anything but its just that controlling height and width don't work properly, this stops me from formatting the chart sizes. Any clue to whats going on ? solutions?

    Thanks!

  99. Chuck says:

    I have Office 2010 and have been integrating to TFS via Excel and subsequently generating Powerpoints through Excel for several months now, using VBA. However, i was upgraded to Project 2013 at the end of January and the integration between both TFS and Powerpoint from within Excel were broken. I found a registry key that made Excel think it was 2013 so it was looking for the wrong libraries to connect with TFS. Removing this key solved the TFS issue. However, I still have not figured out how to solve the error that prevents me from generating Powerpoints. I receive the following error

    Runtime error '-2147319779 (8002801d)'

    Automation error
    Library not registered

    Any help or direction with this would be much appreciated.

    Thank you,
    Chuck

  100. Deepak says:

    Hi all, This is JUST what I needed and was looking for!!

    However, I am getting the following -

    Run-time error: '2147188160 (80048240)':
    Application (unknown member) : Invalid request. There is no active presentation.

    What am I doing wrong?

    • Amjad mahmood says:

      I have managed to make use of this wonderful aspect of excel VBA..
      If you still need my help I am available today...

      awaiting for your response...lets do it with close interaction..
      Amjad

  101. Amjad mahmood says:

    Hi,
    Anybody whose query is still pending, please let me know...
    I have managed to make use of this wonderful aspect of excel VBA..
    If you still need my help I am available today...

    awaiting for your response...lets do it with close interaction..
    Amjad

  102. Kapish says:

    I am getting the following error when I am trying to run this code

    Compile Error: User Type Not Defined

    Can anyone help with this?

  103. Milena says:

    Chadoo,

    How do I make the macro paste each chart on an specific slide?

  104. ravi janjwadia says:

    Hello,

    I want to get the images rather charts in the slides.
    What changes should I make in the vba code?

  105. Chintu says:

    Hi Chandoo, Can we automate alignment of data labels for any charts ?

    • Hui... says:

      @Chintu
      If you setup a chart exactly how you want it
      Then save that chart as a template

      You can apply that template to future charts and get the same styles, layout, alignments, colors etc

  106. Ron says:

    I have a vba button on sheet 1 with charts on sheet 16. I want to click the button on sheet 1 to run the charts on sheet 16. Currently, you can only run the code if you are on the active sheet.

  107. Glenn H says:

    Ron (ref your post 08/17/2016),

    I don't understand what "run the charts" means? Can you enlighten me please?

    • Ron says:

      The code written only works if you are on that active sheet. I want to click a macro button to activate the code. A button will be placed on sheet 1 and the charts will be a sheet 16. I want people to click the button an create a powerpoint with the charts on sheet 16.

Leave a Reply