Excel functions/formular via OLE

Hello all,

The OLE interface from VO to Excel is working fine: Data is transferred,
diagrams are created.

But: On some Excel installations the functions (e.g. SUM() ) is not
correctly displayed, but an ugly #NAME? If the button F2 in Excel is
pressed and then the enter button its fine again.

I create an array in VO, write all fixed values and functions into it
and transfer it via oRange:value to Excel.

oRange := oWorkSheet:[Range, "A1", cEndPosition]
oRange:value := aRange

Any hints on that?

Thanks

Niko

On Thu, 04 Sep 2008 21:36:33 +0200, Nikolaus Kern
<parzival1969-youremovethis@gmx.at> wrote:

Hello Niko,

>But: On some Excel installations the functions (e.g. SUM() ) is not
>correctly displayed, but an ugly #NAME? If the button F2 in Excel is
>pressed and then the enter button its fine again.

We had to solve dozens of problems with Excel. If I remember well, the
code below solves the problem you mentioned. For some reason you can
not enter a general English function like SUM in non English Excel
versions and in other version you HAVE to enter it like this. Idiot
thing is that when it's entered via automation, it gives the error you
mention, but when you edit it, it suddenly accepts it. Anyhow, we use:

SELFRange:Formula:=ExcFnc(SELF:nExcLang,"SOM",11 ) +
SELF:cVanCel+":" + SELF:cTotCel+")"

This determines depending on the version number if it should return
the language specific function or just the English one. I think you
have to add the German translation.

Dick van Kooten

FUNCTION ExcFnc(nLanguagecode AS FLOAT,cDutch AS STRING,nExcelVersion
AS DWORD) AS STRING
//#s Returns Excel command passed in Dutch in language of
nLanguagecode 1-7-2004
//#s
//#
//#x
//#
//#l
//#l
//#l
//#l
//#
//#p
//#
//#r
//#
//#e
//#
LOCAL cFormat AS STRING
// Arabic 966 (Saudi Arabia)
// Czech 42 (Czech Republic)
// Danish 45 (Denmark)
// Dutch 31 (The Netherlands)
// English 1 (The United States OF America)
// Farsi 98 (Iran)
// Finnish 358 (Finland)
// French 33 (France)
// German 49 (Germany)
// Greek 30 (Greece)
// Hebrew 972 (Israel)
// Hungarian 36 (Hungary)
// Indian 91 (India)
// Italian 39 (Italy)
// Japanese 81 (Japan)
// Korean 82 (Korea)
// Norwegian 47 (Norway)
// Polish 48 (Poland)
// Portuguese (Brazil) 55 (Brazil)
// Portuguese 351 (Portugal)
// Russian 7 (Russian Federation)
// Simplified Chinese 86 (People's Republic OF China)
// Spanish 34 (Spain)
// Swedish 46 (Sweden)
// Thai 66 (Thailand)
// Traditional Chinese 886 (Taiwan)
// Turkish 90 (Turkey)
// Urdu 92 (Pakistan)
// Vietnamese 84 (Vietnam)
LOCAL cDecSep, cThSep AS STRING
LOCAL nDecimals AS DWORD

cDecSep := CHR(SetDecimalSep())
cThSep := CHR(SetThousandSep())
// nDecimals - number of Decimals
DO CASE
CASE cDutch="SOM"
IF nExcelVersion>10
RETURN "=SUM("
ELSE
DO CASE
CASE nLanguagecode=1 //
English
RETURN "=SUM("
CASE nLanguagecode=31 //
Dutch
RETURN "=SOM("
CASE nLanguagecode=33 //
French
RETURN "=SOMME("
ENDCASE
ENDIF
ENDCASE
RETURN cDutch

Hello Dick,

