emeadaxsupport: Solving various issues when using Export to Excel feature in Chart of Accounts Form (Microsoft Dynamics AX 2009)

[Blog bot]

Источник: http://blogs.msdn.com/b/emeadaxsuppo...s-ax-2009.aspx
==============

We came across a series of issues sometime back with regards to using the Export to Excel feature where we are using Microsoft Dynamics AX 2009 client and Microsoft Excel 2007:

A user is in the "chart of accounts" form. They click on the "transactions" button and the transactions form opens up. Now they want to export these transactions to excel so that they can work with the data, however we faced 3 issues as follows:

1. When we try to export we get this error message:
Method 'pasteSpecial' in COM object of class '_Worksheet' returned error code 0x800A03EC () which means: PasteSpecial method of Worksheet class failed.


2. Once we get past the above error we found that there were duplicate transactions in the exported data.

3. Also, furthermore, the amount column does not check whether the value is a debit or a credit, so the total summed amount is incorrect.


The following is how we resolved the above 3 issues:

1. The root problem of this issue is that when pasteSpecial is called there is nothing on the clipboard to paste! There are a few possible reasons for it:

This message can occur because when Excel opens for the first time a switch is made from the windows clipboard to the office clipboard. The office clipboard is what allows extra copy/paste functionality in Office, such as copying multiple separate items at once and storing them on the clipboard, with the windows clipboard we can only copy one thing at a time.

With some Excel add-ins the switch from Windows to Office clipboard cause the contents of the clipboard to be lost, so the first thing to try is disabling all Excel addins.

Also some remote access products, such as Microsoft SharedView, add functionality to the Windows clipboard and can cause the contents to be lost when switching from Windows to Office clipboard. If you're using SharedView and you have this problem, then first try disabling remote clipboard in SharedView options, if you still have a problem then uninstall SharedView and retest it.


2. There is a kernel hotfix available, KB970446, which resolves the second issue. It's resolved from kernel build 5.0.1500.649 onwards (and hence is is also included in AX 2009 Rollup Fix 2 and above).


3. We found there were at least two ways to resolve the debit/credit issue:
a) Change the extended data types (EDT for short) AmountMSTDebCred and AmountCurDebCred property "SignDisplay" to "auto". This will change the way the amount field is shown in all forms, instead of the left/right alignment showing whether it is a debit or credit it will have a +/- sign instead. Then when exporting to excel the sign will show appropriately.


b) The above workaround will not work if their is negative credits or positive debits, some amounts will end up with "--" or "-+" which cannot be correctly interpreted by Excel. Of course we shouldn't have these, but in real life sometimes we do, so to get past this we can add some logic to the ledger transactions form (Forms\LedgerTrans).

Attached is a zip file which contains an XPO of the Ledger Transactions form. I have added a new column to the overview page which will show the amount correctly. This takes the natural sign of the values, so credits are negative and debits are positive, but in addition it will reverse the sign on the value if it finds a credit which is more than 0 or a debit which is less than zero. To illustrate:

Crediting=Yes and amount>0 = flip the sign over
Crediting=No and amount