Start a new topic

EPM Add-In: Excel frozen, unresponsive ribbon, frozen data, locked sheet

Symptom:

Certain users report the following issues with Microsoft Office Excel 2013 or 2016:

- Microsoft Excel menus and/or sub-menus are unresponsive.
- The EPM tab and the Excel Ribbon freeze or are unresponsive.
- The data is not up-to-date after a refresh and the worksheet seems to be locked.
- VBA macros containing multiple occurrences of Copy/Paste Special or simple Copy features causes Excel to be unresponsive.
- Microsoft Excel and other Office applications are frozen or unresponsive after a refresh or a macro execution.
- EPM reports that contain EPMCopyRange functions causes Excel to be unresponsive.

None of these issues have been reproduced with Microsoft Office Excel 2010.

Other Terms

EPM add-in 10.0 SP28, FC (for Financial Consolidation), BPC (for Planning and Consolidation), SAP BW (INA Provider)

Reason and Prerequisites

The issues occur after installing or upgrading Microsoft Office to a 2013 or 2016 version.

The issues can be reproduced after an EPM refresh or the use of custom VBA macros with a Range.Copy function.



Solution 1:

Can be applied with any version of the EPM Add-in (or SAP BusinessObjects Analysis, edition for Microsoft Office).

Microsoft has officially delivered a solution to work around the issue in Microsoft Excel.

Please read this page to find out more about the issue and how to fix it:
https://support.microsoft.com/en-us/help/2817672/macro-takes-longer-than-expected-to-execute-many-individual-copy-and-paste-operations-in-excel-2010-and-later

If you have already implemented one of the former solutions below, please remove the keys below from your configuration file if they exist:

- UseCopySpecialPasteForFormatting
- UseExcelCopyForValuesAndFormulas
UseDoEventsOnExcelPasteSpecial
UseDoEventsOnExcelPasteSpecialTimerMilliseconds
UseDoEventsOnExcelPasteSpecialEnableTraces
UseDoEventsOnSpecialPasteForFormatting
ForceDoEventsInRangeCopyLoops

Note: this solution replaces all the other solutions listed below. 
Use one of the solution below only if solution 1 does not function on your environment.

 

Solution 2:

Install the EPM add-in SP28 (or SAP BusinessObjects Analysis, edition for Microsoft Office 2.4 SP02) and set the configuration keys listed below to "false" in the [FPMXLClient.dll.config] file located in your installation folder:

    <add key="UseCopySpecialPasteForFormatting" value ="false"/>
    <add key="UseExcelCopyForValuesAndFormulas" value ="false"/>
    <add key="UseDoEventsOnExcelPasteSpecial" value ="false"/>

Note: this solution does not use the Microsoft Excel copy/paste functions to apply the formatting but doing so has some formatting limitations.
Read the following SAP notes about the workaround formatting limitations:

    2319981 - ALL: EPMCopyRange can freeze application or Ribbon
    2311160 - ALL: Refresh freezes application activities until completed

 

Solution 3:

Install the EPM Add-in SP28 (or SAP BusinessObjects Analysis, edition for Microsoft Office 2.4 SP02) and set the below configuration keys settings in the [FPMXLClient.dll.config] file located in your installation folder:

    <add key="UseCopySpecialPasteForFormatting" value ="true"/>
    <add key="UseExcelCopyForValuesAndFormulas" value ="true"/>
    <add key="UseDoEventsOnExcelPasteSpecial" value ="true"/>

Note: this solution uses the Microsoft Excel copy/paste functions and triggers an additional DoEvents function to avoid the freezing effect but doing so has some performance limitations.

Each time a refresh exceeds the value entered in the key below (4000ms by default), the DoEvents is triggered.
    <add key="UseDoEventsOnExcelPasteSpecialTimerMilliseconds" value ="4000"/>
You can change this interval value to minimize the effect on performances. 
Microsoft recommends not to exceed 5 seconds.
We recommend that you keep this value between 500 (500ms) and 5000 (5s), keeping in mind that the higher the value is, the lower is the impact on performances.

 

Solution 4 - Custom VBA workaround:

Each time you use a Range.Copy function in your custom vba macros, call the DoEvents function after the Paste function like in the samples below:

Private Sub WorkaroundSample()

    Dim SourceRange As Range
    Dim DestinationRange As Range

    ' Range.Copy(Destination)
    For i = 0 To 10
        SourceRange.Copy (DestinationRange)

        DoEvents
    End


    ' Range.Copy & PasteSpecial
    For i = 0 To 10
        SourceRange.Copy
        DestinationRange.PasteSpecial

        DoEvents
    End

End Sub


Login or Signup to post a comment