Break Chart Links  
Author Message
ArtKedzierski





PostPosted: Thu Jul 07 15:28:35 CDT 2005 Top

Excel Programming >> Break Chart Links

I have a template with several embedded linked charts. After updating values
in the linked workbook, I wish to update my charts and then break all links
(so that further changes in the linked workbook are not reflected in the
charts).

I have tried, for example:
ActiveWorkbook.BreakLinks Name:=linkFileName, Type:=xlLinkTypeExcelLinks
This does not seem to have any impact on the chart links.

I have also tried converting each chart series into an array; however, each
formula requires significanly more than 256 characters to accurately generate
the chart.

I have thought about converting each chart into an image after updating, but
I am not sure how to code something like this. Any suggestions or methods of
breaking chart links would be greatly appreciated!

Excel377  
 
 
Jon





PostPosted: Thu Jul 07 15:28:35 CDT 2005 Top

Excel Programming >> Break Chart Links Delinking Chart Data:
http://peltiertech.com/Excel/ChartsHowTo/DelinkChartData.html

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




> I have a template with several embedded linked charts. After updating values
> in the linked workbook, I wish to update my charts and then break all links
> (so that further changes in the linked workbook are not reflected in the
> charts).
>
> I have tried, for example:
> ActiveWorkbook.BreakLinks Name:=linkFileName, Type:=xlLinkTypeExcelLinks
> This does not seem to have any impact on the chart links.
>
> I have also tried converting each chart series into an array; however, each
> formula requires significanly more than 256 characters to accurately generate
> the chart.
>
> I have thought about converting each chart into an image after updating, but
> I am not sure how to code something like this. Any suggestions or methods of
> breaking chart links would be greatly appreciated!
 
 
AshleighGardner





PostPosted: Fri Jul 08 07:29:02 CDT 2005 Top

Excel Programming >> Break Chart Links Jon,

Thank you for the suggestion. I believe my series arrays are being formed
properly; however, when I try to set the formula property, I get the
following error:

(ChtSeries.Formula = ...)
Run Time Error '1004': Unable to set the formula property of the series class

The string I am trying to store as a formula is 429 characters long. From
what I understand, the limit is 1024 characters. Therefore, I don't know why
I get an error.

Have you seen this behavior before?



> Delinking Chart Data:
> http://peltiertech.com/Excel/ChartsHowTo/DelinkChartData.html
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
>

>
> > I have a template with several embedded linked charts. After updating values
> > in the linked workbook, I wish to update my charts and then break all links
> > (so that further changes in the linked workbook are not reflected in the
> > charts).
> >
> > I have tried, for example:
> > ActiveWorkbook.BreakLinks Name:=linkFileName, Type:=xlLinkTypeExcelLinks
> > This does not seem to have any impact on the chart links.
> >
> > I have also tried converting each chart series into an array; however, each
> > formula requires significanly more than 256 characters to accurately generate
> > the chart.
> >
> > I have thought about converting each chart into an image after updating, but
> > I am not sure how to code something like this. Any suggestions or methods of
> > breaking chart links would be greatly appreciated!
>
 
 
Peter





PostPosted: Fri Jul 08 11:58:43 CDT 2005 Top

Excel Programming >> Break Chart Links Do your series have many values perhaps. A while ago I put together a
de-link chart routine which seems to work with series of at least 10,000
values, each with long value lengths, say 16 digits. Converts all data on
the chart to named arrays, with a separate routine to reconvert to cells in
same book and reconstruct if required. Not sure if this would overcome your
problem but you're welcome to try if interested.

Regards,
Peter T

pmbthornton at gmail com




> Jon,
>
> Thank you for the suggestion. I believe my series arrays are being formed
> properly; however, when I try to set the formula property, I get the
> following error:
>
> (ChtSeries.Formula = ...)
> Run Time Error '1004': Unable to set the formula property of the series
class
>
> The string I am trying to store as a formula is 429 characters long. From
> what I understand, the limit is 1024 characters. Therefore, I don't know
why
> I get an error.
>
> Have you seen this behavior before?
>

>
> > Delinking Chart Data:
> > http://peltiertech.com/Excel/ChartsHowTo/DelinkChartData.html
> >
> > - Jon
> > -------
> > Jon Peltier, Microsoft Excel MVP
> > Peltier Technical Services
> > Tutorials and Custom Solutions
> > http://PeltierTech.com/
> > _______
> >
> >

