PDA

View Full Version : Export Chart as Graphic



cscribner
04-22-2006, 09:02 AM
in the VBA help file, when you search for "export chart", it offers this example:
Charts(1).Export _
FileName:="current_sales.gif", FilterName:="GIF"
But this throws me an error when I try to run it. I may have a clue why: It says that

Some examples of valid FilterName values are GIF, JPEG, and PNG.
On the Macintosh, graphics export filters are installed in the
Microsoft Office X:Shared Applications:Graphic Filters folder.
But when I look in that folder, I only have two files:

-EPS Import
-Metafile Import

Do you know where I can get more of these Microsoft file converter files? Or am I barking up the wrong tree? Can any of you use this code as-is?

shades
04-22-2006, 09:56 AM
in the VBA help file, when you search for "export chart", it offers this example:
Charts(1).Export _
FileName:="current_sales.gif", FilterName:="GIF"
But this throws me an error when I try to run it. I may have a clue why: It says that

Some examples of valid FilterName values are GIF, JPEG, and PNG.
On the Macintosh, graphics export filters are installed in the
Microsoft Office X:Shared Applications:Graphic Filters folder.
But when I look in that folder, I only have two files:

-EPS Import
-Metafile Import

Do you know where I can get more of these Microsoft file converter files? Or am I barking up the wrong tree? Can any of you use this code as-is?

Howdy. Not sure if this is helpful but this code works well (XL 2004, OS X 10.3.9); but it may not be what you need. It exports as picture to Powerpoint. The first is for a range, the second is for a chart.

Code from Jon Peltier's site (http://peltiertech.com/Excel/XL_PPT.html), where you can find additional examples.


Sub RangeToPresentation()
' Set a VBE reference to Microsoft PowerPoint Object Library for Office

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")
' 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 Object Library

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")
' 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

cscribner
04-22-2006, 11:55 AM
My goal is to place a picture of my graph inside of a Userform in a little budget application I'm building. As far as I can tell, I need to reference an image file somewhere on my computer if I want to display it in a userform. Is there any other way to make it display?

BlueCactus
04-25-2006, 04:04 PM
You have one or two problems:

a) Charts().Export doesn't seem to work on Mac

b) Userforms().LoadPicture() doesn't work unless you have Excel 2004 SP1 or higher.

The way I do it these days is to snap the userform to a small copy of the chart.

Haven't tried shade's code. And before you get too creative, I can tell you that Charts().CopyAsPicture has issues too (at least under Excel X / Mac OS 10.3.9).

cscribner
04-26-2006, 08:37 AM
I really appreciate you guys offering me these workarounds. BlueCactus, what exactly do you mean by "snapping a copy of the chart to the userform?" I'm not a very experienced VBA coder, so you may have to baby-step me through your meaning.

BlueCactus
04-26-2006, 06:57 PM
I really appreciate you guys offering me these workarounds. BlueCactus, what exactly do you mean by "snapping a copy of the chart to the userform?" I'm not a very experienced VBA coder, so you may have to baby-step me through your meaning.
Something rather complicated. I was writing a KB on it at one time. Try downloading the file to see what I mean. (I think this was the most recent version... :think: )

Edit: Also, I don't remember why I stopped working on this one.

cscribner
05-01-2006, 01:45 PM
BlueCactus, thanks for this. I haven't had time to try this on my Mac, but I'll do it soon, and I'm very grateful for a workaround on this issue.