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.
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 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 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 XlWrkSht.Range(GetExcelReference(5) + ':' + xlColID + xlRowID).Columns.AutoFit; END;
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.
