Photo credit: Teddy Cruz

New functionality in Dynamics NAV 5 allows you to export the data from any form in the application to Microsoft Word or Excel by pressing the Send To… buttons on the toolbar.

However, try running the G/L Account List (18) form for example and then clicking Send to Microsoft Office Excel and you’ll receive the following error:

What causes the error? It’s the forward slash in the object name, or to be more exact, in the Caption/CaptionML porperty.

For list type forms, Dynamics NAV passes the forms caption as the Worksheet name and as Excel doesn’t allow forward slashes in Worksheet names, the error is triggered.

For tabular type forms, the caption of each page in the tab is passed as the Worksheet names, so if you happen to have a PageName which contains a forward slash the error will once again be triggered.

These other characters will also cause problems for Excel: \ ? ‘ : *

The workaround until Microsoft hotfixes the executable is to modify the Caption property of the affected Form or PageName.

Thanks to Jon for spotting this one.

Update

After a little further investigation I realised that it would be possible to intercept the XML stream before it reaches Excel, remove any offending characters and therefore prevent the error message from occuring without having to hack the caption. 

Here’s the fix:

Open Codeunit 403 Application Launch Management and copy the GetAttributeValuefunction then rename it RemoveInvalidCharacters.

Change the function so it has no return value.

Keep the first three lines of code intact but remove the EXIT statement:

Insert the following code with local variables as appropriate:

Place the call to the new function in the LaunchApp function. Anywhere should be ok as long as it is positioned before the call to TransformDocument:

All forms with offending characters in the caption such as the G/L Account List will now work correctly.

Got any tabular forms with invalid characters in the PageNames? That would require an additional fix. Any ideas?…