de-link *dates* in chart from data

[Edward Scott]

I have excel vba code that creates a chart from a table of data, then
de-links the chart from the data and removes the data, as described
here: http://peltiertech.com/Excel/ChartsH...ChartData.html
(scroll down to the VBA bit).


The problem with this is that the x-labels on my chart are dates, and
when I de-link them the x-labels become apparently meaningless numbers.
I have almost torn my hair out trying to fix this, but I have not found
a solution.


I know this has been discussed before, here:
http://groups.google.com/group/micro...40c5d45d534e92
and here:
http://groups.google.com/group/micro...53113c57ea33aa

but no solution appears to have been mentioned in the past. I figured I
would check again to see if any ideas were out there.

[Vacation's Over]

Jon's answer is still valid

Post more specificsas to what you do with the chart after delinking and you
may get a suggestion for workaround. Which wil generally call for putting
the dates into a sheet somewhere not visible.

PS: in addition to dates the delinking array can kill big numbers or big
number sets as well for the same reason.


"Edward Scott" wrote:

> I have excel vba code that creates a chart from a table of data, then
> de-links the chart from the data and removes the data, as described
> here: http://peltiertech.com/Excel/ChartsH...ChartData.html
> (scroll down to the VBA bit).
>
>
> The problem with this is that the x-labels on my chart are dates, and
> when I de-link them the x-labels become apparently meaningless numbers.
> I have almost torn my hair out trying to fix this, but I have not found
> a solution.
>
>
> I know this has been discussed before, here:
> http://groups.google.com/group/micro...40c5d45d534e92
> and here:
> http://groups.google.com/group/micro...53113c57ea33aa
>
> but no solution appears to have been mentioned in the past. I figured I
> would check again to see if any ideas were out there.
>
>

[Peter T]

I have a delink chart addin that converts series X & Y values to arrays and
other links to text. Or as a separate method, copies data linked to another
workbook into same workbook.

Here's a snippet to handle axis formats such as dates

In the main routine

Dim ch as Chart
Dim vaAxis

set ch = whatever chart

before delinking
GetAxisFormats ch, vaAxis

code to delink

after delinking
ApplyAxisFormats ch vaAxis

Function GetAxisFormats(ch As Chart, va)
Dim ax As Axis
Dim i As Long
On Error Resume Next
ReDim va(0 To ch.Axes.Count, 0 To 2)
For Each ax In ch.Axes
With ax.TickLabels
va(i, 0) = .AutoScaleFont
va(i, 1) = .NumberFormat
va(i, 2) = .Orientation ' doesn't return "degree" in Xaxis
.NumberFormatLinked = False ' ?
End With
i = i + 1
Next

End Function

Function ApplyAxisFormats(ch As Chart, va)
Dim ax As Axis
Dim i As Long

For Each ax In ch.Axes
With ax.TickLabels
If Len(va(i, 0)) Then .AutoScaleFont = va(i, 0)
If Len(va(i, 1)) Then .NumberFormat = va(i, 1)
If Len(va(i, 2)) Then .Orientation = va(i, 2)
End With
i = i + 1
Next
End Function

Doing similar for labels is somewhat more complicated as only a string is
returned. I have something that seems to work for dates in labels but not
got around to fully testing.

FWIW the addin is geared to delink large qty's of values (limited to 5000 X
or Y values per series in XL97 & XL2K with array method). If interested I
can send you a copy.

Regards,
Peter T
pmbthornton gmail com



"Edward Scott" <shakermaker@attglobal.net> wrote in message
news:1128716348.355955.153720@g14g2000cwa.googlegroups.com...
> I have excel vba code that creates a chart from a table of data, then
> de-links the chart from the data and removes the data, as described
> here: http://peltiertech.com/Excel/ChartsH...ChartData.html
> (scroll down to the VBA bit).
>
>
> The problem with this is that the x-labels on my chart are dates, and
> when I de-link them the x-labels become apparently meaningless numbers.
> I have almost torn my hair out trying to fix this, but I have not found
> a solution.
>
>
> I know this has been discussed before, here:
>
http://groups.google.com/group/micro...ing/browse_thr
ead/thread/3440c5d45d534e92
> and here:
>
http://groups.google.com/group/micro...ing/browse_thr
ead/thread/5853113c57ea33aa
>
> but no solution appears to have been mentioned in the past. I figured I
> would check again to see if any ideas were out there.
>

