We have a fairly sophisticated Excel AddIn that generates all sorts of Chart
types. It works fine in English versions of Excel, but not foreign langauage
versions.

We've narrowed down the problem to when we're assigning the TypeName when we
create a chart. Here's an example of three types that don't work:
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line - Column"
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= "Line - Column
on 2 Axes"
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Lines on 2 Axes"

The issue seems to be that the TypeName is localized to the native language.
Does Excel have IDs or constants we can use? I've seen the list of
constants for the standart Chart types. But there doesn't seem to be
constants for the custom types.

Thanks in advance for your help.

I'm not sure I was clear on the problem. The problem is that in the foreign
language version of Excel, there is no Custom chart type that corresponds to
the English TypeName. For example, in German the TypeName is not "Line -
Column". It is whatever the German equivalent is. So it fails with an error
saying that "Line - Column" is not a valid type. If I use the German
equivalent of "Line - Column" it works fine.

Does that make more sense?

Thanks again for the help.

"theLuggage" wrote:

We have a fairly sophisticated Excel AddIn that generates all sorts of Chart
types. It works fine in English versions of Excel, but not foreign langauage
versions.

We've narrowed down the problem to when we're assigning the TypeName when we
create a chart. Here's an example of three types that don't work:
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line - Column"
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= "Line - Column
on 2 Axes"
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Lines on 2 Axes"

The issue seems to be that the TypeName is localized to the native language.
Does Excel have IDs or constants we can use? I've seen the list of
constants for the standart Chart types. But there doesn't seem to be
constants for the custom types.

Thanks in advance for your help.

I can't find any ID's or named constants for the BuiltIn custom charts, or
something like a 'LocalName'. Even if there is such an alternative I don't
see how it could be used, but maybe some else knows.

Is it definitely the case the code fails because of non-English names, and
not because the type cannot be applied due to an inappropriate number of
series?

Assuming it is indeed a language problem, the obvious solution would be to
record a macro and get the German name of the custom chart type. But I guess
you need the code to work in all languages. If you care to post the German
names for one or two types I have an idea for a kludgy workaround.

Regards,
Peter T

"theLuggage" wrote in message
...
I'm not sure I was clear on the problem. The problem is that in the
foreign
language version of Excel, there is no Custom chart type that corresponds
to
the English TypeName. For example, in German the TypeName is not "Line -
Column". It is whatever the German equivalent is. So it fails with an
error
saying that "Line - Column" is not a valid type. If I use the German
equivalent of "Line - Column" it works fine.

Does that make more sense?

Thanks again for the help.

"theLuggage" wrote:

We have a fairly sophisticated Excel AddIn that generates all sorts of
Chart
types. It works fine in English versions of Excel, but not foreign
langauage
versions.

We've narrowed down the problem to when we're assigning the TypeName
when we
create a chart. Here's an example of three types that don't work:
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line -
Column"
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= "Line -
Column
on 2 Axes"
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Lines on 2
Axes"

The issue seems to be that the TypeName is localized to the native
language.
Does Excel have IDs or constants we can use? I've seen the list of
constants for the standart Chart types. But there doesn't seem to be
constants for the custom types.

Thanks in advance for your help.

Another reason to avoid the built-in custom chart types. It's a bit more
involved to set up, but it's in code so it will work repeatably once it's
done. Have your code create a line chart with all the data, then change the
appropriate series to columns, and put the appropriate series onto the
secondary axis.

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


"theLuggage" wrote in message
...
I'm not sure I was clear on the problem. The problem is that in the
foreign
language version of Excel, there is no Custom chart type that corresponds
to
the English TypeName. For example, in German the TypeName is not "Line -
Column". It is whatever the German equivalent is. So it fails with an
error
saying that "Line - Column" is not a valid type. If I use the German
equivalent of "Line - Column" it works fine.

Does that make more sense?

Thanks again for the help.

"theLuggage" wrote:

We have a fairly sophisticated Excel AddIn that generates all sorts of
Chart
types. It works fine in English versions of Excel, but not foreign
langauage
versions.

We've narrowed down the problem to when we're assigning the TypeName when
we
create a chart. Here's an example of three types that don't work:
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line -
Column"
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= "Line -
Column
on 2 Axes"
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Lines on 2
Axes"

The issue seems to be that the TypeName is localized to the native
language.
Does Excel have IDs or constants we can use? I've seen the list of
constants for the standart Chart types. But there doesn't seem to be
constants for the custom types.

Thanks in advance for your help.

Thanks for the help, Peter.

The German equivalent for "Line - Column" is "Linie - Säule".
The German equivalent for "Line - Column on 2 Axes" is "Linie - Säule auf
zwei Achsen".



"Peter T" wrote:

