Exporting Dynamic Worksheets to Excel in Dynamics CRM 2011

Roshan Mehta, 05 January 2012

Microsoft Dynamics CRM 2011 provides useful tools for reporting and data analysis. In this post we will take a look at the Export to Excel feature and focus on the ability to export “dynamic worksheets”. When a dynamic worksheet is exported from Microsoft Dynamics CRM 2011, the resulting export file maintains a link back to CRM. This means that the worksheet will always contain the most up-to-date data.

When you choose to export a dynamic worksheet from CRM, the user is presented with the following export options.

Unlike a static worksheet, the user can select the columns to include in the dynamic worksheet. Clicking on Edit Columns displays the column selector window which you may be familiar with when configuring Advanced Find views.

Here the user can add/remove columns, reposition columns, and change the width of columns. When the data is exported to excel, Microsoft Excel will maintain the column configuration as part of the worksheet.

When a user saves a dynamic worksheet on their desktop, the data is automatically refreshed in the worksheet every time the export file is opened provided the user’s computer has an active connection back to CRM. Alternatively, the user can click on the Data tab in Microsoft Excel to force refresh the data.

For this reason, the option to Make this data available for re-importing by including required column headings is greyed out for dynamic worksheets. If I export a list of Contacts to update their address details, the updated data will be lost the next time I open up the exported file since CRM will automatically refresh the dynamic worksheet.