Creating charts in non-English versions of Excel

[theLuggage]

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.

 

[theLuggage]

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.

 

[Peter T]

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

 

[Jon Peltier]

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

 

[theLuggage]

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".

 

[theLuggage]

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

 

[theLuggage]

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]

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

 

[Peter T]

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

 

[theLuggage]

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]

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

 

[Jon Peltier]

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