The New Microsoft Dynamics AX (formerly referred to as AX 7) has a better integration with Microsoft Excel. It allows you to export to Excel, as with previous versions, but it also lets you write back changes from Excel to Dynamics AX much easier than in the past. The New AX has also expanded the use of entities to make it easier to import and export data.
In any list page in AX, when you click on the Office icon it will give you two options. The option in RED, will export to Excel so you send to another person. The option in Green, to open in Excel, will allow you to edit the data in excel and push it back to AX. Those that have used Dynamics AX 2012 know about the excel add-in, that was supposed to give you this functionality as well, but was very finicky to use. The integration with the New AX has been improved significantly.
Once you click the open in excel button, a screen pops up to let you save locally, or to SharePoint or OneDrive for business.
After downloading or saving the file, open it and enable edit mode. It will then prompt you sign in, simply use your AX login credentials. Once the credentials are provided it will load the data into Excel. From here you can edit any of the data, that is not read only. Read only fields are noted in red. A key symbol is used to identify key fields. Finally, an asterisk is used to identify required fields for new records. Once you’ve made the necessary changes, simply hit publish and the changes are written back to excel. You can even use Excel to create new records.
In addition to the open in excel function from all to the list view pages, you can also access the Excel workbook designer. This is located in the Common module, under Common>Common>Office Integration>Excel Workbook Designer. The workbook designer gives you access to over 1600 entities that come standard in the New AX. If you can’t find an entity that meets your needs, you can build your own by using visual studios. The use of the workbook designer does require you to have Excel 2016.
Using the workbook designer, you can add all the fields you want from the entity, you can view the related form in AX, and you can get a count of all the records associated with the data entity.
Once you’ve selected the fields you want for the workbook, click “create workbook” and it will prompt you to save the workbook. Then you can open, and start editing data or creating new records.
This post was written by Jason Federspiel, AX Functional Consultant at Tridea Partners. Tridea is a leading Microsoft Dynamics provider.