Annoying file format warning when exporting CRM records to Excel
When you export from CRM to Excel the data is derived as XML, saved with an XLS file extension and Excel is invoked to open the temporary file. Unfortunately Excel checks to see if the file being opened is actually of a type which matches the file extension and tries to be helpful. Normally this is to help overcome problems such as a comma-separated variable (CSV) file being saved as an XLS file extension, which ought to mean Excel tries to read the XLS file, fails because the contents are nothing like a real Excel binary file and gives up. Instead, Excel actually looks at the content, spots that it looks very much like a CSV and allows you to open it just as if the file extension was correct in the first place. However, this cleverness is tempered somewhat by the fact that the default setting for this is to ask the user every single time what they want to do.
As always, this is probably intended to be a helpful warning and prevent people opening files which might have insecure content, but it fails to do so because most users do not understand the implications and the longwinded message is probably not even read properly anyway. Certainly the 50th time someone sees a dialog like the one below, they just click “yes” without reading and it no longer provides any benefit whatsoever (by the way, I have done nothing to this, it displays in this ridiculously wide, un-resizable window on my machine).
Whenever I have managed people in IT support roles I try to eliminate fixes which involve things like “ignore that error message, just hit OK and it will work fine”. This not only numbs people to the meaning of that particular error message but to these sorts of warnings in general. Too often I have heard users explain why they did not report a problem until it was too late, saying “well, I got an error every day saying something about faulty disk or something but I just clicked OK, like John said we should with that other one…”. Find the root cause, eliminate the error, or suppress the error somehow, don’t teach people that errors don’t matter or they just ignore them. If you went to your doctor and said “it hurts my neck when I lift my arm up” you would not be impressed if she replied “then don’t lift your arms up!”, would you?
Stop Excel asking unhelpful questions when you export records from CRM
Luckily there is a really easy way to control this behaviour across your whole network using Group Policy. You will need to download the appropriate admin files for Office 2007 or Office 2010 and add them to your Group Policy in the usual way* (either to a new policy or an existing one you might already have for applying other Office policy settings). Go to:
User configuration\Administrative templates\Microsoft Excel 2007 (or 2010)\Excel options\Security
Look for the policy setting for “Force file extension to match file type”, enable it and choose one of the three options:
- Allow different (this is the one you need to stop the warning when exporting from CRM)
- Allow different, but warn (this is the default behaviour)
- Always match file type (this stops the warning but prevents mismatched files from being opened)
This is the full explanation text which you can see in that policy setting, or in the spreadsheet of available Group Policy settings for Office:
This policy setting controls how Excel 2007 loads file types that do not match their extension. Excel 2007 can load files with extensions that do not match the files’ type. For example, if a comma-separated values (CSV) file named example.csv is renamed example.xls, Excel can properly load it as a CSV file.
If you enable this policy setting, you can choose from three options for working with files that have non-matching extensions:
- Allow different – Excel 2007 opens the files properly without warning users that the files have non-matching extensions. If users subsequently edit and save the files, Excel preserves both the true, underlying file format and the incorrect file extension.
- Allow different, but warn – Excel opens the files properly, but warns users about the file type mismatch. This option is the default configuration in Excel.
- Always match file type – Excel does not open any files that have non-matching extensions.
If you disable or do not configure this policy setting, if users attempt to open files with the wrong extension, Excel opens the file and displays a warning that the file type is not what Excel expected.
*Adding group policy ADM files to GPMC
Just in case you don’t know how to do this, here’s the quick version:
Download and unzip the ADM files you need to use, and remember where they are. Open Group Policy management Console (GPMC), find the policy you want to change, right click > Edit.
Navigate to Computer or User Configuration as necessary, then right click “Administrative templates” and choose “Add/remove templates”. Click the Add button and navigate to where you saved them. Select the policy template you need (Excel12.adm or Excel14.adm in this case) and click Open – you can use Ctrl-click to select multiple template files before clicking Open, or just double click a file if you only need one. Click Close.
Double click Administrative Templates to expand that branch, and look for your new template as a “folder” – Microsoft Excel 2007 or 2010 as appropriate.
Tagged: Dynamics; CRM; Excel; export; file format warning
Written by Adam Vero on July 1st, 2010 with no comments.
Read more articles on Dynamics CRM and Dynamics; CRM; Excel; export; file format warning and office 2010 and Excel and otherSoftware and Group Policy.