I can't find any ID's or named constants for the BuiltIn custom charts, or
something like a 'LocalName'. Even if there is such an alternative I don't
see how it could be used, but maybe some else knows.

Is it definitely the case the code fails because of non-English names, and
not because the type cannot be applied due to an inappropriate number of
series?

Assuming it is indeed a language problem, the obvious solution would be to
record a macro and get the German name of the custom chart type. But I guess
you need the code to work in all languages. If you care to post the German
names for one or two types I have an idea for a kludgy workaround.

Regards,
Peter T

"theLuggage" wrote in message
...
I'm not sure I was clear on the problem. The problem is that in the
foreign
language version of Excel, there is no Custom chart type that corresponds
to
the English TypeName. For example, in German the TypeName is not "Line -
Column". It is whatever the German equivalent is. So it fails with an
error
saying that "Line - Column" is not a valid type. If I use the German
equivalent of "Line - Column" it works fine.

Does that make more sense?

Thanks again for the help.

"theLuggage" wrote:

We have a fairly sophisticated Excel AddIn that generates all sorts of
Chart
types. It works fine in English versions of Excel, but not foreign
langauage
versions.

We've narrowed down the problem to when we're assigning the TypeName
when we
create a chart. Here's an example of three types that don't work:
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line -
Column"
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= "Line -
Column
on 2 Axes"
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Lines on 2
Axes"

The issue seems to be that the TypeName is localized to the native
language.
Does Excel have IDs or constants we can use? I've seen the list of
constants for the standart Chart types. But there doesn't seem to be
constants for the custom types.

Thanks in advance for your help.

Thanks, Jon. I appreciate your wisdom. I'll give your idea a try.

"Jon Peltier" wrote:

Another reason to avoid the built-in custom chart types. It's a bit more
involved to set up, but it's in code so it will work repeatably once it's
done. Have your code create a line chart with all the data, then change the
appropriate series to columns, and put the appropriate series onto the
secondary axis.

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


"theLuggage" wrote in message
...
I'm not sure I was clear on the problem. The problem is that in the
foreign
language version of Excel, there is no Custom chart type that corresponds
to
the English TypeName. For example, in German the TypeName is not "Line -
Column". It is whatever the German equivalent is. So it fails with an
error
saying that "Line - Column" is not a valid type. If I use the German
equivalent of "Line - Column" it works fine.

Does that make more sense?

Thanks again for the help.

"theLuggage" wrote:

We have a fairly sophisticated Excel AddIn that generates all sorts of
Chart
types. It works fine in English versions of Excel, but not foreign
langauage
versions.

We've narrowed down the problem to when we're assigning the TypeName when
we
create a chart. Here's an example of three types that don't work:
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line -
Column"
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= "Line -
Column
on 2 Axes"
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Lines on 2
Axes"

The issue seems to be that the TypeName is localized to the native
language.
Does Excel have IDs or constants we can use? I've seen the list of
constants for the standart Chart types. But there doesn't seem to be
constants for the custom types.

Thanks in advance for your help.

I forgot to answer one of your questions.

Yes, it works fine when I change the English TypeName to the German
TypeName. So, I'm sure it's not an issue with a bad number or series.

And you are right. Our tool is used globally and it needs to work in all
languages. I was hoping to find a solution that wouldn't require me to
figure out what all the localized terms are for all the chart types.


"Peter T" wrote:

I can't find any ID's or named constants for the BuiltIn custom charts, or
something like a 'LocalName'. Even if there is such an alternative I don't
see how it could be used, but maybe some else knows.

Is it definitely the case the code fails because of non-English names, and
not because the type cannot be applied due to an inappropriate number of
series?

Assuming it is indeed a language problem, the obvious solution would be to
record a macro and get the German name of the custom chart type. But I guess
you need the code to work in all languages. If you care to post the German
names for one or two types I have an idea for a kludgy workaround.

Regards,
Peter T

"theLuggage" wrote in message
...
I'm not sure I was clear on the problem. The problem is that in the
foreign
language version of Excel, there is no Custom chart type that corresponds
to
the English TypeName. For example, in German the TypeName is not "Line -
Column". It is whatever the German equivalent is. So it fails with an
error
saying that "Line - Column" is not a valid type. If I use the German
equivalent of "Line - Column" it works fine.

Does that make more sense?

Thanks again for the help.

"theLuggage" wrote:

We have a fairly sophisticated Excel AddIn that generates all sorts of
Chart
types. It works fine in English versions of Excel, but not foreign
langauage
versions.

We've narrowed down the problem to when we're assigning the TypeName
when we
create a chart. Here's an example of three types that don't work:
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line -
Column"
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= "Line -
Column
on 2 Axes"
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Lines on 2
Axes"