There are actually two problems:
1) The use of the different function names (e.g. SUM() vs. SOM() vs.
SUMME() ) -> This can be solved by the function below. Is not there a
function-call for Excel to switch this into a wanted language?
2) I am using oRange:value := aRange to assign an array containing fixed
values (text, numbers) and functions SUM(). This works ok on the dev PC
(XP ger) and a notebook (XP eng) but not on the PC of the customer (XP,
ger). I guess that there is a difference between oRange:value and
oRange:formula. Both are working on my dev PC...

I would like to keep the simple way to transfer any data and/or
functions from VO to Excel. How to mix both and send them to Excel:
values and functions?

Niko

> On Thu, 04 Sep 2008 21:36:33 +0200, Nikolaus Kern
> <parzival1969-youremovethis@gmx.at> wrote:
>
> Hello Niko,
>
>> But: On some Excel installations the functions (e.g. SUM() ) is not
>> correctly displayed, but an ugly #NAME? If the button F2 in Excel is
>> pressed and then the enter button its fine again.
>
> We had to solve dozens of problems with Excel. If I remember well, the
> code below solves the problem you mentioned. For some reason you can
> not enter a general English function like SUM in non English Excel
> versions and in other version you HAVE to enter it like this. Idiot
> thing is that when it's entered via automation, it gives the error you
> mention, but when you edit it, it suddenly accepts it. Anyhow, we use:
>
> SELFRange:Formula:=ExcFnc(SELF:nExcLang,"SOM",11 ) +
> SELF:cVanCel+":" + SELF:cTotCel+")"
>
> This determines depending on the version number if it should return
> the language specific function or just the English one. I think you
> have to add the German translation.
>
> Dick van Kooten
>
> FUNCTION ExcFnc(nLanguagecode AS FLOAT,cDutch AS STRING,nExcelVersion
> AS DWORD) AS STRING
> //#s Returns Excel command passed in Dutch in language of
> nLanguagecode 1-7-2004
> //#s
> //#
> //#x
> //#
> //#l
> //#l
> //#l
> //#l
> //#
> //#p
> //#
> //#r
> //#
> //#e
> //#
> LOCAL cFormat AS STRING
> // Arabic 966 (Saudi Arabia)
> // Czech 42 (Czech Republic)
> // Danish 45 (Denmark)
> // Dutch 31 (The Netherlands)
> // English 1 (The United States OF America)
> // Farsi 98 (Iran)
> // Finnish 358 (Finland)
> // French 33 (France)
> // German 49 (Germany)
> // Greek 30 (Greece)
> // Hebrew 972 (Israel)
> // Hungarian 36 (Hungary)
> // Indian 91 (India)
> // Italian 39 (Italy)
> // Japanese 81 (Japan)
> // Korean 82 (Korea)
> // Norwegian 47 (Norway)
> // Polish 48 (Poland)
> // Portuguese (Brazil) 55 (Brazil)
> // Portuguese 351 (Portugal)
> // Russian 7 (Russian Federation)
> // Simplified Chinese 86 (People's Republic OF China)
> // Spanish 34 (Spain)
> // Swedish 46 (Sweden)
> // Thai 66 (Thailand)
> // Traditional Chinese 886 (Taiwan)
> // Turkish 90 (Turkey)
> // Urdu 92 (Pakistan)
> // Vietnamese 84 (Vietnam)
> LOCAL cDecSep, cThSep AS STRING
> LOCAL nDecimals AS DWORD
>
> cDecSep := CHR(SetDecimalSep())
> cThSep := CHR(SetThousandSep())
> // nDecimals - number of Decimals
> DO CASE
> CASE cDutch="SOM"
> IF nExcelVersion>10
> RETURN "=SUM("
> ELSE
> DO CASE
> CASE nLanguagecode=1 //
> English
> RETURN "=SUM("
> CASE nLanguagecode=31 //
> Dutch
> RETURN "=SOM("
> CASE nLanguagecode=33 //
> French
> RETURN "=SOMME("
> ENDCASE
> ENDIF
> ENDCASE
> RETURN cDutch
>