Formula's in Excel using automation

Luc_VanDyckLuc_VanDyck AartselaarMember, Moderator, Administrator Posts: 3,632
edited 2010-03-23 in Navision Attain
When working with formula's in Excel using automation, you have to know what language-version of Excel is installed (English, Dutch, French, etc.) in order to write your formula (eg. SUM/SOM/SOMME, ROUND/AFRONDEN/...).

The function
xlApp.International(lxlCountryCode));
(with lxlCountryCode = 1) returns the language being used.

There must be a way to use formula's independant of the language, so you only have to write them in English.

How can you do this?
No support using PM or e-mail - Please use this forum.
NAV TechDays 2020: 19 & 20 November 2020, Antwerp (Belgium)

Comments

  • mart1n0mart1n0 Member Posts: 123
    Anybody ever sorted out how to do this?

    If I do the following in Excel Range("A1").Formula = "=Sum(B1:B10)", A1 will contain the formula in the local user his language.

    If I do the same in Navision, it A1 always contains "=Sum(B1:B10)", it does not translate the formula.

    It would be SUPER, and I mean super super handy to get this working since we have EMEA offices where all users have Excel in their native language and I do not want to start looking at the Excel version of the user and start saving all formulas in all languages.
  • mart1n0mart1n0 Member Posts: 123
    I've finally found an easy way to do this, I'll put the code here as soon as I have a free minute!
  • AntidotEAntidotE Member Posts: 60
    Still no free minute? Fri - Tue... hmm... :-#
    It is hard to swim against self bloodstream... (c) Old, experienced kamikadze.
  • mart1n0mart1n0 Member Posts: 123
    Hi AntidotE,
    I was actually entering an article about this on my blog when I got a message you replied to this post :-).

    You can read how I solved it on my blog article here: http://www.noisyvan.com/2008/10/09/make-exporting-excel-formulas-fully-multilanguage-aware/.

    Is it allowed to put just a link to this article or should I cope the content over here too?
  • Luc_VanDyckLuc_VanDyck AartselaarMember, Moderator, Administrator Posts: 3,632
    mart1n0 wrote:
    Is it allowed to put just a link to this article or should I cope the content over here too?
    If you copy the content overhere, this topic can be searched (and found!) using the forum search engine.
    No support using PM or e-mail - Please use this forum.
    NAV TechDays 2020: 19 & 20 November 2020, Antwerp (Belgium)
  • AntidotEAntidotE Member Posts: 60
    Actually, old link is incorrect already, new one: http://noisyvan.wordpress.com/2008/10/0 ... uage-aware
    Anyway, I'll take a time and post this blog page here (screenshots will be from blog too):
    Make Exporting Excel Formulas Fully Multilanguage Aware
    October 9, 2008
    I have been struggling with exporting data from Dynamics NAV to Excel Spreadsheets including formulas lately. I had the problem with a custom developed export to excel so I checked the code in the Excel Buffer table to see how standard Navision solved the problem only to see that the problem also existed in standard NAV too.
    Let’s explain the problem first. What happens is that for people with a Microsoft Office version in Language A and a Dynamics NAV client in language B, Navision does not translate the formulas correctly. You can reproduce it as follows.
    Make sure the language of your Dynamics NAV client and of Microsoft Office are different. Open your Dynamics NAV client go to the Budget form and run the “Export to Excel …” function. You’ll see that the formulas aren’t be translated as they should, giving errors in Excel.
    nueva-imagen2.png
    English NAV Client and Spanish Office, wrong formula
    Standard Dynamics NAV uses multilanguage text constants to translate the Formulas in the Excel Buffer table. The problem is that this only works if your Microsoft Office language equals your Dynamics NAV Client language and on top of this, for it to work you need to translate ALL the formulas you want to use to ALL the languages you want to use. Not a fun thing to do but feel free to use a page like this fonctions XL en 9 langues or this Microsoft Excel function translations to get started putting all your formulas into multilanguage text constants.
    Since we have some workstations where the Dynamics NAV client and Microsoft Office use a different language, I was looking for something better and this post on mibuso handed me the solution. Below I put the code if you want this feature to work for the Excel Buffer table but you’ll see that it is easy to adopt it to all of you custom code that exports to Excel with formulas.
    Table 370 – Excel Buffer
    Add the following local variables to the CreateSheet function:
    • autScriptControl Automation variable with SubType ‘Microsoft Script Control 1.0′.ScriptControl
    • txtCode Text variable with Length 1024
    • autDictionary Automation variable with Subtype ‘Microsoft Scripting Runtime’.Dictionary
    • txtFormula Text variable with Length 1024
    • intI Integer variable
    Remove all languages but English from the text constants SUM, SUMIF and others should there be others.
    Change to code of the CreateSheet function as below:
    XlWrkSht.Name := SheetName;
    IF ReportHeader <> '' THEN
    XlWrkSht.PageSetup.LeftHeader :=
    STRSUBSTNO('%1%2%1%3%4',GetExcelReference(1),ReportHeader,CRLF,CompanyName);
    XlWrkSht.PageSetup.RightHeader :=
    STRSUBSTNO(Text006,GetExcelReference(2),GetExcelReference(3),CRLF,UserID2);
    XlWrkSht.PageSetup.Orientation := XlLandscape;
    IF FIND('-') THEN BEGIN
    //CRQ.001 start insert
    CREATE(autDictionary);
    CREATE(autScriptControl);
    //CRQ.001 end insert
    REPEAT
    RecNo := RecNo + 1;
    Window.UPDATE(1,ROUND(RecNo / TotalRecNo * 10000,1));
    IF NumberFormat <> '' THEN
    XlWrkSht.Range(xlColID + xlRowID).NumberFormat := NumberFormat;
    //CRQ.001 delete start
    //  IF Formula = '' THEN
    //    XlWrkSht.Range(xlColID + xlRowID).Value := "Cell Value as Text"
    //  ELSE
    //    XlWrkSht.Range(xlColID + xlRowID).Formula := GetFormula;
    //CRQ.001 delete end
    //CRQ.001 start insert
    IF Formula = '' THEN
    XlWrkSht.Range(xlColID + xlRowID).Value := "Cell Value as Text"
    ELSE BEGIN
    txtFormula := GetFormula();
    txtCode :='objCell.Formula=objList.Item(1);';
    intI := 1;
    autScriptControl.Language := 'JScript';
    autScriptControl.AddObject('objCell', XlWrkSht.Range(xlColID + xlRowID));// Add excel cell object
    autScriptControl.AddObject('objList', autDictionary);                    // Add dictionary object
    autDictionary.Add(intI,txtFormula);                                      // Add formula to dictionary object
    autScriptControl.ExecuteStatement(txtCode);                              // Execute javascript
    autDictionary.RemoveAll();                                               // Remove item from dictionary
    autScriptControl.Reset();                                                // Reset Script object for next using
    END;
    //CRQ.001 end insert[/b]IF Comment <> '' THEN
    XlWrkSht.Range(xlColID + xlRowID).AddComment := Comment;
    IF Bold THEN
    XlWrkSht.Range(xlColID + xlRowID).Font.Bold := Bold;
    IF Italic THEN
    XlWrkSht.Range(xlColID + xlRowID).Font.Italic := Italic;
    XlWrkSht.Range(xlColID + xlRowID).Borders.LineStyle := XlLineStyleNone;
    IF Underline THEN
    XlWrkSht.Range(xlColID + xlRowID).Borders.Item(XlEdgeBottom).LineStyle := XlContinuous;
    UNTIL NEXT = 0;
    //CRQ.001 start insert
    CLEAR(autDictionary);
    CLEAR(autScriptControl);
    //CRQ.001 end insert[/b]XlWrkSht.Range(GetExcelReference(5) + ':' + xlColID + xlRowID).Columns.AutoFit;
    END;
    
    Let’s have a look at the “Export to Excel …” function on the Budget form again.
    nueva-imagen-1.png
    English NAV Client and Spanish Office, correct formula
    That’s all, it is that simple, no more text constants, no more looking for translations of a formula, just put all your fomulas in English and let Excel do the work. Please let me know if you know a faster/better/cleaner way to do this.
    It is hard to swim against self bloodstream... (c) Old, experienced kamikadze.
  • ricardopaivaricardopaiva PortugalMember Posts: 14
    Even though this post is rather old, here's how to handle this.
    https://ricardomoinhos.com/excel-buffer-detect-excels-language/
Sign In or Register to comment.