Remove remote references in VBA without "BreakLink"

  • Thread starter Thread starter tkstock
  • Start date Start date
T

tkstock

I have an Excel template with charts that spawns multiple copies o
itself by copying specific worksheets to a new workbook, then savin
the new workbook. The only problem is, the charts that are copied ove
retain their references to the original workbook. Isn't there
workbook setting I can set in VBA that would prevent Excel from keepin
those references?

Setting the "UpdateRemoteReferences" property doesn't help. Setting th
"SaveLinkValues" property doesn't help. When are the references actuall
created? If I delete the link after the workbook is created wit
BreakLink, it says that the chart series "value" string is too long, s
I can't just delete the link either.

Another problem I'm having is that when a series on a particular char
refers to a cell that has an empty string from a formula (=if(conditon
"", result)), when the formula returns the empty string, it plots it o
the chart. I've got the Options - Chart property "Plot empty cell
as..." set to "Not Plotted (leave gaps)" but this doens't work.
don't want the cells with an empty string to be plotted. Any idea
there?

Any help would be appreciated!!

Thanks
 
If an embedded chart references only cells in its parent worksheet and
if you copy the worksheet to another workbook, the chart in the new
workbook will refer to the worksheet in the new workbook.

Replace the "" with NA().

BTW, what is BreakLink?


--
Regards,

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