> >
> > > I have a template with several embedded linked charts. After updating
values
> > > in the linked workbook, I wish to update my charts and then break all
links
> > > (so that further changes in the linked workbook are not reflected in
the
> > > charts).
> > >
> > > I have tried, for example:
> > > ActiveWorkbook.BreakLinks Name:=linkFileName,
Type:=xlLinkTypeExcelLinks
> > > This does not seem to have any impact on the chart links.
> > >
> > > I have also tried converting each chart series into an array; however,
each
> > > formula requires significanly more than 256 characters to accurately
generate
> > > the chart.
> > >
> > > I have thought about converting each chart into an image after
updating, but
> > > I am not sure how to code something like this. Any suggestions or
methods of
> > > breaking chart links would be greatly appreciated!
> >


 
 
AshleighGardner





PostPosted: Fri Jul 08 12:13:13 CDT 2005 Top

Excel Programming >> Break Chart Links Peter,

The routine you have written sounds very promising. Basically I have
several charts, each with 2 data series. There are 26 data points per
series, but each value has 16 digits. After delinking these series and
converting to strings, I far exceed the 255 character limit of VBA's
seriesformula property.

Sounds like you overcame a similar problem, so I'd love to see how. Thanks!



> Do your series have many values perhaps. A while ago I put together a
> de-link chart routine which seems to work with series of at least 10,000
> values, each with long value lengths, say 16 digits. Converts all data on
> the chart to named arrays, with a separate routine to reconvert to cells in
> same book and reconstruct if required. Not sure if this would overcome your
> problem but you're welcome to try if interested.
>
> Regards,
> Peter T
>
> pmbthornton at gmail com
>
>


> > Jon,
> >
> > Thank you for the suggestion. I believe my series arrays are being formed
> > properly; however, when I try to set the formula property, I get the
> > following error:
> >
> > (ChtSeries.Formula = ...)
> > Run Time Error '1004': Unable to set the formula property of the series
> class
> >
> > The string I am trying to store as a formula is 429 characters long. From
> > what I understand, the limit is 1024 characters. Therefore, I don't know
> why
> > I get an error.
> >
> > Have you seen this behavior before?
> >

> >
> > > Delinking Chart Data:
> > > http://peltiertech.com/Excel/ChartsHowTo/DelinkChartData.html
> > >
> > > - Jon
> > > -------
> > > Jon Peltier, Microsoft Excel MVP
> > > Peltier Technical Services
> > > Tutorials and Custom Solutions
> > > http://PeltierTech.com/
> > > _______
> > >
> > >

> > >
> > > > I have a template with several embedded linked charts. After updating
> values
> > > > in the linked workbook, I wish to update my charts and then break all
> links
> > > > (so that further changes in the linked workbook are not reflected in
> the
> > > > charts).
> > > >
> > > > I have tried, for example:
> > > > ActiveWorkbook.BreakLinks Name:=linkFileName,
> Type:=xlLinkTypeExcelLinks
> > > > This does not seem to have any impact on the chart links.
> > > >
> > > > I have also tried converting each chart series into an array; however,
> each
> > > > formula requires significanly more than 256 characters to accurately
> generate
> > > > the chart.
> > > >
> > > > I have thought about converting each chart into an image after
> updating, but
> > > > I am not sure how to code something like this. Any suggestions or
> methods of
> > > > breaking chart links would be greatly appreciated!
> > >
>
>
>
 
 
Peter





PostPosted: Fri Jul 08 13:22:48 CDT 2005 Top

Excel Programming >> Break Chart Links Ashleigh,

> Sounds like you overcame a similar problem, so I'd love to see how.
Thanks!

Horizontal array 256, vertical array 64k. But there's a bit more to it.

I've just sent you what I have.

Regards,
Peter




> Peter,
>
> The routine you have written sounds very promising. Basically I have
> several charts, each with 2 data series. There are 26 data points per
> series, but each value has 16 digits. After delinking these series and
> converting to strings, I far exceed the 255 character limit of VBA's
> seriesformula property.
>
> Sounds like you overcame a similar problem, so I'd love to see how.
Thanks!
>

>
> > Do your series have many values perhaps. A while ago I put together a
> > de-link chart routine which seems to work with series of at least 10,000
> > values, each with long value lengths, say 16 digits. Converts all data
on
> > the chart to named arrays, with a separate routine to reconvert to cells
in
> > same book and reconstruct if required. Not sure if this would overcome
your
> > problem but you're welcome to try if interested.
> >
> > Regards,
> > Peter T


 
 
Peter





PostPosted: Fri Jul 08 13:52:47 CDT 2005 Top

Excel Programming >> Break Chart Links > I've just sent you what I have.


email me direct (see bottom of my first & this reply) or post your address
here.

Peter T

pmbthornton at gmail com



> Ashleigh,
>
> > Sounds like you overcame a similar problem, so I'd love to see how.
> Thanks!
>
> Horizontal array 256, vertical array 64k. But there's a bit more to it.
>
> I've just sent you what I have.
>
> Regards,
> Peter
>
>


