AutoCalc Excel with DBRWs and ActiveForm hangs upon opening

[bunchukokoy]

Hi Experts,

Needing your suggestions. I think that some of you have already encountered this many times.

I have an Excel report, this Excel file has like 10 tabs. Eight of which use Classic slice DBRWs to show data from cubes.
While two other tabs use Active Form to show data.
Currently I have one Macro-driven button, at the first tab, that calculates the entire workbook after selecting some filters. BUT takes around two minutes to finish.

The problem is; since all of our users have their Excels auto-calculating, when there's an already opened Excel file of TM1-unrelated, then we try to launch this Excel report containing DBRWs and ActiveForms, the Excel suddenly hangs. Never to work again. Disaster.

Sometimes, it prompts the image below.

I think there's an issue with the ActiveForm.

Is it the DBRWs or the ActiveForm that's causing the Excel to hang?

Do you ideas guys on any work-around?


Thanks very much.

bunch

[bhushpar82]

Hi,

I understand your issue, as we are in the same soup.
To address to this I have figured out two ways to approach it:
1) Keep Excel Calc Manual (I think you would have already tried this 1).. this method has some other issues.
2) As I understand, your every tab is having and extract from TM1 and thus your excel gets hanged.
Alternativeway(semi-automatic) =>
Excel 1(tm1 links): keep all your extract from individual tabs to 1 excel sheet.
Excel 2(non tm1 links): you excel with multi-tab report, refer to the last sheet where you can copypaste abv extract as values.

So whenever you have to generate report, step1-> open excel 1 press F9, open excel 2 paste as values...

Till IBM comes with seamless integration with excel..heheh

[bunchukokoy]

Thanks for the suggestions bhushpar82.

Excel 1(tm1 links): keep all your extract from individual tabs to 1 excel sheet.

I'd like to think you say use VUSLICE, which what I also think the way. My experience with VUSLICE is just, it may take maybe seconds to slice the data, but I think I'm sure it won't hang forever.

So whenever you have to generate report, step1-> open excel 1 press F9, open excel 2 paste as values...

Unlike if there's a ready ActiveForm (particularly) and DBRW cells, my experience is, the moment the Excel connects to TM1 or calculates, then it starts hanging. So I think, for me, once you have ActiveForm in Excel, to avoid hanging and some blank error message box, if it's automation that you do using Macro, I'd prefer to use VUSLICE.

Am I right with this idea guys ?


Plus another thing, ActiveForm, I believe, doesn't delete entire rows below TM1RPTROW after every F9 or Alt+F9.


Thanks.

[garry cook]

Can't you just use a Workbook_Open event to check calc settings, flip to manual then set back to original setting on Workbook_Close then use a recalc switch on each of the sheets that only calculates when this is flipped to 1 (IF(switch=1, DBR...) then get your VBA to flip the switch, rebuild the sheets then flip it back again?

Would seem the simplest and most obvious method unless I've misunderstood your requirements.

[AmbPin]

When you press OK lots of times does the TM1 ribbon start to dismantle itself?
I have seen this on some users machines but think we have mostly fix packed our way out of it.
You could try disabling the TM1 ribbon in Excel to see if the problem goes away.

[tomok]

If you have an Excel workbook with more than one active form tab in it you should not have auto-recalc on AND you should probably never use F9 to recalculate the entire workbook. That many tabs with TM1 formulas in one workbook is probably a bad idea too. Best practice would be to split these up into separate workbooks and add some navigation buttons to open the various sheets as needed.

[bunchukokoy]

Thank you guys and tomok for the idea.