In Dynamics 365 Finance and Operations I am using the option Open in Microsoft Office → Excel on the Purchase Requisition page.
The Excel file opens correctly and I can edit some data and publish it back to the system. However, some fields I need do not appear in the spreadsheet, for example Vendor and Line number.
I would also like to understand how to standardize a field so that it appears for all users, and not only when I manually add it using the Design option in Excel.
My questions are:
Can I use any Data Entity that is public in the environment inside the Excel Add-in? Does this depend only on user permissions or are there other limitations?
How can I create an Excel template from scratch without relying on the default button from the page?
The idea would be to build a spreadsheet with the required columns and connect it to Dynamics to read and update data.
If a field already exists in the Data Entity, how can I include it in the template and make it available to all users, not only for me through the Design option in Excel?
If the field is not available in the Data Entity, is there any way to include it through system configuration only, or is it necessary to extend the Data Entity through development?
I would also like to understand what the best practice is to expose additional fields in Excel for editing data in Dynamics 365 Finance and Operations.
The key thing you seem to be unaware of is Common > Common > Office integration > Document templates. You can modify a spreadsheet (e.g. by adding data sources and fields, change design etc.) and create a template from it, which will be available to other users as well.
And yes, you can use any public entity and entity permissions do apply.
Be aware also this hidden parameter ‘Integration mode’:
System administration/Security/Security configuration/Privileges tab
Select a privilege and click ‘Entities’ under References
Select an entity under Entities and Integration mode parameter appears on the right-hand side. In my understanding you have to select ‘Data services’ or ‘All’ for Excel add-in.
In Microsoft Dynamics 365 Finance & Operations, the Open in Microsoft Office → Excel feature works through data entities and the Excel add-in, so what appears in Excel depends on the entity configuration.
You can use any public data entity with Excel as long as it is exposed through OData and the user has the correct security permissions. Normally, the entity shows in the “Open in Excel” menu only if it shares the same root data source as the form.
If you want to create a template from scratch, use Excel Workbook Designer (Office integration). Select the data entity, choose the fields, and generate the workbook. The Excel add-in will connect the sheet to the entity so users can refresh and publish data.
To make the template available for all users, upload the workbook under Office integration → Document templates.
If some fields are missing, they must be exposed or added in the data entity (often through an extension) before they can be used in Excel.