How does the Export to Excel feature work under the hood?
One of the nice features of Dynamics AX 2009 is the Export to Excel that is available throughout the product and allows to content of a Grid to be exported to Microsoft Office Excel for further analysis. Sometimes however the feature does not work as expected. So for example Microsoft Office Excel is starting but the data from the Grid is not showing up.
The Export to Excel is not completely happening in the Dynamics AX Kernel but parts of the export logic are coded in X++:
- If you click at the button Export to Excel in any Form, the Dynamics AX Kernel is generating the list of data to be exported. The Dynamics AX Kernel takes here the formatted data as it is visible in the Grid.
- Next the Dynamics AX Kernel places this data in the Windows Clipboard, while the currently existing content of the Windows Clipboard is backed up.
- Then the Dynamics AX Kernel calls the X++ method performPushAndFormatting of the class SysGridExportToExcel. This method is instantiating Microsoft Office Excel using COM, pasting the data from the Windows Clipboard and applies some formatting to the inserted data.
- When the X++ part is finished the Dynamics AX Kernel places the original backed up content of the Windows Clipboard back.
The Windows Clipboard is playing a central role in the Export to Excel. Between the placing of the data in the Windows Clipboard and the retrieving of the data by Microsoft Office Excel several steps are happening. If for any reason the content of the Windows Clipboard is altered or replaced during this time the export will fail.
One cause of problems can be Add-Ins for Microsoft Office Excel. Add-Ins are loaded when Microsoft Office Excel starts and can manipulate the content of the Windows Clipboard before Dynamics AX had any chance to paste the content at a worksheet.
In order to find out what is happening during the export of data the Dynamics AX Debugger can be of a great help. Simply by putting a breakpoint at the beginning of the method performPushAndFormatting and checking the content of the Windows Clipboard while executing line after line.