The issue seems to be that the TypeName is localized to the native
language.
Does Excel have IDs or constants we can use? I've seen the list of
constants for the standart Chart types. But there doesn't seem to be
constants for the custom types.

Thanks in advance for your help.

Actually Jon gave good advice, as he implied there are other reasons trying
to apply chart types from the gallery might fail. A "fairly sophisticated
Excel AddIn that generates all sorts of Charts" ought generate a custom
chart to needs.

Anyway and FWIW, this is what I had in mind as a workaround. It relies on
two big assumptions -

1. When first attempting to apply a built in custom chart type XL8galry.xls
opens and can be accessed (invisible but can see it in the VBE). It always
does for me but can't be sure if it does for all.

2. Apart from the Chart names, XL8galry.xls is identical in all XL versions
and languages, in particular the charts are in same order in different
language versions. I can't test this at all.

Sub Test()
Dim sTypeName As String
Dim ch As Chart

On Error GoTo errH

sTypeName = "Linie - Säule" ' "Line - Column"
'sTypeName = "Linie - Säule auf zwei Achsen" '"Line - Column on 2 Axes"

' more tests

Set ch = ActiveChart
If ch Is Nothing Then
MsgBox "no chart is selected"
Exit Sub
End If

On Error GoTo errBuiltIn:
ch.ApplyCustomType ChartType:=xlBuiltIn, TypeName:=sTypeName

' regular error handler
On Error GoTo errH
' more code

Exit Sub

errBuiltIn:
If TranslateBuiltInName(sTypeName) Then
Resume
'Else
' Resume Next ' or Resume elsewhere or handle somehow
End If
Exit Sub

errH:

MsgBox Err.Description, , Err.Number

End Sub


Function TranslateBuiltInName(ByRef sName) As Boolean
Dim i As Long
Dim sLocalName As String
Dim sErrMsg As String
Dim vArr
Dim ch As Chart
Dim wb As Workbook
Static col As Collection

On Error GoTo errH
If col Is Nothing Then

' the order of charts in XL8galry.xls in English XL2000
vArr = Array("dummy", _
"Outdoor Bars", "Logarithmic", "Column - Area", _
"Lines on 2 Axes", "Line - Column on 2 Axes", _
"Line - Column", "Smooth Lines", "Cones", _
"Area Blocks", "Tubes", "Pie Explosion", _
"Stack of Colors", "Columns with Depth", "Blue Pie", _
"Floating Bars", "Colored Lines", "B&W Column", _
"B&W Line - Timescale", "B&W Area", "B&W Pie")

Set col = New Collection
100 Set wb = Workbooks("XL8GALRY.XLS")
If wb Is Nothing Then Err.Raise 12345
101
For Each ch In wb.Charts
i = i + 1
col.Add ch.Name, vArr(i)
Next
End If

200 sLocalName = col(sName)
201

sName = sLocalName
TranslateBuiltInName = True

Exit Function

errH:
Select Case Erl
Case 100: sErrMsg = "Cannot access XL8galry.xls"
Case 200: sErrMsg = sName & vbCr & "does not exist in XL8galry.xls"
Case Else: sErrMsg = "unexpected error" & vbCr & Err.Description
End Select

MsgBox sErrMsg

End Function

If this works (big if), debug the collection of chart names and verify they
appear in same order as those in 'vArr'. Perhaps you could let us know how
the German version compares.

Regards,
Peter T


"theLuggage" wrote in message
...
Thanks for the help, Peter.

The German equivalent for "Line - Column" is "Linie - Säule".
The German equivalent for "Line - Column on 2 Axes" is "Linie - Säule auf
zwei Achsen".



"Peter T" wrote:

I can't find any ID's or named constants for the BuiltIn custom charts,
or
something like a 'LocalName'. Even if there is such an alternative I
don't
see how it could be used, but maybe some else knows.

Is it definitely the case the code fails because of non-English names,
and
not because the type cannot be applied due to an inappropriate number of
series?

Assuming it is indeed a language problem, the obvious solution would be
to
record a macro and get the German name of the custom chart type. But I
guess
you need the code to work in all languages. If you care to post the
German
names for one or two types I have an idea for a kludgy workaround.

Regards,
Peter T

"theLuggage" wrote in message
...
I'm not sure I was clear on the problem. The problem is that in the
foreign
language version of Excel, there is no Custom chart type that
corresponds
to
the English TypeName. For example, in German the TypeName is not
"Line -
Column". It is whatever the German equivalent is. So it fails with
an
error
saying that "Line - Column" is not a valid type. If I use the German
equivalent of "Line - Column" it works fine.

Does that make more sense?

Thanks again for the help.

"theLuggage" wrote:

