Excel Buffer – Detect Excel’s Language

Dynamics NAV allows you to “easily” export information to an Excel file, via code, using the Excel Buffer table.

To export a cell value as a formula, you may add and use the following function to your object:

LOCAL EnterFormula(RowNo : Integer;ColumnNo : Integer;CellValue : Text[250];Bold : Boolean;UnderLine : Boolean;NumberFormat : Text[30])
ExcelBuf.INIT;
ExcelBuf.VALIDATE(“Row No.”,RowNo);
ExcelBuf.VALIDATE(“Column No.”,ColumnNo);
ExcelBuf.”Cell Value as Text” := ”;
ExcelBuf.Formula := CellValue; // is converted to formula later.
ExcelBuf.Bold := Bold;
ExcelBuf.Underline := UnderLine;
ExcelBuf.NumberFormat := NumberFormat;
ExcelBuf.INSERT;

For the sake of simplicity, if you want to sum the value of two cells you’ll write the following formula in Excel:

=SUM(A1:A2)

That will be fine if Excel’s language is English. What if Excel is in Portuguese? Or Dutch? Or if you have different users with different Excel languages?

Well, there’s a way!

First, create a new function in Excel Buffer table (T 370) to check the Excel language. The function can be created in your object but it is a good practice to create it in Excel Buffer (or a new object exclusively for this) so that you have the Excel automations/dotnet dependencies in a single object that you need to compile only once in a while.

So the function would look like this:

GetLanguageID() LanguageID : Integer

IF NOT CREATE(XlApp2,TRUE,TRUE) THEN
ERROR(Text000);
XlApp2.Visible(FALSE);

LanguageID := XlApp2.LanguageSettings.LanguageID(1);
XlApp2.Quit;
CLEAR(XlApp2);

EXIT(LanguageID);

XlApp2 is a local variable:

Name   DataType                           Subtype              Length
XlApp2 Automation/Dotnet       ‘Microsoft Excel 15.0 Object Library’.Application

Then, in your object you can create a new function to retrieve the correct formula, like this:

GetSumFormula() : Text[30]

CASE ExcelLanguageID OF
2070:
EXIT(‘SOMA’);
1043:
EXIT(‘SOM’);
ELSE
EXIT(‘SUM’);
END;

And a new function to retrieve and store the ExcelLanguageID, to be used when needed:

GetExcelLanguageID() : Text[30]

IF ExcelLanguageID <> 0 THEN
EXIT;

EXIT(ExcelBuf.GetLanguageID);

ExcelLanguageID is a global variable:

Name                           DataType
ExcelLanguageID      Integer

So you’d use the following line of code to add the formula to the field in row 3, column 1:

EnterFormula(3,1,
‘=’ + GetSumFormula + ‘(A1:B1)’,
TRUE,FALSE,”);

Any questions you have, feel free to ask.

Share this

4 Comments

  1. Hello!
    Your post has helped me a lot, but I have a question

    I need to send a formula in text and return the result, how do I do it? Up to now, this is my code:
    ExcelBuf.INIT;
    ExcelBuf.VALIDATE(“Row No.”,3);
    ExcelBuf.VALIDATE(“Column No.”,1);
    ExcelBuf.”Cell Value as Text”:= ”;
    ExcelBuf.Comment:=”;
    ExcelBuf.Formula := txtFormula;
    ExcelBuf.NumberFormat := ”;
    ExcelBuf.INSERT;

    • Hi Mar,

      I believe you won’t be able to do it easily.
      When you are inserting records in the ExcelBuf, you’re just populating the table in Dynamics NAV.
      The Excel file is only created after calling the CreateBook/WriteSheet/CloseBook methods, which are called at the end.

      You may try to save the Excel file and then use the OpenBook/ReadSheet methods to open the file and try get the value from the cell where you write the formula.

  2. Hello

    could you please tell me if there is a way to highlight non-English words (especially German) in an Excel spreadsheet. For example in A1 I have English texts along with some German texts. Is there an option to remove non English words?

    Many thanks in advance

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.