de-link *dates* in chart from data
 

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
he 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, he
http://groups.google.com/group/micro...40c5d45d534e92
and he
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.

de-link *dates* in chart from data
 

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
he 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, he
http://groups.google.com/group/micro...40c5d45d534e92
and he
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.

de-link *dates* in chart from data
 

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" wrote in message
oups.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
he 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, he

http://groups.google.com/group/micro...ing/browse_thr
ead/thread/3440c5d45d534e92
and he

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.

de-link *dates* in chart from data
 

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
...
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" wrote in message
oups.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
he 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, he


http://groups.google.com/group/micro...ing/browse_thr
ead/thread/3440c5d45d534e92
and he


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.