We have a fairly sophisticated Excel AddIn that generates all sorts
of
Chart
types. It works fine in English versions of Excel, but not foreign
langauage
versions.

We've narrowed down the problem to when we're assigning the TypeName
when we
create a chart. Here's an example of three types that don't work:
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line -
Column"
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= "Line -
Column
on 2 Axes"
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Lines
on 2
Axes"

The issue seems to be that the TypeName is localized to the native
language.
Does Excel have IDs or constants we can use? I've seen the list of
constants for the standart Chart types. But there doesn't seem to
be
constants for the custom types.

Thanks in advance for your help.

Oops, mistakes in both routines

In Test() apply sTypeName in English, so change -

sTypeName = "Linie - Säule" ' "Line - Column"
'sTypeName = "Linie - Säule auf zwei Achsen" '"Line - Column on 2
Axes"

to
sTypeName = "Line - Column"
'sTypeName = "Line - Column on 2 Axes"

hopefully sTypeName will return in German

In TranslateBuiltInName() somehow in posting the line to create a New
Collection got lost -

If col Is Nothing Then
Set col = New Collection ' add this line

Regards,
Peter T

"Peter T" <peter_t@discussions wrote in message
...
Actually Jon gave good advice, as he implied there are other reasons
trying
to apply chart types from the gallery might fail. A "fairly sophisticated
Excel AddIn that generates all sorts of Charts" ought generate a custom
chart to needs.

Anyway and FWIW, this is what I had in mind as a workaround. It relies on
two big assumptions -

1. When first attempting to apply a built in custom chart type
XL8galry.xls
opens and can be accessed (invisible but can see it in the VBE). It always
does for me but can't be sure if it does for all.

2. Apart from the Chart names, XL8galry.xls is identical in all XL
versions
and languages, in particular the charts are in same order in different
language versions. I can't test this at all.

Sub Test()
Dim sTypeName As String
Dim ch As Chart

On Error GoTo errH

sTypeName = "Linie - Säule" ' "Line - Column"
'sTypeName = "Linie - Säule auf zwei Achsen" '"Line - Column on 2
Axes"

' more tests

Set ch = ActiveChart
If ch Is Nothing Then
MsgBox "no chart is selected"
Exit Sub
End If

On Error GoTo errBuiltIn:
ch.ApplyCustomType ChartType:=xlBuiltIn, TypeName:=sTypeName

' regular error handler
On Error GoTo errH
' more code

Exit Sub

errBuiltIn:
If TranslateBuiltInName(sTypeName) Then
Resume
'Else
' Resume Next ' or Resume elsewhere or handle somehow
End If
Exit Sub

errH:

MsgBox Err.Description, , Err.Number

End Sub


Function TranslateBuiltInName(ByRef sName) As Boolean
Dim i As Long
Dim sLocalName As String
Dim sErrMsg As String
Dim vArr
Dim ch As Chart
Dim wb As Workbook
Static col As Collection

On Error GoTo errH
If col Is Nothing Then

' the order of charts in XL8galry.xls in English XL2000
vArr = Array("dummy", _
"Outdoor Bars", "Logarithmic", "Column - Area", _
"Lines on 2 Axes", "Line - Column on 2 Axes", _
"Line - Column", "Smooth Lines", "Cones", _
"Area Blocks", "Tubes", "Pie Explosion", _
"Stack of Colors", "Columns with Depth", "Blue Pie",
_
"Floating Bars", "Colored Lines", "B&W Column", _
"B&W Line - Timescale", "B&W Area", "B&W Pie")

Set col = New Collection
100 Set wb = Workbooks("XL8GALRY.XLS")
If wb Is Nothing Then Err.Raise 12345
101
For Each ch In wb.Charts
i = i + 1
col.Add ch.Name, vArr(i)
Next
End If

200 sLocalName = col(sName)
201

sName = sLocalName
TranslateBuiltInName = True

Exit Function

errH:
Select Case Erl
Case 100: sErrMsg = "Cannot access XL8galry.xls"
Case 200: sErrMsg = sName & vbCr & "does not exist in
XL8galry.xls"
Case Else: sErrMsg = "unexpected error" & vbCr & Err.Description
End Select

MsgBox sErrMsg

End Function

If this works (big if), debug the collection of chart names and verify
they
appear in same order as those in 'vArr'. Perhaps you could let us know how
the German version compares.

Regards,
Peter T


"theLuggage" wrote in message
...
Thanks for the help, Peter.

The German equivalent for "Line - Column" is "Linie - Säule".
The German equivalent for "Line - Column on 2 Axes" is "Linie - Säule
auf
zwei Achsen".



"Peter T" wrote:

