Change Chart links to values using VBA?

[WSF]

Excel 97
I have a worksheet with a collection of productivity charts based on
data in other worksheets of the same workbook.

I need to copy / email the worksheet with the charts only to other staff
- all they need is the static charts.

So I patiently covert the links in the charts to values by selecting the
chart line, going to the formula bar, press F9 then Enter to do so.

Surely there is a better way?

Can I do this laborious job in VBA?

Any ideas gratefully appreciated.

WSF

 

[Andy Pope]

Hi,

This routine will convert links to array values.

Sub ChartValues()
Dim intSeries As Integer

With ActiveChart
For intSeries = 1 To .SeriesCollection.Count
With .SeriesCollection(intSeries)
.Values = .Values
.XValues = .XValues
.Name = .Name
End With
Next
End With
End Sub

Cheers
Andy

 

[WSF]

Thanks for that Andy.
It doesn't seem to want to work on some charts but.
My problem is that I have up to 6 small charts per worksheet, rather
than in a chart Sheet.

I do recall a post a long while back that offered code to go through any
number of charts on the active worksheet and change all the chart links
to array values.

WSF

 

[Andy Pope]

Hi,

It is possible the routine will fail if the chart contains empty cells.

Here is an extension of the code to process all charts on a worksheet.
Note I have note tested the amendments.

Sub ChartValues()
Dim intSeries As Integer
Dim objTemp as chartobject

For Each objTemp in Activesheet.chartobjects
With objtemp.Chart
For intSeries = 1 To .SeriesCollection.Count
With .SeriesCollection(intSeries)
.Values = .Values
.XValues = .XValues
.Name = .Name
End With
Next
End With
Next
End Sub

Cheers
Andy

 

[Jon Peltier]

In general Andy's approach is fine, but I have addressed some of the problems that
may arise on this web page:

http://peltiertech.com/Excel/ChartsHowTo/DelinkChartData.html

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