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!

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