I can't find any ID's or named constants for the BuiltIn custom
charts,
or
something like a 'LocalName'. Even if there is such an alternative I
don't
see how it could be used, but maybe some else knows.

Is it definitely the case the code fails because of non-English names,
and
not because the type cannot be applied due to an inappropriate number
of
series?

Assuming it is indeed a language problem, the obvious solution would
be
to
record a macro and get the German name of the custom chart type. But I
guess
you need the code to work in all languages. If you care to post the
German
names for one or two types I have an idea for a kludgy workaround.

Regards,
Peter T

"theLuggage" wrote in message
...
I'm not sure I was clear on the problem. The problem is that in the
foreign
language version of Excel, there is no Custom chart type that
corresponds
to
the English TypeName. For example, in German the TypeName is not
"Line -
Column". It is whatever the German equivalent is. So it fails with
an
error
saying that "Line - Column" is not a valid type. If I use the
German
equivalent of "Line - Column" it works fine.

Does that make more sense?

Thanks again for the help.

"theLuggage" wrote:

We have a fairly sophisticated Excel AddIn that generates all
sorts
of
Chart
types. It works fine in English versions of Excel, but not
foreign
langauage
versions.

We've narrowed down the problem to when we're assigning the
TypeName
when we
create a chart. Here's an example of three types that don't work:
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn,
TypeName:="Line -
Column"
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:=
"Line -
Column
on 2 Axes"
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Lines
on 2
Axes"

The issue seems to be that the TypeName is localized to the native
language.
Does Excel have IDs or constants we can use? I've seen the list
of
constants for the standart Chart types. But there doesn't seem to
be
constants for the custom types.

Thanks in advance for your help.

Thanks, Peter and Jon.

Your workaround won't work unfortunately, Peter. The XL8galry.xls file is
not accessable. I think Jon's right that we just need to create the charts
in code.

That should be interesting. The AddIn may be fairly sophisticated, but I
never said I was! <grin I'm somewhat new to Excel developement. I spend
all my time on the Word side of things.



"Peter T" wrote:

Actually Jon gave good advice, as he implied there are other reasons trying
to apply chart types from the gallery might fail. A "fairly sophisticated
Excel AddIn that generates all sorts of Charts" ought generate a custom
chart to needs.

Anyway and FWIW, this is what I had in mind as a workaround. It relies on
two big assumptions -

1. When first attempting to apply a built in custom chart type XL8galry.xls
opens and can be accessed (invisible but can see it in the VBE). It always
does for me but can't be sure if it does for all.

2. Apart from the Chart names, XL8galry.xls is identical in all XL versions
and languages, in particular the charts are in same order in different
language versions. I can't test this at all.

Sub Test()
Dim sTypeName As String
Dim ch As Chart

On Error GoTo errH

sTypeName = "Linie - Säule" ' "Line - Column"
'sTypeName = "Linie - Säule auf zwei Achsen" '"Line - Column on 2 Axes"

' more tests

Set ch = ActiveChart
If ch Is Nothing Then
MsgBox "no chart is selected"
Exit Sub
End If

On Error GoTo errBuiltIn:
ch.ApplyCustomType ChartType:=xlBuiltIn, TypeName:=sTypeName

' regular error handler
On Error GoTo errH
' more code

Exit Sub

errBuiltIn:
If TranslateBuiltInName(sTypeName) Then
Resume
'Else
' Resume Next ' or Resume elsewhere or handle somehow
End If
Exit Sub

errH:

MsgBox Err.Description, , Err.Number

End Sub


Function TranslateBuiltInName(ByRef sName) As Boolean
Dim i As Long
Dim sLocalName As String
Dim sErrMsg As String
Dim vArr
Dim ch As Chart
Dim wb As Workbook
Static col As Collection

On Error GoTo errH
If col Is Nothing Then

' the order of charts in XL8galry.xls in English XL2000
vArr = Array("dummy", _
"Outdoor Bars", "Logarithmic", "Column - Area", _
"Lines on 2 Axes", "Line - Column on 2 Axes", _
"Line - Column", "Smooth Lines", "Cones", _
"Area Blocks", "Tubes", "Pie Explosion", _
"Stack of Colors", "Columns with Depth", "Blue Pie", _
"Floating Bars", "Colored Lines", "B&W Column", _
"B&W Line - Timescale", "B&W Area", "B&W Pie")

Set col = New Collection
100 Set wb = Workbooks("XL8GALRY.XLS")
If wb Is Nothing Then Err.Raise 12345
101
For Each ch In wb.Charts
i = i + 1
col.Add ch.Name, vArr(i)
Next
End If

200 sLocalName = col(sName)
201

sName = sLocalName
TranslateBuiltInName = True

Exit Function

