Skip to Content
0

EPM workbook with 60+ sheets got stuck while refresh

Apr 14 at 10:48 AM

81

avatar image

Hello Experts,

I've made a workbook with set of 130 sheets (60 sheets with reports and almost 70 formatting sheets). It's a reports package for printing. Yes, it's a huge beast:)

Now I'm faced with an issue while refreshing a whole book or single sheet. When I press the Refresh button on EPM ribbon, the system initializes refreshing process and shows a status window in the center of the screen: "Process in progress. Please wait". Then the Refresh process gets stuck until I make a mouse click anywhere on excel sheet. If I don't click Excel can go dump.

I switched formula calculation from automatic to manual mode in Excel options and refresh workbook again. System works fine and doesn't have any pauses and freezes while refreshing. Do you have any idea what could be a reason and how to avoid it?

Thank you

Excel 32-bit

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Vadim Kalinin Apr 17 at 07:23 AM
0

In general it's easy to create a complex report that will be extremely slow :)

You can try to refresh sheet by sheet using VBA code...

Show 2 Share
10 |10000 characters needed characters left characters exceeded

Hi Vadim,

thank you for answer. I wrote a small VBA code to switch calculation from automatic mode to manual before refresh and than switch back to automatic mode after refresh. Maybe it's not elegant solution but it works without any delays.

Thanks again,

Alex.

0
Alexander Keshishian

Then please accept the correct answer!

0
Graham Cunningham
Apr 16 at 04:17 PM
0

Hello Alexander,

This appears similar to the issue documented in KBA https://launchpad.support.sap.com/#/notes/2419757 .

Make sure your Office is updated to the latest patch. If you are still encountering issues then you may want to check in Task Manager to see how much memory the MS Excel instance is using.


Regards,

Graham

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi Graham,

thank you for your response and link. It's not the same issue, but anyway thank you.

MS Excel instance is using approximately 1Gb of memory while updating.

I've deleted almost every formatting sheets and trying to compile all formatting settings on two sheets. It make sense cause it has improved productivity. But I can't account for all the settings on two sheets. Too complex workbook.

Thanks.

0