Problems with decimal separator using "Send to ....."

alexjensen

Hi all

I am testing the NAV 5.0 (SP1) "Send to Microsoft Excel" function. I use the standard stylesheet "NavisionFormToExcel.xslt" installed with NAV 5. But if I export e.g. "Chart of Accounts" the numbers looks like this:

-5539444.84

I'm located in Denmark, så the correct number format would be -5539444,84. Excel seems to format the number (-5539444.84) as text. My regional settings are Danish but even though I have tried to change these to English the same thing happen.

I have checked the code in CU403 (Application Launch Management) and it seems like the developers have tried to solve it in the function AddLocaleInfo:

ParentNode := DataXML.documentElement;
ChildNode := DataXML.createElement('DecimalSeparator');
ChildNode.text := COPYSTR(FORMAT(1.1),2,1);
ParentNode.appendChild(ChildNode);

I have tried the same scenario using NAV 2009 (SP1) and it works. So I have tried different things in order to get it working in NAV 5 (as a client upgrade is not an option right now). I have tried merging NAV 2009 code from CU403 to NAV 5 and using the 2009 stylesheets. But when I try this no numbers are exported - all number columns are 0 (zero). It seems like it only works when I use the 2009 client. If I have 2 databases with the same objects and stylesheets (2009) it only works with NAV 2009 and not NAV 5.

Have anyone in this forum had he same problems and maybe found a solution?

Best regards,

Alex Jensen

sgi

I have done the same copy AddLocaleInfo funcion changes from 2009 to 5.0 and can get it to work fine in Excel 2003 but not with Excel 2007 - there I get the zeros as you do. The big difference is that Nav2009 uses MSXML 6 throughout CU 1 and 403 while Nav 5.0 codeunit 1 and 403 use MSXML 4 and then they have some extra functions to copy from XML 4 to XML 6 function, transform the nodes using XML 6 and then copy back to XML 4 after.

Apparently Excel 2003 can work out the result but excel 2007 cannot. But since excel documents changed drastically between these 2 versions, I am not really surprised.

I am not an expert in MSXML/Nav/Excel version compatibility, so I am giving it further to our solution center to ask MS about.

jannestig

I believe theres a hotfix for it

YOu need update 1 installed before it is resolved the reason is below

https://mbs.microsoft.com/knowledgebase ... -us;956161

When you export a number to an Excel worksheet, the number is formatted as text instead of as a number. Therefore, Microsoft Dynamics NAV does not consider the regional thousand separator as expected.

You probably confirm this by formatting the cells manually

sgi

Thanks for the tip. We are in the process of ensuring unity of client versions on our citrix servers so we will make sure we have run the platform update KB956161 on the citrix servers as well and see if that fixes the problem with the zeros.

Cheers

Steve

jannestig

Citrix could be another issue as well as you may have to look at what the regional server setting there are and the applications server for office apps.

IF you have users in the UK or locations with different regional settings you may have to test there again

I like to keep my client versions up todate not just the straight CD install like my company seems to do though ](*,)