Essbase Add-in VBA programming question

[pickytweety]

Outside of VBA, if I'm in Excel, I can click on a check box called "Retain on
Retrieval" under:
Essbase Options,
Mode tab,
Formula preservation title.

I need to find example code for doing that in VBA. When I do a retrieve in
the macro, it blows away some formulas. I need those formulas to stay put,
but I don't know the syntax of the code. Here are some examples of code I'm
currently using:

Declare Function EssVConnect Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant,
ByVal username As Variant, ByVal password As Variant, ByVal server As
Variant, ByVal application As Variant, ByVal database As Variant) As Long
Declare Function EssVDisconnect Lib "ESSEXCLN.XLL" (ByVal sheetName As
Variant) As Long
Declare Function EssVRetrieve Lib "ESSEXCLN.XLL" (ByVal sheetName As
Variant, ByVal Range As Variant, ByVal lockFlag As Variant) As Long

x = EssVConnect(Null, "userid", "password", "ipaddress", "database",
"database")
x = EssVRetrieve(Null, Null, 1)
x = EssVDisconnect(Null)

If anyone knows of a discussion group related to Essbase Programming let me
know....I didn't see one.

 

[Jim Thomlinson]

Retain on retrieval is trick because it possibly conflicts with Suppress
Missing and Right Click. To that end here is some code...

'****Turn off retain
Call EssVSetSheetOption(Empty, 11, False)

'****Turn On Retain
If EssVGetSheetOption(Empty, 6) = True Or _
EssVGetSheetOption(Empty, 7) = True Or _
EssVGetSheetOption(Empty, 8) = True Then
Call EssVSetSheetOption(Empty, 6, False)
Call EssVSetSheetOption(Empty, 7, False)
Call EssVSetSheetOption(Empty, 8, False)
End If
If EssVGetGlobalOption(1) = True Then
Call EssVSetGlobalOption(1, False)
End If
Call EssVSetSheetOption(Empty, 11, True)
Call EssVSetSheetOption(Empty, 21, True)
Call EssVSetSheetOption(Empty, 22, True)

Check out the Help file in Essbase Help... Here is EssVSetSheetOption. You
also need EssVGetSheetOptions and EssvGetGlobalOptions

Description

EssVSetSheetOption() sets individual spreadsheet options.

Note: This function is not plural because you can set only one option at a
time.

Syntax

EssVSetSheetOption(sheetName, item, sheetOption)

ByVal sheetName As Variant
ByVal item As Variant
ByVal sheetOption As Variant

Parameters

sheetName

Text name of worksheet to operate on. SheetName is of the form
"[Book.xls]Sheet". If sheetName is Null or Empty, the active sheet is used.

item

Number indicating which option is to be set. item cannot be Null or Empty.
The following table indicates which options are set for which number and the
expected data type:

Item Option Expected Data Type and Values of sheetOption
1 Specify drill level setting Number
1 Next level
2 All levels
3 Bottom level
4 Sibling level
5 Same level
6 Same generation
7 Calc level
2 Enable Include-selection setting Boolean
3 Enable Within Selection Group setting Boolean
4 Enable Remove Unselected Group setting Boolean
5 Specify Indent setting Number
1 No indentation
2 Indent sub items
3 Indent totals
6 Enable suppress missing setting Boolean
7 Enable suppress zeros setting Boolean
8 Enable suppress underscores setting Boolean
9 Specify alias for missing text Text
10 Enable update mode setting Boolean
11 Enable Retain on Retrieval formula preservation setting Boolean
12 Enable adjust columns setting Boolean
13 Enable alias names setting Boolean
14 Specify alias names table setting Text
15 Enable template retrieve mode Boolean
16 Enable free form/Version-2.x mode Boolean
17 Eable auto sort rows setting Boolean
18 Enable use styles Boolean
19 Specify No Access label Text
21 Enable Retain on Keep Only and Remove Only formula preservation
setting Boolean
22 Enable Retain on Zooms formula preservation setting Boolean
23 Enable Formula Fill setting Boolean
24 Enable Both Member Name and Alias setting Boolean
25 Enable Repeat Member Labels setting Boolean
26 Enable Sheet Option for Query Designer setting Boolean
27 Enable Latest Time Period Boolean
28 Specify Latest Time Period Text
Note: Item 20 is not used.

sheetOption

A Boolean, Number or Text value denoting the new value of item. If
sheetOption is Null or Empty, the active sheet value for the item is used.

Return Value

Returns 0 if successful. A negative number indicates a local failure. A
return value greater than zero indicates a failure on the server.

Example

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

Sub SetSheet()
X=EssVSetSheetOption(Null, 6, FALSE)
If X=0 Then
MsgBox("#Missing values will appear. ")
Else
MsgBox("Error. #Missing option not set.")
End If
End Sub

Notes:

§ You could also use the Level Constants instead of 1-7 to set the drill
level.

§ For information on each item, see the Essbase Options dialog box.

§ To use items 21 and 22, you must enable item 11.

§ To use item 23, you must enable item 22.

§ If you enable items 11, 21, or 22, then items 6 and 7 are not available.

§ If you enable item 22, then item 4 is not available.

© 1991-2001 Hyperion Solutions Corporation. All rights reserved. Portions ©
eHelp Corporation.