[Peter T]

For the archives, the OP replied to me off line -

"
I tried out the code you posted on microsoft.public.excel.programming, and
it did the trick! interestingly enough I don't seem to need to use
ApplyAxisFormats. using GetAxisFormats before delinking alone seems to do
the trick and leave my dates in their normal format after delinking. I'm not
sure why that is.
"

and I replied back -

"
In that case all you need do is to loop each axis, or simply the axis you
are concerned with, and change this property -

.NumberFormatLinked = False

You probably don't need a separate function to do only this.

The code I posted was a partial snippet to cater for unknown potential
formats, also for delinking in different ways.
"

Regards,
Peter T

"Peter T" <peter_t@discussions> wrote in message
news:OhlvGJ6yFHA.2312@TK2MSFTNGP14.phx.gbl...
> I have a delink chart addin that converts series X & Y values to arrays
and
> other links to text. Or as a separate method, copies data linked to
another
> workbook into same workbook.
>
> Here's a snippet to handle axis formats such as dates
>
> In the main routine
>
> Dim ch as Chart
> Dim vaAxis
>
> set ch = whatever chart
>
> before delinking
> GetAxisFormats ch, vaAxis
>
> code to delink
>
> after delinking
> ApplyAxisFormats ch vaAxis
>
> Function GetAxisFormats(ch As Chart, va)
> Dim ax As Axis
> Dim i As Long
> On Error Resume Next
> ReDim va(0 To ch.Axes.Count, 0 To 2)
> For Each ax In ch.Axes
> With ax.TickLabels
> va(i, 0) = .AutoScaleFont
> va(i, 1) = .NumberFormat
> va(i, 2) = .Orientation ' doesn't return "degree" in Xaxis
> .NumberFormatLinked = False ' ?
> End With
> i = i + 1
> Next
>
> End Function
>
> Function ApplyAxisFormats(ch As Chart, va)
> Dim ax As Axis
> Dim i As Long
>
> For Each ax In ch.Axes
> With ax.TickLabels
> If Len(va(i, 0)) Then .AutoScaleFont = va(i, 0)
> If Len(va(i, 1)) Then .NumberFormat = va(i, 1)
> If Len(va(i, 2)) Then .Orientation = va(i, 2)
> End With
> i = i + 1
> Next
> End Function
>
> Doing similar for labels is somewhat more complicated as only a string is
> returned. I have something that seems to work for dates in labels but not
> got around to fully testing.
>
> FWIW the addin is geared to delink large qty's of values (limited to 5000
X
> or Y values per series in XL97 & XL2K with array method). If interested I
> can send you a copy.
>
> Regards,
> Peter T
> pmbthornton gmail com
>
>
>
> "Edward Scott" <shakermaker@attglobal.net> wrote in message
> news:1128716348.355955.153720@g14g2000cwa.googlegroups.com...
> > I have excel vba code that creates a chart from a table of data, then
> > de-links the chart from the data and removes the data, as described
> > here: http://peltiertech.com/Excel/ChartsH...ChartData.html
> > (scroll down to the VBA bit).
> >
> >
> > The problem with this is that the x-labels on my chart are dates, and
> > when I de-link them the x-labels become apparently meaningless numbers.
> > I have almost torn my hair out trying to fix this, but I have not found
> > a solution.
> >
> >
> > I know this has been discussed before, here:
> >
>
http://groups.google.com/group/micro...ing/browse_thr
> ead/thread/3440c5d45d534e92
> > and here:
> >
>
http://groups.google.com/group/micro...ing/browse_thr
> ead/thread/5853113c57ea33aa
> >
> > but no solution appears to have been mentioned in the past. I figured I
> > would check again to see if any ideas were out there.
> >
>
>