EPM workbook with 60+ sheets got stuck while refresh

[former_member539392]

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

[former_member186338 · Accepted solution]

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...

[former_member539392 · Accepted solution]

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.

[former_member186338 · Accepted solution]

Then please accept the correct answer!

[former_member260594]

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

[former_member539392]

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.