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.