Consulting

Results 1 to 19 of 19

Thread: Advice: Automating Chart and Range to PPT (from XL)

  1. #1
    Mac Moderator VBAX Expert shades's Avatar
    Joined
    May 2004
    Location
    Kansas City, USA
    Posts
    638
    Location

    Advice: Automating Chart and Range to PPT (from XL)

    I don't remember where I got this code, but it allows a user to select a chart (or range) in Excel, and it will immediately place a copied picture into PPT. Since I do this many times a day, I attached each to a button the main Toolbar, and it is always a click away.

    [vba]
    Option Explicit
    Sub RangeToPresentation()
    ' Set a VBE reference to Microsoft PowerPoint 10.0 Object Library for Office 2002,
    Dim PPApp As PowerPoint.Application
    Dim PPPres As PowerPoint.Presentation
    Dim PPSlide As PowerPoint.Slide
    ' Make sure a range is selected
    If Not TypeName(Selection) = "Range" Then
    MsgBox "Please select a worksheet range and try again.", vbExclamation, _
    "No Range Selected"
    Else
    ' Reference existing instance of PowerPoint 2002
    Set PPApp = GetObject(, "Powerpoint.Application.10")
    ' Reference active presentation
    Set PPPres = PPApp.ActivePresentation
    PPApp.ActiveWindow.ViewType = ppViewSlide
    ' Reference active slide
    Set PPSlide = PPPres.Slides(PPApp.ActiveWindow.Selection.SlideRange.SlideIndex)

    ' Copy the range as a piicture
    Selection.CopyPicture Appearance:=xlScreen, _
    Format:=xlPicture
    ' Paste the range
    PPSlide.Shapes.Paste.Select

    ' Align the pasted range
    PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
    PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
    ' Clean up
    Set PPSlide = Nothing
    Set PPPres = Nothing
    Set PPApp = Nothing
    End If
    End Sub


    Sub ChartToPresentation()
    ' Set a VBE reference to Microsoft PowerPoint 10.0 Object Library for Office 2002,
    Dim PPApp As PowerPoint.Application
    Dim PPPres As PowerPoint.Presentation
    Dim PPSlide As PowerPoint.Slide
    ' Make sure a chart is selected
    If ActiveChart Is Nothing Then
    MsgBox "Please select a chart and try again.", vbExclamation, _
    "No Chart Selected"
    Else
    ' Reference existing instance of PowerPoint 2002
    Set PPApp = GetObject(, "Powerpoint.Application.10")
    ' Reference active presentation
    Set PPPres = PPApp.ActivePresentation
    PPApp.ActiveWindow.ViewType = ppViewSlide
    ' Reference active slide
    Set PPSlide = PPPres.Slides _
    (PPApp.ActiveWindow.Selection.SlideRange.SlideIndex)

    ' Copy chart as a picture
    ActiveChart.CopyPicture Appearance:=xlScreen, Size:=xlScreen, _
    Format:=xlPicture
    ' Paste chart
    PPSlide.Shapes.Paste.Select

    ' Align pasted chart
    PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
    PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
    ' Clean up
    Set PPSlide = Nothing
    Set PPPres = Nothing
    Set PPApp = Nothing
    End If
    End Sub

    [/vba]

    Hope others find this helpful.

    Software: LibreOffice 3.3 on Mac OS X 10.6.5
    (retired Excel 2003 user, 3.28.2008 )
    Humanware: Older than dirt
    --------------------
    old, slow, and confused
    but at least I'm inconsistent!

    Rich

  2. #2
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    This would make a great KB Entry.

  3. #3
    VBAX Tutor cmpgeek's Avatar
    Joined
    Jun 2004
    Location
    Athens, Ga USA
    Posts
    204
    Location
    i was thinking that same thing! Not to mention that i am in the middle of a Statistics class and we are working on a PP presentation right now...



    thanx shades!



    Life is like a jar of jalepenos... What you do with it today might burn your butt tomorrow....

  4. #4
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Did this ever become a kb entry?
    ~Anne Troy

  5. #5
    Knowledge Base Approver VBAX Expert brettdj's Avatar
    Joined
    May 2004
    Location
    Melbourne
    Posts
    649
    Location
    It looks similar to the code from Jon Peltiers site, I've used it before. Very handy

    I'm currently writing code to do this as a picture, chart link, html etc for both powerpoint and word at EE, see http://oldlook.experts-exchange.com:..._21337053.html. If Shades doesn't write it up then I will

    Cheers

    Dave

  6. #6
    MS Excel MVP VBAX Tutor
    Joined
    Mar 2005
    Posts
    246
    Location
    I was going to say that it looks familiar. Same variable names and everything.

    Glad it's working.

    - Jon
    -------
    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services
    Tutorials and Custom Solutions
    http://PeltierTech.com/
    _______


  7. #7
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    If it's Jon's code, perhaps we can persuade him to write it to the KB when he has some free time. :rofl
    ~Anne Troy

  8. #8
    Knowledge Base Approver VBAX Expert brettdj's Avatar
    Joined
    May 2004
    Location
    Melbourne
    Posts
    649
    Location
    Quote Originally Posted by JonPeltier
    I was going to say that it looks familiar. Same variable names and everything.

    Glad it's working.

    - Jon
    -------
    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services
    Tutorials and Custom Solutions
    http://PeltierTech.com/
    _______

    Jon, its good to see you here. It's a great site you have, especially the graph section.

    DB,

    I've finished the excel to powerpoint code (in 2003) at the EE link with a few different options (linked, non-link, picture, HTML), see http://experts-exchange.com/Q_21337053.html

    I've also pulled together a word example, http://experts-exchange.com/Q_21339980.html

    I'll write these up unless Jon wants to jump in first, his code was the basis of my original attempt

    Cheers

    Dave

  9. #9
    Knowledge Base Approver VBAX Expert brettdj's Avatar
    Joined
    May 2004
    Location
    Melbourne
    Posts
    649
    Location
    Excel to PowerPoint routine written up at

    It includes the following options/examples

    - copy Range or Graph
    - copy object either Link or Unlinked
    - use extisting instance or create new instance
    - add a new slide or use last slide

    Cheers

    Dave

    <KB position being updated - will be linked when complete>

  10. #10
    VBAX Newbie
    Joined
    Mar 2005
    Posts
    5
    Location

    and... save it

    I'd like to save that pasted object as GIF, so I recorded it in powerpoint:

    [VBA]
    ActivePresentation.Save Filename:="C:\WINNT\Profiles\srzxww\Desktop\Picture1.gif", _
    FileFormat:=ppSaveAsGIF, EmbedTrueTypeFonts:=msoFalse
    [/VBA]

    and customized:

    [VBA]PPApp.ActivePresentation.Save Filename:="C:\WINNT\Profiles\srzxww\Desktop\Picture1.gif", _
    FileFormat:=ppSaveAsGIF, EmbedTrueTypeFonts:=msoFalse[/VBA]

    but, it is not working, it throws: "Invalid enumeration value"

    HELP

  11. #11
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Code edited to it doesn't stretch width of page.
    ~Anne Troy

  12. #12
    MS Excel MVP VBAX Tutor
    Joined
    Mar 2005
    Posts
    246
    Location
    Quote Originally Posted by wene
    I'd like to save that pasted object as GIF, so I recorded it in powerpoint:

    [VBA]
    ActivePresentation.Save Filename:="C:\WINNT\Profiles\srzxww\Desktop\Picture1.gif", _
    FileFormat:=ppSaveAsGIF, EmbedTrueTypeFonts:=msoFalse
    [/VBA]

    and customized:

    [VBA]PPApp.ActivePresentation.Save Filename:="C:\WINNT\Profiles\srzxww\Desktop\Picture1.gif", _
    FileFormat:=ppSaveAsGIF, EmbedTrueTypeFonts:=msoFalse[/VBA]

    but, it is not working, it throws: "Invalid enumeration value"

    HELP
    This should be .SaveAs, not .Save. Also, if you have not set a reference to PowerPoint, and you do not explicitly declare variables (how could anyone not? and how could this be the default setting?), ppSaveAsGIF will be an undeclared variable, not the PowerPoint constant, and will be assigned a value of 0, which does not match any of the enumerated ppFileType constants.

    - Jon
    -------
    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services
    Tutorials and Custom Solutions
    http://PeltierTech.com/
    _______

  13. #13
    Knowledge Base Approver VBAX Expert brettdj's Avatar
    Joined
    May 2004
    Location
    Melbourne
    Posts
    649
    Location
    Excel to PowerPoint KB written up at http://www.vbaexpress.com/kb/getarticle.php?kb_id=370

    Jon, any comments you may have would be welcomed

    Cheers

    Dave

  14. #14
    MS Excel MVP VBAX Tutor
    Joined
    Mar 2005
    Posts
    246
    Location
    Dave -

    When I get a chance I'll look it over. I suddenly got real busy.

    If I haven't commented within a couple weeks, PM me to remind me.

    - Jon

  15. #15
    VBAX Newbie
    Joined
    Mar 2005
    Posts
    5
    Location
    Great it works,

    The answer to my issue is: use 16 instead of ppSaveAsGIF

    because, excel does not this variable, therefore it treats it as an undeclared variable and throws an enumaration error. By using the constant (think this is the correct term for the 'number' 16) this issue is solved.

    And SaveAs was a copy/paste mistake...

    Thanks!

  16. #16
    MS Excel MVP VBAX Tutor
    Joined
    Mar 2005
    Posts
    246
    Location
    Quote Originally Posted by wene
    ... because, excel does not this variable, therefore it treats it as an undeclared variable and throws an enumaration error.
    This is why you have to require declaration of all variables, so it errors at compile time, not run time. It should be a default setting, but the MS VBE team chose the opposite back in 1995 or so.

    In the VB Editor, go to the Tools menu, and choose Options. On the Editor tab, check the box for Require Variable Declaration.

    This puts "Option Explicit" at the top of every new code module. You should go to all of your existing code modules and type the line "Option Explicit" at the top.

    When you try to compile or run code with an undeclared variable, the variable will be highlighted and you will get a compile error. Undeclared variables may come from many places and may have many consequences if not handled: lazy programming in which you don't bother using Option Explicit (which the task above will prevent), pasted in code with constants in an unreferenced library (like ppSaveAsGIF), variables which you've misspelled (accidentally typing ppSilde when you've declared ppSlide, which would be treated as a new variable, value zero, without Option Explicit).

    - Jon
    -------
    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services
    Tutorials and Custom Solutions
    http://PeltierTech.com/
    _______

  17. #17
    Hello folks,

    I came here from http://www.mrexcel.com/board2/viewto...=914174#914174.

    Would ypu explain me what I have to do with that code, you show before?
    Easy and cheap PowerPoint Templates
    And my My own blog Leave the comments, pls !

  18. #18
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Hey Brandy, in the Knowledge Base article (here) scroll to the bottom and there are directions for making the code work.

  19. #19
    Unbelievable !
    It's working ! Thank you, everybody
    Easy and cheap PowerPoint Templates
    And my My own blog Leave the comments, pls !

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •