Excel hangs while publishing/uploading data using the Excel Add-in for Dynamics AX

In a scenario, while trying to publish data/records using Excel add-in for Dynamics AX, issues like performance, hang/not responding, time out have been observed.

For Example, once clicked on ‘Publish All’ or ‘Publish Selected’ Excel goes to ‘not responding’ stage. This behavior gives an impression that the Publish action was not successful or it is abruptly hung.

Cause:

Behind the scenes, Excel is designed to automatically perform the ‘Refresh all’ action after completing ‘Publish All’ or ‘Publish Selected’ task.

Ideally, once the data from Excel is published, it is to amend that data in the database and then the Excel spins off a refresh action which fetches all the records (old and new) on Excel for the User’s visibility.

The larger the amount of data, the longer it takes for Excel to come back to life.

It is the ‘Refresh’ action which consumes additional time and resources. This leads to performance, Excel not responding/hang, time-out issues on Excel.

Resolution:

Shorten the time consumed by the Auto-Refresh action in Excel add-in. In order to do this:

• Before doing the ‘Publish All’ or ‘Publish Selected’ task, use the built-in Filter functionality on Excel add-in.

• Add a random filter criteria for any field e.g. ‘ZYX’ for ‘Journal batch number’ field. When Excel does the Refresh post publishing, this filter criteria will not return any results, as the value ZYX does not exists for this field in the database.

Hence in an environment where there is lot of data, it will certainly make sense to add a random filter (as in the above screen capture) as it will reduce the refresh time and Excel will still be active.