Traditionally, producing dynamic worksheets from Dynamics 365 data, where the data could be refreshed outside of Dynamics, required the use of the installed Dynamics 365 for Outlook software. We explore a simpler method, using Excel and Power Queries.
Although the Dynamics 365 Outlook client had previously been listed for deprecation, this was postponed due to some of its features having no modern equivalent, such as Dynamic Worksheets. However, with the introduction of Power Queries in Excel. It is now possible to replace the Dynamic Worksheet functionality in a few simple steps without installing the Dynamics 365 Outlook client.
If you have tried to refresh a dynamic worksheet from Dynamics 365 without having the Outlook client installed, you may previously have faced an error message or even had the refresh clear all the data that was there originally. By following these steps, you can reconfigure the Excel file to refresh correctly:
- Open an Excel workbook generated by Dynamics 365
- Click the “Data” menu from the Excel ribbon, followed by “Queries & Connections”
- In the “Queries & Connections” pane that appears on the right-hand side, double-click “Query_from_Microsoft_CRM”
- On the properties window that appears, change to the “Definition” tab and click the “Edit Query” button.
- After clicking “Edit Query”, a small browser popup will appear. You may be asked to log in; if so, enter your Dynamics 365 credentials.
- You will then see the following error. This is to be expected.
- Close all of the popup by clicking the X in the upper-right corner.
- You should now have an established connection to Dynamics 365 from Excel and now be able to refresh data.
If you have an active support agreement with Strategy 365 Ltd and would like to discuss the above process, please get in touch.