errH:
Select Case Erl
Case 100: sErrMsg = "Cannot access XL8galry.xls"
Case 200: sErrMsg = sName & vbCr & "does not exist in XL8galry.xls"
Case Else: sErrMsg = "unexpected error" & vbCr & Err.Description
End Select

MsgBox sErrMsg

End Function

If this works (big if), debug the collection of chart names and verify they
appear in same order as those in 'vArr'. Perhaps you could let us know how
the German version compares.

Regards,
Peter T


"theLuggage" wrote in message
...
Thanks for the help, Peter.

The German equivalent for "Line - Column" is "Linie - Säule".
The German equivalent for "Line - Column on 2 Axes" is "Linie - Säule auf
zwei Achsen".



"Peter T" wrote:

I can't find any ID's or named constants for the BuiltIn custom charts,
or
something like a 'LocalName'. Even if there is such an alternative I
don't
see how it could be used, but maybe some else knows.

Is it definitely the case the code fails because of non-English names,
and
not because the type cannot be applied due to an inappropriate number of
series?

Assuming it is indeed a language problem, the obvious solution would be
to
record a macro and get the German name of the custom chart type. But I
guess
you need the code to work in all languages. If you care to post the
German
names for one or two types I have an idea for a kludgy workaround.

Regards,
Peter T

"theLuggage" wrote in message
...
I'm not sure I was clear on the problem. The problem is that in the
foreign
language version of Excel, there is no Custom chart type that
corresponds
to
the English TypeName. For example, in German the TypeName is not
"Line -
Column". It is whatever the German equivalent is. So it fails with
an
error
saying that "Line - Column" is not a valid type. If I use the German
equivalent of "Line - Column" it works fine.

Does that make more sense?

Thanks again for the help.

"theLuggage" wrote:

We have a fairly sophisticated Excel AddIn that generates all sorts
of
Chart
types. It works fine in English versions of Excel, but not foreign
langauage
versions.

We've narrowed down the problem to when we're assigning the TypeName
when we
create a chart. Here's an example of three types that don't work:
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line -
Column"
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= "Line -
Column
on 2 Axes"
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Lines
on 2
Axes"

The issue seems to be that the TypeName is localized to the native
language.
Does Excel have IDs or constants we can use? I've seen the list of
constants for the standart Chart types. But there doesn't seem to
be
constants for the custom types.

Thanks in advance for your help.

The XL8galry.xls file is not accessable.

That's a shame, I was rather hoping the method would translate the names
into local language.

I first wondered if you had incorporated the two corrections I later posted.
But either way it would fail if you can't access XL8galry.xls.

The two custom types you mentioned would be quite easy to do in VBA. You can
get most by simply recording a macro, amend to remove the any .Activate &
..Select statements, and adapt for the relevant series. Would also need some
error handling.

Regards,
Peter T


"theLuggage" wrote in message
...

Thanks, Peter and Jon.

Your workaround won't work unfortunately, Peter. The XL8galry.xls file is
not accessable. I think Jon's right that we just need to create the
charts
in code.

That should be interesting. The AddIn may be fairly sophisticated, but I
never said I was! <grin I'm somewhat new to Excel developement. I
spend
all my time on the Word side of things.



"Peter T" wrote:

Actually Jon gave good advice, as he implied there are other reasons
trying
to apply chart types from the gallery might fail. A "fairly
sophisticated
Excel AddIn that generates all sorts of Charts" ought generate a custom
chart to needs.

Anyway and FWIW, this is what I had in mind as a workaround. It relies
on
two big assumptions -

1. When first attempting to apply a built in custom chart type
XL8galry.xls
opens and can be accessed (invisible but can see it in the VBE). It
always
does for me but can't be sure if it does for all.

2. Apart from the Chart names, XL8galry.xls is identical in all XL
versions
and languages, in particular the charts are in same order in different
language versions. I can't test this at all.

Sub Test()
Dim sTypeName As String
Dim ch As Chart

On Error GoTo errH

sTypeName = "Linie - Säule" ' "Line - Column"
'sTypeName = "Linie - Säule auf zwei Achsen" '"Line - Column on 2
Axes"

' more tests

Set ch = ActiveChart
If ch Is Nothing Then
MsgBox "no chart is selected"
Exit Sub
End If

On Error GoTo errBuiltIn:
ch.ApplyCustomType ChartType:=xlBuiltIn, TypeName:=sTypeName

' regular error handler
On Error GoTo errH
' more code

Exit Sub

errBuiltIn:
If TranslateBuiltInName(sTypeName) Then
Resume
'Else
' Resume Next ' or Resume elsewhere or handle somehow
End If
Exit Sub

errH:

MsgBox Err.Description, , Err.Number

End Sub


