Converting Graph data links to values using code

[WSF]

Excel 97
I create charts using a workbook with templates and change the reports using
dynamic data ranges. One worksheet can hold several graphs. I then copy the
charts to another workbook for distribution. I extinguish the data series
links by manually converting them to values (select graph line, F2, F9,
Enter) etc., graph by graph. A painstaking job at times.

Can this be automated with code?

TIA WSF

 

[Frank Kabel]

Hi
have a look at
http://peltiertech.com/Excel/Charts/chartvba.html#DelinkCht

 

[Tushar Mehta]

One way to convert all embedded charts on a sheet:

Sub Macro2()
Dim aSeries As Series, aChartObj As ChartObject
For Each aChartObj In ActiveSheet.ChartObjects
For Each aSeries In aChartObj.Chart.SeriesCollection
With aSeries
.Values = .Values
.XValues = .XValues
End With
Next aSeries
Next aChartOba
End Sub

Do note that the code above converts only the series. If there are
other objects (datalabels, titles, textboxes, etc.) with links to
cells, they are not converted.

An alternative you might want to consider is to copy an image of the
chart with SHIFT + Edit | Copy Picture...

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Jon Peltier]

An alternative page on my site shows how to copy pictures of the charts
into PowerPoint. Might make for a nicer presentation vehicle than Excel.

http://peltiertech.com/Excel/XL_PPT.html

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