Breaking Chart Links Loses Formatting in Chart Data Table

[weaverjohn]

I create workbooks that have a significant amount of private data that is used to generate charts and reports. We want to distribute a workbook with just the charts and reports without any links to the data. No big deal - use vba - create a new workbook, copy over the charts to the new workbook and run

For Each x In ActiveChart.SeriesCollection
   x.Values = x.Values
   x.XValues = x.XValues
   x.Name = x.Name
Next x

This breaks the links - just what we wanted codes the series an array (eg. {21,15,26,32})
Problem is there is a data table in most of the charts and we lose the formatting. So the original formatting is $ in k "$#,##," which is lost.

Anyone have a though as to how to format the series again or the data in the data table?

Thanks,

John Weaver

 

[pgc01]

Hi John

I don't think that what you want is possible. The DataTable in the chart has very little flexibility.

Whay you can do instead, is simply to copy the chart as picture. This way you get an image of the chart, including the data table formatted as you want, that you can distribute.

 

[weaverjohn]

Hi John

I don't think that what you want is possible. The DataTable in the chart has very little flexibility.

Whay you can do instead, is simply to copy the chart as picture. This way you get an image of the chart, including the data table formatted as you want, that you can distribute.

pgc01:

Thanks for the response. I thought I could manipulate it, but in the end think you are correct. So what I'm doing now is working on a routine that gets the series from each chart and copies it to a new book, copies the chart to a new book, and the replaces the values in the chart with the local ones. Then I can depreciate it all the way back to excel 2003 with no macros and no picot tables - just the way the boss wants it. Although I will have to go look to see if I can just copy out the charts as jpgs.


Thanks,

John