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" <(E-Mail Removed)> wrote in message
news:07D9A62E-C6D1-402E-BC02-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:07D9A62E-C6D1-402E-BC02-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:07D9A62E-C6D1-402E-BC02-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> news:07D9A62E-C6D1-402E-BC02-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> news:07D9A62E-C6D1-402E-BC02-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
news:F250CB6B-2091-4B86-8864-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> > news:07D9A62E-C6D1-402E-BC02-(E-Mail Removed)...
> > > 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
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:F250CB6B-2091-4B86-8864-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > > news:07D9A62E-C6D1-402E-BC02-(E-Mail Removed)...
> > > > 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" <(E-Mail Removed)> wrote in message
> news:F250CB6B-2091-4B86-8864-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > > news:07D9A62E-C6D1-402E-BC02-(E-Mail Removed)...
> > > > 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.
> > >
> > >
> > >
>
>
>