Safely automating any language version of Excel

[cpnet]

I (and one other developer) have posted problems in the
microsoft.public.office.developer.automation newsgroup about automation of
the Swedish version of Excel. (See, "Help w/ Swedish RPC error" - Jun 14,
2005, and, "international excel issues", June 17, 2005 in
microsoft.public.office.developer.automation). In my particular case, my
automation code was
built on the English version of Excel. I'm creating a calculated field for
use in a pivot table that includes the "if" statement (ie "IF( A, B, C)").
My code is failing on Swedish versions of Excel. If I change my code to
use, "OM( A; B; C)", then it works fine in the Swedish version of Excel.

I notice that this problem only occurs during automation. If a spreadsheet
is created using the English Excel, using "IF", I can save the spreadsheet.
Then, I can open it in the Swedish version of Excel, and my calculated
fields are automatically translated to use, "OM". As soon as I try to add
"IF" in a Swedish version of Excel via automation, I have problems. I have
seen this problem on Excel 2000, 2002 (XP), and 2003.

I some articles about setting LCID to the Swedish code, but I don't see how
that will help me here. In my testing of the Swedish Excel, it's a Swedish
version of Excel running on a Swedish version of XP, so LCID should already
have the Swedish setting. Am I missing something?

Thanks.

 

[cpnet]

Well, I found a partial solution to my problem as well. If I use:

CalculatedFields.Add("MyCalcField", strMyFormula, True)

then my code works in the English and Swedish versions of Excel (2002,2003).

The 'new' signature for CalculatedFields.Add is:

CalculatedFields.Add(Name, Formula, UseStandardFormula)

where, "UseStandardFormula" is optional and False by default. Setting it to
True seems to get Excel to do the appropriate translation of my formula.

Unfortunately, in Excel '97, the signature is:

CalculatedFields.Add(Name, Formula)

So, my new code breaks in Excel '97. I haven't tested Excel 2000. It seems
then I have to decide between not worrying about language and only support
Excel 2002 up, or support all versions of Excel, but the also aware of the
language of Excel that I'm currently automating and formatting my formulas
accordingly.