Function TranslateBuiltInName(ByRef sName) As Boolean
Dim i As Long
Dim sLocalName As String
Dim sErrMsg As String
Dim vArr
Dim ch As Chart
Dim wb As Workbook
Static col As Collection

On Error GoTo errH
If col Is Nothing Then

' the order of charts in XL8galry.xls in English XL2000
vArr = Array("dummy", _
"Outdoor Bars", "Logarithmic", "Column - Area", _
"Lines on 2 Axes", "Line - Column on 2 Axes", _
"Line - Column", "Smooth Lines", "Cones", _
"Area Blocks", "Tubes", "Pie Explosion", _
"Stack of Colors", "Columns with Depth", "Blue
Pie", _
"Floating Bars", "Colored Lines", "B&W Column", _
"B&W Line - Timescale", "B&W Area", "B&W Pie")

Set col = New Collection
100 Set wb = Workbooks("XL8GALRY.XLS")
If wb Is Nothing Then Err.Raise 12345
101
For Each ch In wb.Charts
i = i + 1
col.Add ch.Name, vArr(i)
Next
End If

200 sLocalName = col(sName)
201

sName = sLocalName
TranslateBuiltInName = True

Exit Function

errH:
Select Case Erl
Case 100: sErrMsg = "Cannot access XL8galry.xls"
Case 200: sErrMsg = sName & vbCr & "does not exist in
XL8galry.xls"
Case Else: sErrMsg = "unexpected error" & vbCr & Err.Description
End Select

MsgBox sErrMsg

End Function

If this works (big if), debug the collection of chart names and verify
they
appear in same order as those in 'vArr'. Perhaps you could let us know
how
the German version compares.

Regards,
Peter T


"theLuggage" wrote in message
...
Thanks for the help, Peter.

The German equivalent for "Line - Column" is "Linie - Säule".
The German equivalent for "Line - Column on 2 Axes" is "Linie - Säule
auf
zwei Achsen".



"Peter T" wrote:

I can't find any ID's or named constants for the BuiltIn custom
charts,
or
something like a 'LocalName'. Even if there is such an alternative I
don't
see how it could be used, but maybe some else knows.

Is it definitely the case the code fails because of non-English
names,
and
not because the type cannot be applied due to an inappropriate
number of
series?

Assuming it is indeed a language problem, the obvious solution would
be
to
record a macro and get the German name of the custom chart type. But
I
guess
you need the code to work in all languages. If you care to post the
German
names for one or two types I have an idea for a kludgy workaround.

Regards,
Peter T

"theLuggage" wrote in message
...
I'm not sure I was clear on the problem. The problem is that in
the
foreign
language version of Excel, there is no Custom chart type that
corresponds
to
the English TypeName. For example, in German the TypeName is not
"Line -
Column". It is whatever the German equivalent is. So it fails
with
an
error
saying that "Line - Column" is not a valid type. If I use the
German
equivalent of "Line - Column" it works fine.

Does that make more sense?

Thanks again for the help.

"theLuggage" wrote:

We have a fairly sophisticated Excel AddIn that generates all
sorts
of
Chart
types. It works fine in English versions of Excel, but not
foreign
langauage
versions.

We've narrowed down the problem to when we're assigning the
TypeName
when we
create a chart. Here's an example of three types that don't
work:
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn,
TypeName:="Line -
Column"
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:=
"Line -
Column
on 2 Axes"
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn,
TypeName:="Lines
on 2
Axes"

The issue seems to be that the TypeName is localized to the
native
language.
Does Excel have IDs or constants we can use? I've seen the
list of
constants for the standart Chart types. But there doesn't seem
to
be
constants for the custom types.

Thanks in advance for your help.

Excel's way easier to program than Word. Excel's object model is fairly
logical, while Word's was designed by the inmates of a schizophrenia ward.
No offense to any schizophrenics reading this.

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


"theLuggage" wrote in message
...

Thanks, Peter and Jon.

Your workaround won't work unfortunately, Peter. The XL8galry.xls file is
not accessable. I think Jon's right that we just need to create the
charts
in code.

That should be interesting. The AddIn may be fairly sophisticated, but I
never said I was! <grin I'm somewhat new to Excel developement. I
spend
all my time on the Word side of things.



"Peter T" wrote:

Actually Jon gave good advice, as he implied there are other reasons
trying
to apply chart types from the gallery might fail. A "fairly sophisticated
Excel AddIn that generates all sorts of Charts" ought generate a custom
chart to needs.

Anyway and FWIW, this is what I had in mind as a workaround. It relies on
two big assumptions -

1. When first attempting to apply a built in custom chart type
XL8galry.xls
opens and can be accessed (invisible but can see it in the VBE). It
always
does for me but can't be sure if it does for all.

