MS Delete Links Addin (Wizard)

  • Thread starter Thread starter SEDK
  • Start date Start date
S

SEDK

I need to delink charts from their source data before
posting the workbook on a network. Each worksheet has 8
charts. There are about 12 worksheets. There are bar
charts and line charts. The line charts have up to two
series of data each. The Delete Links Addin does remove
the links from the bar charts and from the embedded (cell)
formulas. But, although it 'sees' the links in the line
charts (they appear in the list as the wizard seeks
links), they are not eliminated. These links remain after
processing. Any Help or Ideas?

Thanks!!! SEDK
 
A macro like this ought to handle it, providing each series doesn't have
too many points:

Sub KillLinks
Dim ws As Worksheet
Dim ChtOb As ChartObject
Dim srs As Series
For Each ws In ActiveWorkbook.Worksheets
For Each ChtOb In ws.ChartObjects
For Each srs in ChtOb.Chart.SeriesCollection
.Values = .Values
.XValues = .XValues
.Name = .Name
Next
Next
Next
End Sub

If your series have too many points (i.e., they need too many characters
to describe in the series formula), you can try the hints on this web page:

http://www.geocities.com/jonpeltier/Excel/ChartsHowTo/DelinkChartData.html

- Jon
 
Thank you for the help. I'm having trouble getting the
macro to run. VBA doesn't like the .(dot)Values, etc.
without a With statement. So I tried With srs/End With
before and after the .Value and .Name statements
respectively, but the values still couldn't be assigned.
I get "unable to set the Values property if the Series
class". It seems like the connection between replacing
the series range with the values in that range isn't being
made. I've tried various combinations of quotes and
parens on the right sides of the formulas, but no luck.
Any ideas?
SEDK
 
Hi Jon,

I picked up what I needed to solve my first problem from
your site after sending my first reply. The macro is
working except when values are percents. Would you expect
a problem with percents?

Thank you for the help! SEDK
 
See, this is why I have to test it before I post it! Here's the way it
should be:

Sub KillLinks
Dim ws As Worksheet
Dim ChtOb As ChartObject
Dim srs As Series
For Each ws In ActiveWorkbook.Worksheets
For Each ChtOb In ws.ChartObjects
For Each srs in ChtOb.Chart.SeriesCollection
With srs
.Values = .Values
.XValues = .XValues
.Name = .Name
End With
Next
Next
Next
End Sub

I think I need a With Each...Next construction.

- Jon
 
Here's one reason why. I just tested my corrected procedure with some
percentages, and what looked like 22.4% in the worksheet looked like
0.223606797749979 in the series formula. 17 characters to convey the
meaning of 5. As I mentioned, the series formula has a limit in how
many characters can be used to represent XValues and Values, so you
might want to use my procedure on the Delink page I cited earlier to
truncate some of these excess digits.

I also noticed that the axis format changed from 0.0% to General (0.0 or
0.00). So I've inserted a link to unlink the number format from the values:

Sub KillLinks()
Dim ws As Worksheet
Dim ChtOb As ChartObject
Dim srs As Series
For Each ws In ActiveWorkbook.Worksheets
For Each ChtOb In ws.ChartObjects
'' Unlink number format from values
ChtOb.Chart.Axes(xlValue).TickLabels.NumberFormatLinked = False
For Each srs In ChtOb.Chart.SeriesCollection
With srs
.Values = .Values
.XValues = .XValues
.Name = .Name
End With
Next
Next
Next
End Sub

- Jon
 
Hi Jon,

I got swamped here and am just now getting back to this.
I solved the problem in the interim by writing a macro
that changed the charts to pictures. I will now go for
making this fix.

I wanted to thank you so much for the help. I really
appreciate it!

Sue
-----Original Message-----
Here's one reason why. I just tested my corrected procedure with some
percentages, and what looked like 22.4% in the worksheet looked like
0.223606797749979 in the series formula. 17 characters to convey the
meaning of 5. As I mentioned, the series formula has a limit in how
many characters can be used to represent XValues and Values, so you
might want to use my procedure on the Delink page I cited earlier to
truncate some of these excess digits.

I also noticed that the axis format changed from 0.0% to General (0.0 or
0.00). So I've inserted a link to unlink the number format from the values:

Sub KillLinks()
Dim ws As Worksheet
Dim ChtOb As ChartObject
Dim srs As Series
For Each ws In ActiveWorkbook.Worksheets
For Each ChtOb In ws.ChartObjects
'' Unlink number format from values
ChtOb.Chart.Axes
Click to expand...
(xlValue).TickLabels.NumberFormatLinked = False
 
Back
Top