> > Peter,
> >
> > The routine you have written sounds very promising. Basically I have
> > several charts, each with 2 data series. There are 26 data points per
> > series, but each value has 16 digits. After delinking these series and
> > converting to strings, I far exceed the 255 character limit of VBA's
> > seriesformula property.
> >
> > Sounds like you overcame a similar problem, so I'd love to see how.
> Thanks!
> >

> >
> > > Do your series have many values perhaps. A while ago I put together a
> > > de-link chart routine which seems to work with series of at least
10,000
> > > values, each with long value lengths, say 16 digits. Converts all data
> on
> > > the chart to named arrays, with a separate routine to reconvert to
cells
> in
> > > same book and reconstruct if required. Not sure if this would overcome
> your
> > > problem but you're welcome to try if interested.
> > >
> > > Regards,
> > > Peter T
>
>


 
 
Jon





PostPosted: Fri Jul 08 19:17:40 CDT 2005 Top

Excel Programming >> Break Chart Links Yeah, the series formula is limited to 1024, but that's misleading, and
any single component is limited to around 250 (it's not the hard 256
you'd expect). This means the Values and XValues each have to be less
than 250 characters.

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




> Jon,
>
> Thank you for the suggestion. I believe my series arrays are being formed
> properly; however, when I try to set the formula property, I get the
> following error:
>
> (ChtSeries.Formula = ...)
> Run Time Error '1004': Unable to set the formula property of the series class
>
> The string I am trying to store as a formula is 429 characters long. From
> what I understand, the limit is 1024 characters. Therefore, I don't know why
> I get an error.
>
> Have you seen this behavior before?
>

>
>
>>Delinking Chart Data:
>> http://peltiertech.com/Excel/ChartsHowTo/DelinkChartData.html
>>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Peltier Technical Services
>>Tutorials and Custom Solutions
>>http://PeltierTech.com/
>>_______
>>
>>

>>
>>
>>>I have a template with several embedded linked charts. After updating values
>>>in the linked workbook, I wish to update my charts and then break all links
>>>(so that further changes in the linked workbook are not reflected in the
>>>charts).
>>>
>>>I have tried, for example:
>>>ActiveWorkbook.BreakLinks Name:=linkFileName, Type:=xlLinkTypeExcelLinks
>>>This does not seem to have any impact on the chart links.
>>>
>>>I have also tried converting each chart series into an array; however, each
>>>formula requires significanly more than 256 characters to accurately generate
>>>the chart.
>>>
>>>I have thought about converting each chart into an image after updating, but
>>>I am not sure how to code something like this. Any suggestions or methods of
>>>breaking chart links would be greatly appreciated!
>>
 
 
nbowman





PostPosted: Tue Jul 12 11:10:36 CDT 2005 Top

Excel Programming >> Break Chart Links
Peter,
I would also be interested in taking a look at your routine, as I
am having the same problem. My goal is to avoid storing thousands of
points in cells by using VBA code to calculate points and putting the
data directly into a chart. The 256 character is greatly limiting me.
If you don't mind sending the routine to me, my email is below.

-Nick

nickbowman at yahoo.com


--
nbowman
------------------------------------------------------------------------
nbowman's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25152
View this thread: http://www.excelforum.com/showthread.php?threadid=385283

 
 
Jon





PostPosted: Tue Jul 12 12:08:18 CDT 2005 Top

Excel Programming >> Break Chart Links Peter -

Does it make use of the xlusrgal.xls file? I was using that technique
for a while, and now I can't save any new user defined chart types.

If it doesn't use xlusrgal.xls, I'd be appreciative if you sent me a
copy of your routine. delete the caps from the From email.

Thanks,

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





> Do your series have many values perhaps. A while ago I put together a
> de-link chart routine which seems to work with series of at least 10,000
> values, each with long value lengths, say 16 digits. Converts all data on
> the chart to named arrays, with a separate routine to reconvert to cells in
> same book and reconstruct if required. Not sure if this would overcome your
> problem but you're welcome to try if interested.
>
> Regards,
> Peter T
>
> pmbthornton at gmail com
>
>


>
>>Jon,
>>
>>Thank you for the suggestion. I believe my series arrays are being formed
>>properly; however, when I try to set the formula property, I get the
>>following error:
>>
>>(ChtSeries.Formula = ...)
>>Run Time Error '1004': Unable to set the formula property of the series
>
> class
>
>>The string I am trying to store as a formula is 429 characters long. From
>>what I understand, the limit is 1024 characters. Therefore, I don't know
>
> why
>
>>I get an error.
>>
>>Have you seen this behavior before?
>>

>>
>>
>>>Delinking Chart Data:
>>> http://peltiertech.com/Excel/ChartsHowTo/DelinkChartData.html
>>>
>>>- Jon
>>>-------
>>>Jon Peltier, Microsoft Excel MVP
>>>Peltier Technical Services
>>>Tutorials and Custom Solutions
>>>http://PeltierTech.com/
>>>_______
>>>
>>>

>>>
>>>
>>>>I have a template with several embedded linked charts. After updating
>
> values
>
>>>>in the linked workbook, I wish to update my charts and then break all
>
> links
>
>>>>(so that further changes in the linked workbook are not reflected in
>
> the
>
>>>>charts).
>>>>
>>>>I have tried, for example:
>>>>ActiveWorkbook.BreakLinks Name:=linkFileName,
>
> Type:=xlLinkTypeExcelLinks
>
>>>>This does not seem to have any impact on the chart links.
>>>>
>>>>I have also tried converting each chart series into an array; however,
>
> each
>
>>>>formula requires significanly more than 256 characters to accurately
>
> generate
>
>>>>the chart.
>>>>
>>>>I have thought about converting each chart into an image after
>
> updating, but
>
>>>>I am not sure how to code something like this. Any suggestions or
>
> methods of
>
>>>>breaking chart links would be greatly appreciated!
>>>
>
>
 
 
Peter





PostPosted: Tue Jul 12 17:16:34 CDT 2005 Top

Excel Programming >> Break Chart Links Hi Jon,

> Does it make use of the xlusrgal.xls file? I was using that technique
> for a while, and now I can't save any new user defined chart types.

No nothing like that, guess you mean store a custom chart. I hadn't thought
of that but not sure I'd want to put loads of UD charts with many values
there, in any case wouldn't be transportable.

As I mentioned to the OP, named vertical arrays, eg
vertical {1;2;3}
vs
horizontal {1,2,3}

> If it doesn't use xlusrgal.xls, I'd be appreciative if you sent me a
> copy of your routine. delete the caps from the From email.

I've just sent you my routine, also sample a chart with some "long" series
to test with.

Nick - I have also cc'd same to you.

Regards,
Peter T



>
> > Do your series have many values perhaps. A while ago I put together a
> > de-link chart routine which seems to work with series of at least 10,000
> > values, each with long value lengths, say 16 digits. Converts all data
on
> > the chart to named arrays, with a separate routine to reconvert to cells
in
> > same book and reconstruct if required. Not sure if this would overcome
your
> > problem but you're welcome to try if interested.
> >
> > Regards,
> > Peter T
> >
> > pmbthornton at gmail com
> >
> >


> >
> >>Jon,
> >>
> >>Thank you for the suggestion. I believe my series arrays are being
formed
> >>properly; however, when I try to set the formula property, I get the
> >>following error:
> >>
> >>(ChtSeries.Formula = ...)
> >>Run Time Error '1004': Unable to set the formula property of the series
> >
> > class
> >
> >>The string I am trying to store as a formula is 429 characters long.
From
> >>what I understand, the limit is 1024 characters. Therefore, I don't
know
> >
> > why
> >
> >>I get an error.
> >>
> >>Have you seen this behavior before?
> >>

> >>
> >>
> >>>Delinking Chart Data:
> >>> http://peltiertech.com/Excel/ChartsHowTo/DelinkChartData.html
> >>>
> >>>- Jon
> >>>-------
> >>>Jon Peltier, Microsoft Excel MVP
> >>>Peltier Technical Services
> >>>Tutorials and Custom Solutions
> >>>http://PeltierTech.com/
> >>>_______
> >>>
> >>>

> >>>
> >>>
> >>>>I have a template with several embedded linked charts. After updating
> >
> > values
> >
> >>>>in the linked workbook, I wish to update my charts and then break all
> >
> > links
> >
> >>>>(so that further changes in the linked workbook are not reflected in
> >
> > the
> >
> >>>>charts).
> >>>>
> >>>>I have tried, for example:
> >>>>ActiveWorkbook.BreakLinks Name:=linkFileName,
> >
> > Type:=xlLinkTypeExcelLinks
> >
> >>>>This does not seem to have any impact on the chart links.
> >>>>
> >>>>I have also tried converting each chart series into an array; however,
> >
> > each
> >
> >>>>formula requires significanly more than 256 characters to accurately
> >
> > generate
> >
> >>>>the chart.
> >>>>
> >>>>I have thought about converting each chart into an image after
> >
> > updating, but
> >
> >>>>I am not sure how to code something like this. Any suggestions or
> >
> > methods of
> >
> >>>>breaking chart links would be greatly appreciated!
> >>>
> >
> >