Hi I have two related questions,

1) Is it possible to select an excel chart and save it as a separate object
that is that independent of the workbook it was created in?

2) Assuming this can be done - can I use VBA to overwrite this file when the
underlying data source changes?

Thanks,

Steve

One way
Sub ExportChartJPG()
ActiveChart.Export Filename:="C:\a\MyChart.jpg", _
FilterName:="jpeg"
End Sub

--
Don Guillett
SalesAid Software

"Stevie D" wrote in message
...
Hi I have two related questions,

1) Is it possible to select an excel chart and save it as a separate
object
that is that independent of the workbook it was created in?

2) Assuming this can be done - can I use VBA to overwrite this file when
the
underlying data source changes?

Thanks,

Steve

Thanks for your private email that it worked for you.

--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
One way
Sub ExportChartJPG()
ActiveChart.Export Filename:="C:\a\MyChart.jpg", _
FilterName:="jpeg"
End Sub

--
Don Guillett
SalesAid Software

"Stevie D" wrote in message
...
Hi I have two related questions,

1) Is it possible to select an excel chart and save it as a separate
object
that is that independent of the workbook it was created in?

2) Assuming this can be done - can I use VBA to overwrite this file when
the
underlying data source changes?

Thanks,

Steve

Hi Don, I got this question after reading your help. Do you think it
is possible to save charts to illustrator file format as a seperate
file? Thank you in advance!

try it. Just change the extensions.

--
Don Guillett
SalesAid Software

"crossingmind" wrote in message
oups.com...
Hi Don, I got this question after reading your help. Do you think it
is possible to save charts to illustrator file format as a seperate
file? Thank you in advance!

Sub ExportChartAI()
ActiveChart.Export Filename:="C:\a\MyChart.ai", _
FilterName:="ai"
End Sub

Hi Don, thanks for your reply. I tried this code, but it kept telling
me

ActiveChart.Export Filename:="C:\a\MyChart.ai", _
FilterName:="ai"

needs debug. Could you help take a look? Thank you again!

I think Excel only supports exports to jpg and gif.

"crossingmind" ¦b¶l¥ó
oups.com ¤¤¼¶¼g...
Sub ExportChartAI()
ActiveChart.Export Filename:="C:\a\MyChart.ai", _
FilterName:="ai"
End Sub

Hi Don, thanks for your reply. I tried this code, but it kept telling
me

ActiveChart.Export Filename:="C:\a\MyChart.ai", _
FilterName:="ai"

needs debug. Could you help take a look? Thank you again!

Herbert and others -

Excel supports jpg, gif, png, and tif. I recall mixed results with bmp, but you
don't need it if you have gif and png. Avoid jpg, which is not optimized for this
kind of graphic.

Excel does not support ai or eps. I have been hunting for an eps export solution,
and the best I can find (which I haven't tested) is to install a postscript printer
driver, locate the chart on its own chart sheet, adjust the margins so the chart is
the right size (the margins may be 3" on the sides and 2.5" top and bottom), and
print the chart sheet to a file using this driver. This produces a ps file, but it's
apparently interchangeable (or mostly so) with eps.

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

Herbert Chan wrote:

I think Excel only supports exports to jpg and gif.

"crossingmind" ¦b¶l¥ó
oups.com ¤¤¼¶¼g...

Sub ExportChartAI()
ActiveChart.Export Filename:="C:\a\MyChart.ai", _
FilterName:="ai"
End Sub

Hi Don, thanks for your reply. I tried this code, but it kept telling
me

ActiveChart.Export Filename:="C:\a\MyChart.ai", _
FilterName:="ai"

needs debug. Could you help take a look? Thank you again!

Hi -

1) Yes. This takes a moment to explain, but it's doable.

In this code snippet, we have a ChartArchiveBook that simply holds a
large number of charts in WMF/EMF format. It assumes that you have
already selected the chart.

With ActiveChart
.ChartArea.Select
.CopyPicture Appearance:=xlPrinter, Size:=xlScreen,
Format:=xlPicture
End With
'
' First name the graph so we can find it again, and put it where we
can see it...
'
GraphName = "WhateverYouWantItToBe"

' The following pastes to the proper format and to the proper place.
You can fiddle with the range...
'
With Workbooks(IndustryAnalyseBook).Sheets(AnalyseBookS heetName)
.Activate
.Range("A1").Select
.PasteSpecial Format:="Bild (Extended Metafile)",
Link:=False, _ DisplayAsIcon:=False
End With
With Selection.ShapeRange
.Parent.Name = GraphName
.Height = 500
.Width = 232
End With

The latter code simply places it in cell A1 and gives it the size of
500 by 232 pixels, you'll want to change that.

2) This took a long time to figure out, but it works great:

For Each MyShape In
Workbooks("TargetWorkbook").Sheets("TargetWorkshee t").Shapes
If MyShape.Name = GraphName Then MyShape.Delete
Next MyShape

These code snippets are part of a very large system, so they might not
be completely understandable: let me know if you need further
explanation...

John

PS: some of the code wraps funny here...you'll need to play with it to
get it working right...

Darn, forgot to neutralize this code:

With Workbooks("TargetWorkbook").Sheets("TargetWorkshee t")
.Activate
.Range("A1").Select
.PasteSpecial Format:="Bild (Extended Metafile)",
Link:=False, _ DisplayAsIcon:=False
End With
With Selection.ShapeRange
.Parent.Name = GraphName
.Height = 500
.Width = 232
End With

So, now that should be clearer...

John