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

Printable View

06-29-2004, 10:57 AM
shades

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.
02-12-2005, 03:27 AM
Jacob Hilderbrand

This would make a great KB Entry. :reading:
02-18-2005, 08:42 AM
cmpgeek

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!
02-26-2005, 02:08 PM
Anne Troy

Did this ever become a kb entry?
03-04-2005, 07:30 PM
brettdj

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
03-06-2005, 08:00 AM
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/
_______
03-06-2005, 10:13 AM
Anne Troy

If it's Jon's code, perhaps we can persuade him to write it to the KB when he has some free time. :rofl
03-07-2005, 01:34 AM
brettdj

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
03-22-2005, 07:09 PM
brettdj

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>
03-23-2005, 09:19 AM
wene

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:p
03-23-2005, 09:21 AM
Anne Troy

Code edited to it doesn't stretch width of page.
03-23-2005, 09:59 AM
JonPeltier

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:p

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/
_______
03-23-2005, 04:21 PM
brettdj

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
03-23-2005, 04:31 PM
JonPeltier

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
03-24-2005, 04:42 AM
wene

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!
03-24-2005, 05:33 AM
JonPeltier

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/
_______
01-20-2006, 04:21 AM
Brandy_in_Re

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? :think:
01-20-2006, 11:42 AM
Zack Barresse

Hey Brandy, in the Knowledge Base article (here) scroll to the bottom and there are directions for making the code work. :)
01-23-2006, 04:25 AM
Brandy_in_Re

Unbelievable ! :eek: : pray2: :clap: :yes
It's working ! Thank you, everybody :bow: