Send To Excel Problem with Decimal Type

mscalera

Hi all,
I'm working with dynamics nav 5.1.

I created a new tabular type form, from sales invoice line; not all columns are real fields, some of theme are functions.
Anyway, when executing
"Send To Microsoft Excel..."
all numerics columns exported are text formatted ( as if starting with " ' ", but no "'" is in the cell).

With this values no operation is possible.
I'm using <Default> Style Sheet.

Yes, I know I can do copy and paste over excel ... but is really better send to excel.
Can anybody help me ?

AlexWiley

I'm not really clear on what is happening... can you type an example of how the numerical values are displayed in Excel? Unit Price, Line Amount Excl. VAT, etc? Once I know what I'm looking for I can try to regenerate the same issue.

mscalera

This is print screen
note that in italy we use dot (.) as thousands separator and (,) as decimal separator.

AlexWiley

Have you changed the system separator in the Excel options?

jannestig

You should have had a example of a 1000 + value you then.

Did this today for a finnish client. if you are 5.1 there is a code fix in it where it always reads the digit group symbol or "thousand seperator" from the server or client language and region settings

described as
- When you export a number that uses a space as the thousand separator from Microsoft Dynamics NAV to a Microsoft Word file, the exported number always uses the format that is specified in the regional settings.

- 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.

Solution is
https://mbs.microsoft.com/knowledgebase ... omnpprxpxm


The problem in this particular case is caused by the syntax of the specific format mask chosen rather than the fact that the format mask already exists as the error message indicates. The problem with this particular syntax is the choice of a 'space' as thousand separators. In Enterprise Reporting a 'space' is not a valid character for thousand separators as it is in e.g. Excel. Since the system cannot interpret the syntax the topical error message is returned as a warning for the user.

But even if you cannot set up a 'space' as thousand separators directly in Enterprise Reporting you can however



Apply the option of ‘space’ as a thousand separator as an overall character for your workstation, which will then automatically force ER to use a 'space' as thousand separators as well. You can apply a 'space' as the overall character for your workstation within your Regional Settings as follows:

1. Open up Regional Settings. (Go to your Start menu | Choose Settings | Control Panel | Regional Settings)



2. Choose the Number tab.



3. In the Digit Grouping Symbol filed you replace the existing separator character with a 'space'.



4. Click OK in order to store the new setting.

If you now execute your report in Enterprise Reporting (using your initial format mask defined with period as the thousand separator) your figures will appear with a 'space' as thousand separators as it is defined under your regional settings

mscalera

AlexWiley wrote:

Have you changed the system separator in the Excel options?

no, this is system regional setting that excel inherits

jannestig

Did you check or implemement the changes mentioed above ?