EssVGetSheetOption

Description

Returns information about individual Essbase spreadsheet options.

Syntax

EssVGetSheetOption(sheetName, item)
ByVal sheetName As Variant
ByVal item 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 worksheet is used.

item

Number indicating which option is to be retrieved. item cannot be Null or Empty. Table 10 indicates which options are returned for which number.

Note:

Items 20 and 31 are not used.

Table 10. Item Number Options, Return Types, and Values

ItemOptionReturn Data Type and Values

1

Set drill level setting

1 Next level

2 All levels

3 Bottom level

4 Sibling level

5 Same level

6 Same generation

7 Calc level

Number

2

Enable Include Selection setting

Boolean

3

Enable Within Selection Group setting

Boolean

4

Enable Remove Unselected Groups setting

Boolean

5

Specify Indent setting

1 No indentation

2 Indent sub items

3 Indent totals

Number

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 (block storage databases only)

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

Enable auto sort rows setting

Boolean

18

Enable use styles setting

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 member names and alias setting

Boolean

25

Enable repeat member labels setting

Boolean

26

Enable sheet options for Query Designer setting

Boolean

27

Enable Latest Time Period setting

Boolean

28

Specify Latest Time Period

Text

29

Enable Hybrid Analysis

Boolean

30

Enables metadata sampling when performing a Zoom In operation

Boolean

32

Indicates the percentage of the members to retrieve when metadata sampling is enabled

Number

33

Enables display of the qualified name of the duplicate (non-unique) member in the Excel comment box

Boolean

34

Enables display of the qualified name of the duplicate member in the Excel spreadsheet

Boolean

Return Value

Returns the value of the current setting as a string, number, or Boolean. If an error occurs, #VALUE! is returned. When item is out of range, #NUM! is returned.

Example

Declare Function EssVGetSheetOption Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal item As Variant) As Variant

Sub GetSheet()
Dim X As String
X=EssVGetSheetOption("Sheet1", 6)
If X="#NUM!" Then
MsgBox("Invalid item ID specified.")
Else
If X="#VALUE!" Then
MsgBox("Error. Option could not be found.")
Else
MsgBox("Suppress Missing is set to " + X)
End If
End If
End Sub