2. Apart from the Chart names, XL8galry.xls is identical in all XL
versions
and languages, in particular the charts are in same order in different
language versions. I can't test this at all.

Sub Test()
Dim sTypeName As String
Dim ch As Chart

On Error GoTo errH

sTypeName = "Linie - Säule" ' "Line - Column"
'sTypeName = "Linie - Säule auf zwei Achsen" '"Line - Column on 2
Axes"

' more tests

Set ch = ActiveChart
If ch Is Nothing Then
MsgBox "no chart is selected"
Exit Sub
End If

On Error GoTo errBuiltIn:
ch.ApplyCustomType ChartType:=xlBuiltIn, TypeName:=sTypeName

' regular error handler
On Error GoTo errH
' more code

Exit Sub

errBuiltIn:
If TranslateBuiltInName(sTypeName) Then
Resume
'Else
' Resume Next ' or Resume elsewhere or handle somehow
End If
Exit Sub

errH:

MsgBox Err.Description, , Err.Number

End Sub


Function TranslateBuiltInName(ByRef sName) As Boolean
Dim i As Long
Dim sLocalName As String
Dim sErrMsg As String
Dim vArr
Dim ch As Chart
Dim wb As Workbook
Static col As Collection

On Error GoTo errH
If col Is Nothing Then

' the order of charts in XL8galry.xls in English XL2000
vArr = Array("dummy", _
"Outdoor Bars", "Logarithmic", "Column - Area", _
"Lines on 2 Axes", "Line - Column on 2 Axes", _
"Line - Column", "Smooth Lines", "Cones", _
"Area Blocks", "Tubes", "Pie Explosion", _
"Stack of Colors", "Columns with Depth", "Blue Pie",
_
"Floating Bars", "Colored Lines", "B&W Column", _
"B&W Line - Timescale", "B&W Area", "B&W Pie")

Set col = New Collection
100 Set wb = Workbooks("XL8GALRY.XLS")
If wb Is Nothing Then Err.Raise 12345
101
For Each ch In wb.Charts
i = i + 1
col.Add ch.Name, vArr(i)
Next
End If

200 sLocalName = col(sName)
201

sName = sLocalName
TranslateBuiltInName = True

Exit Function

errH:
Select Case Erl
Case 100: sErrMsg = "Cannot access XL8galry.xls"
Case 200: sErrMsg = sName & vbCr & "does not exist in
XL8galry.xls"
Case Else: sErrMsg = "unexpected error" & vbCr & Err.Description
End Select

MsgBox sErrMsg

End Function

If this works (big if), debug the collection of chart names and verify
they
appear in same order as those in 'vArr'. Perhaps you could let us know
how
the German version compares.

Regards,
Peter T


"theLuggage" wrote in message
...
Thanks for the help, Peter.

The German equivalent for "Line - Column" is "Linie - Säule".
The German equivalent for "Line - Column on 2 Axes" is "Linie - Säule
auf
zwei Achsen".



"Peter T" wrote:

I can't find any ID's or named constants for the BuiltIn custom
charts,
or
something like a 'LocalName'. Even if there is such an alternative I
don't
see how it could be used, but maybe some else knows.

Is it definitely the case the code fails because of non-English
names,
and
not because the type cannot be applied due to an inappropriate number
of
series?

Assuming it is indeed a language problem, the obvious solution would
be
to
record a macro and get the German name of the custom chart type. But
I
guess
you need the code to work in all languages. If you care to post the
German
names for one or two types I have an idea for a kludgy workaround.

Regards,
Peter T

"theLuggage" wrote in message
...
I'm not sure I was clear on the problem. The problem is that in
the
foreign
language version of Excel, there is no Custom chart type that
corresponds
to
the English TypeName. For example, in German the TypeName is not
"Line -
Column". It is whatever the German equivalent is. So it fails
with
an
error
saying that "Line - Column" is not a valid type. If I use the
German
equivalent of "Line - Column" it works fine.

Does that make more sense?

Thanks again for the help.

"theLuggage" wrote:

We have a fairly sophisticated Excel AddIn that generates all
sorts
of
Chart
types. It works fine in English versions of Excel, but not
foreign
langauage
versions.

We've narrowed down the problem to when we're assigning the
TypeName
when we
create a chart. Here's an example of three types that don't
work:
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn,
TypeName:="Line -
Column"
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:=
"Line -
Column
on 2 Axes"
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn,
TypeName:="Lines
on 2
Axes"

The issue seems to be that the TypeName is localized to the
native
language.
Does Excel have IDs or constants we can use? I've seen the list
of
constants for the standart Chart types. But there doesn't seem
to
be
constants for the custom types.

Thanks in advance for your help.