Export to Excel in NAV5.0SP1 - Number stored as text

R_van_den_Boom · 2010-10-19

When exporting the Chart of Accounts in NAV 5.0SP1 with the excel export button in the NAV client, the numbers that were exported all get stored as text.

I am using Excel 2007, is this something I need to modify in my Excel settings?

Thank you all for your time,

Luc_VanDyck · 2010-10-19

I searched this forum on excel+export+text, and came up with this thread: Problems with decimal separator using "Send to .....".
In that thread there is a link to a Microsoft KB article: Platform Update rollup 956161 is available for Microsoft Dynamics NAV 5.0 SP1.
And in that KB article, you can read this:

Additionally, this platform update rollup resolves the following problems that are not documented:
[...]
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.

R_van_den_Boom · 2010-10-28

Thanks for the feedback, we installed this update but the problem has not been fixed yet unfortunately..

Still getting the same results when exporting to Excel

Luc_VanDyck · 2010-10-28

Then I suggest you contact Microsoft support, directly or via your NAV partner.

R_van_den_Boom · 2010-10-29

Did some additional searching and it seems this is something you can fix in the stylesheet.

This only works in Navision 5.0 SP1, from build 27191.

In NAV :
• Open Form 690 (Manage Style Sheets).
• Select the stylesheet for exporting to Excel.
• Functions, export.
• Open the file in Notepad or visual studio
• Modify the following in the Style sheet:
<xsl:when test='@subtype != 'number'''> replace by <xsl:when test='@datatype != 'Decimal''>
<xsl:when test='@subtype = 'number'''> replace by <xsl:when test='@datatype = 'Decimal''>
PLEASE NOTE the capitals..
• Save the Style Sheet.
• Delete the existing Stylesheet in Navision
• Functions, Import.
• Select the newly imported stylesheet in the model
• Send To program: EXCEL.EXE

This did the trick for me, no more numbers stored as text now in Office2007

Luc_VanDyck · 2010-10-30

Thx for posting the solution! This is the real community spirit =D>

jackies · 2012-06-01

R van den Boom wrote:

Did some additional searching and it seems this is something you can fix in the stylesheet.

This only works in Navision 5.0 SP1, from build 27191.

In NAV :
• Open Form 690 (Manage Style Sheets).
• Select the stylesheet for exporting to Excel.
• Functions, export.
• Open the file in Notepad or visual studio
• Modify the following in the Style sheet:
<xsl:when test='@subtype != 'number'''> replace by <xsl:when test='@datatype != 'Decimal''>
<xsl:when test='@subtype = 'number'''> replace by <xsl:when test='@datatype = 'Decimal''>
PLEASE NOTE the capitals..
• Save the Style Sheet.
• Delete the existing Stylesheet in Navision
• Functions, Import.
• Select the newly imported stylesheet in the model
• Send To program: EXCEL.EXE

This did the trick for me, no more numbers stored as text now in Office2007

I can't get this to work

Decimal with caps or not, I get the same numbers in text format. I have noticed that Aged Accounts receivable report which has export to excel works fine. It's only the stylesheets...

Howdy, Stranger!

Quick Links

Categories

Export to Excel in NAV5.0SP1 - Number stored as text

Comments