2076788 - Workbook takes several minutes to open or crash when Analysis for Office is activated

You try to open a workbook in Excel with Analysis for Office activated. You are experiencing one of the symptoms below:

• The workbook takes several minutes to open (it is slow);
• Analysis for Office crashes and the workbook doesn't open.

The workbook works fine when Analysis for Office is disabled.

SAP Analysis for Microsoft Office

The root cause of the issue may be related to the number of styles in the workbook. Depending on the number of custom styles in the workbook the performance of Analysis for Office decreases, because AO has to check the styles in order to apply the AO-styles if applicable.

This issue is also explained in SAP note below:
>> 1899658 - Too many styles slow down or crash Analysis Office

How to check if this is your case

1. Open the workbook (wait until it is opened);

2. Click on “Home” tab in Excel;

3. Click on the “More” button in the “Styles” ribbon;
   
   
4. The drop down with the styles of the workbook will open;
   
   

The issue in this case is not solved by upgrading the Analysis for Office.

In order to solve this issue it is necessary to remove the huge number of custom styles in the affected workbook.

This can be done manually, via macro (custom code) or by using some tools available in the internet.

The example below shows how to remove the custom styles by using a tool also available in Microsoft Store.

For Windows 7:

1. Visit the link: https://onedrive.live.com/?cid=53e1d37f76f69444&id=53E1D37F76F69444%21900&ithint=file,.zip&authkey=!AH5oeGVaWlMsFHA
2. Download the "XLCleanerDotNET4.0";
   
   
3. Extract the file;

4. Run the file “XLStylesTool.exe”;
   
   

5. Click on “Get Excel File” and choose your workbook;
   
   
6. Select the option “Force all cell to Normal” and click on “Process File” to check the results and confirm that the styles will be removed:
   
   

7. Make sure to select also the option “Commit changes and open in Excel”, then click on “Process File” again;
   
   

8. The workbook will be opened in MS Excel and you can confirm that the custom styles have been removed;

9. Save the workbook.

For Windows 8 or higher:

1. Download XLStyles Tool from Microsoft >> https://www.microsoft.com/en-US/store/p/xlstylestool/9wzdncrfjptg
   
   
2. Start the XLStyles Tool;
   
   
3. Click on "Get File";
   
   
4. Select the workbook;
   
   
5. Make sure the option "Force all Styles to 'Normal'" is selected and click on "Process File";
   
   

1899658 - Too many styles slow down or crash Analysis Office

slow, performance, crash, excel, long, time, Analysis, Office, launch, activate, activating, styles, InitailziedSapStyleSets, InitializedSapStyleSets , KBA , BI-RA-AO-XLA , Excel Addin , BI-RA-AO-BIP , AO Precalculation, Scheduling and AO InfoOb... , How To