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.
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.
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.
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.
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
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)
' Range.Copy & PasteSpecial For i = 0 To 10 SourceRange.Copy DestinationRange.PasteSpecial