Using EssVSetSheetOption macros to turn retain on/off

[Slate]

Hi,

I am trying to write an Essbase macro that will either turn the retain options all off or on but am having trouble.

For example, the one to turn the retain formulas options off I have got:

Declare Function EssVSetSheetOption Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal item As Variant, ByVal sheetOption As Variant) As Long

then:

sub Turnoff()

X = EssVSetSheetOption(Null, 11, True)
X = EssVSetSheetOption(Null, 22, True)
X = EssVSetSheetOption(Null, 23, False)
X = EssVSetSheetOption(Null, 22, False)
X = EssVSetSheetOption(Null, 21, False)
X = EssVSetSheetOption(Null, 11, False)

End sub

However, when I try to run it is gives three error messages:

1. Suppress Zero or Missing options cannot be modified in Formula Preservation mode
2. Same again
3. The retain on and keep and remove options cannot be modified with the Both Member Name and Alias option set

I am confused because I have code above this that makes sure suppress missings is off first anyway - and I don't try and modify the Member Name and Alias setting either!

Thanks

Liam

 

[Talesind]

The problem could be that essbase already has some options selected befoe the code runs. I've had problem like this in a past when trying to use essbase options. Check what options areopen in essbase before running the code.

 

[Slate]

Thanks - but I have already tried that and I'm not sure what else to do.

Specifically I am getting the error messages when:

X = EssVSetSheetOption(Null, 11, True) - works ok


X = EssVSetSheetOption(Null, 22, True) - Suppress Zero or Missing options cannot be modified in Formula Preservation mode

X = EssVSetSheetOption(Null, 23, False) - works ok

X = EssVSetSheetOption(Null, 22, False) - Suppress Zero or Missing options cannot be modified in Formula Preservation mode

X = EssVSetSheetOption(Null, 21, False) - The retain on and keep and remove options cannot be modified with the Both Member Name and Alias option set

X = EssVSetSheetOption(Null, 11, False) - works ok


Do you have any other ideas?

 

[Talesind]

It would appear that you've got 2 conflicting options set. before the line

X = EssVSetSheetOption(Null, 21, False)

try adding

X = EssVSetSheetOption(Null, 24, False)

 

[Slate]

Worse still! I now get an additional error message on the essvsetsheetoption (Null, 24, False) code saying:

"Both member name and alias setting cannot be modified in formula preservation code"

So.. I tried putting essvsetsheetoption (Null, 11, False) above this and I still get the same error!?!

Starting to wonder if this is a bug in Essbase!

 

[Talesind]

it's not a bug as such, essbase has some options that are mutually exclusive, normally it won't let you choose them, but since you're working in code that bypasses the normal functions.

It might be an idea to manually select all of the options you want from essbase and find where there are conflicts and decide which option you need the most :/

Once that's out of the way just change your code options to suit.

Here's a full option list if you've not already got one

http://www.pcreview.co.uk/forums/thread-2311105.php

 

[Slate]

Thanks. I'll have a look.