Function TM1RECALC unloads XL UserForm

[Guillaume Galtier]

Hello all,

My XL report is a "Browse in Excel" TM1 view. I've added some VBA code to improve my report. I've made an XL UserForm to display additional informations and to perform some changes that can affect the view result.
My form is modeless, and I want it, to be always displayed, until I choose to close it.
When I perform some data modification through my form, I use the function TM1RECALC1 to recalculate my view and so, display the changes. The recalculation is Ok, but systematically, once the code is finished, my form is unloaded.

It seems that all opened forms are unloaded after performing the TM1RECALC1 (same with TM1RECALC, or hitting F9 or Shift+F9).

Has somebody already faced this problem ?

Don't know how to let my form active after the TM1RECALC... Any idea, please ?

Thanks in advance

Guillaume

[David Usherwood]

Guillaume, if you have done a report using 'Browse in Excel' you must be using the In Spreadsheet Browser. This is quite a venerable piece of code, and Cognos have said they plan to discontinue it. That said, it has its moments and I do use it myself from time to time. However I _never_ heard that it was programmable. I know that the formatting is quite limited and that if you change the formats in Excel they are overwritten on a refresh.

You will have better luck 'slicing' your data from Excel. This delivers a live view of your cube and you can write all the code you want around the slice. However you can't drill or pivot, which may be what you were after.

HTH

[Guillaume Galtier]

David,

first of all, thanks for your reply.

In fact, I'm trying to develop an administration tool, more than a report. And this tool will alow to add/remove elements into a dimension.
It's the first reason why I have chosen the "In spreadsheet browser": just to see in real time my changes into the structure of the dimension.
The second reason is effectively because drill and pivot could be helpfull for the tool...

Guillaume

[ScottW]

I've just done the experiment with a modeless Form in Excel and tried F9 and Application.Run "TM1RECALC" and the Form did not disappear (with sliced report and ISB). Where is your Form? Is it built from the workbook with the ISB or in a separate xla? I suspect that if you move the Form to a separate xla the problem might disappear. As has been noted IBM have issued an end of life statement for the ISB, alas it dies with v9.4

[Guillaume Galtier]

My form is in the same workbook that the ISB.

It seems that this problem occurs only when the ISB option "Update View on Recalc" is checked.

In fact, I have used the ISB, because it seems to be the most efficient solution to display dynamic views in Excel. But if the ISB disappears with 9.4, perhaps it's not a good idea to use it... (we are still in 9.0 sp3, but not for a long time).

I have tried dynamic slices, using the function "TM1Refresh" to update the excel view, but the refresh of the view is longer than for the ISB...

Which technology do you advise me?

Guillaume

[ScottW]

If you want to show dimension changes why not use TM macro functions SUBPICK (or VUSLICE if you want to show values from an }ElementAttributes cube).

Or you could use MDX to return an element set.

Both should be fast but require VBA coding (which by the sounds of it I assume shouldn't be a problem for you.)

[Guillaume Galtier]

You're right Scott, VBA codinf isn't a problem for me

"However I _never_ heard that it was programmable"

David, i just have found how to control ISB via VBA coding.
If somenone is interested, I put here a little piece of code that reproduce the "Clear display" of the ISB:

Code: Select all

Sub ISB_Clear()
    
    Dim vwTm1 As TM1XlCubeView_1_3.View
    
    '*Set ISB
    Set vwTm1 = ActiveSheet.View1
    
    '*Clear
    vwTm1.ClearDisplay
    
    Set vwTm1 = Nothing

End Sub

Guillaume

[David Usherwood]

That's interesting (and I stand corrected). The risk, I suspect, is that if the objects/methods/properties you have unearthed are not 'official', then Applix/Cognos/IBM can change them when they please. And if they drop the feature altogether, I think that qualifies as 'changing them when they please' .