SAP Knowledge Base Article - Public

1271035 - Error: 'Excel encountered an error and had to remove some formatting...' when opening a report exported to MS Excel from Crystal Reports

Symptom

  • Error when opening a document in MS Excel.
  • Report exported from Crystal Reports to MS Excel generates an error when open in MS Excel. 
  • When opening a report exported to MS Excel format from Crystal Reports, it generates the error:
        
     'Excel encountered an error and had to remove some formatting to avoid corrupting the workbook. Please re-check your formatting carefully.'

Environment

  • SAP Crystal Reports 2008
  • SAP Crystal Reports 2011
  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016

Reproducing the Issue

  1. In Crystal Reports, export a report to MS Excel Format.
  2. Open the exported report in MS Excel.
  3. The error displays: 'Excel encountered an error and had to remove some formatting to avoid corrupting the workbook.'

Cause

  • An object on the report doesn't have a font associated with it. ( Text object, Database Fields, Formula Fields, ... )
  • When opening the exported report in MS Excel, the application displays an error since there is no associated font with the object

Resolution

  • Ensure all objects on the report have a font associated with it. To do so, follow the steps below:
    1. Open the report in Crystal Reports
    2. In the Design view of the report, right click on an object, and select "Format ..." in the contextual menu
    3. In the "Format Editor" window, under the tab "Font", verify there is a font name
    4. If there is no font name and it is blank, select a font, and click "OK" to accept the change.
    5. Repeat step 1 to 4 for each object ( Text objects, Database Fields, Formula Fields,... )
    6. Export the report to MS Excel format

           Now, when opening the report in MS Excel, it will not longer display an error.

Keywords

MS Excel, Crystal Reports, CR, Excel, Export , KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , Problem

Product

Crystal Reports 2008 V0 ; Crystal Reports 2008 V1 ; SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016