Setting an Essbase Option using code

Hopefully someone can answer this question

I have declatred some functions in a module and one of these statements is

Declare Function EssVSetGlobalOption Lib "ESSEXCLN.XLL" (ByVal Item As Long,
ByVal globalOption As Variant) As Long

I want to set the zoom option in essbase to next level (which is the first
option on the zoom tab), and am not sure how to do this, any help would be
appreciated

I am not sure of the format using the EssVSetGlobalOption Function

Thanks,

If you wish to discuss by phone, email me number to call at [email protected]

I'm pretty sure you should use the EssVSetSheetOption function. I've included
the declaration and the item/option settings for you:

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

Item Option Expected Data Type
---- -------------------------------------- ------------------
'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 formula on Retrieval 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
'20 (Not Used)
'21 Retain formula on Keep/Remove Only Boolean
'22 Enable Retain formula on Zooms Boolean
'23 Enable Formula Fill setting Boolean
'24 Enable Both Member Name and Alias Boolean
'25 Enable Repeat Member Labels setting Boolean
'26 Enable Sheet Query Designer Option Boolean
'27 Enable Latest Time Period Boolean
'28 Specify Latest Time Period Text

I hope that helps?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Nigel" wrote:

> Hopefully someone can answer this question
>
> I have declatred some functions in a module and one of these statements is
>
> Declare Function EssVSetGlobalOption Lib "ESSEXCLN.XLL" (ByVal Item As Long,
> ByVal globalOption As Variant) As Long
>
> I want to set the zoom option in essbase to next level (which is the first
> option on the zoom tab), and am not sure how to do this, any help would be
> appreciated
>
> I am not sure of the format using the EssVSetGlobalOption Function
>
> Thanks,
>
> If you wish to discuss by phone, email me number to call at [email protected]

thanks,

on the code tho, I am trying to set the global options to set the zoom level
to next and not all members

"Ron Coderre" wrote:

> I'm pretty sure you should use the EssVSetSheetOption function. I've included
> the declaration and the item/option settings for you:
>
> Declare Function EssVSetSheetOption Lib "ESSEXCLN.XLL" (ByVal sheetName As
> Variant, ByVal item As Variant, ByVal sheetOption As Variant) As Long
>
> Item Option Expected Data Type
> ---- -------------------------------------- ------------------
> '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 formula on Retrieval 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
> '20 (Not Used)
> '21 Retain formula on Keep/Remove Only Boolean
> '22 Enable Retain formula on Zooms Boolean
> '23 Enable Formula Fill setting Boolean
> '24 Enable Both Member Name and Alias Boolean
> '25 Enable Repeat Member Labels setting Boolean
> '26 Enable Sheet Query Designer Option Boolean
> '27 Enable Latest Time Period Boolean
> '28 Specify Latest Time Period Text
>
> I hope that helps?
>
> ***********
> Regards,
> Ron
>
> XL2002, WinXP-Pro
>
>
> "Nigel" wrote:
>
> > Hopefully someone can answer this question
> >
> > I have declatred some functions in a module and one of these statements is
> >
> > Declare Function EssVSetGlobalOption Lib "ESSEXCLN.XLL" (ByVal Item As Long,
> > ByVal globalOption As Variant) As Long
> >
> > I want to set the zoom option in essbase to next level (which is the first
> > option on the zoom tab), and am not sure how to do this, any help would be
> > appreciated
> >
> > I am not sure of the format using the EssVSetGlobalOption Function
> >
> > Thanks,
> >
> > If you wish to discuss by phone, email me number to call at [email protected]

That functionality is in what I posted....
This statement sets the drill down option for the workbook to "next level"

EssSetSheetOption "Sample.xls",1,1

> Item Option Expected Data Type
> ---- -------------------------------------- ------------------
> '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

(Note: Since I'm not at work today, I can't test it.)

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Nigel" wrote:

> thanks,
>
> on the code tho, I am trying to set the global options to set the zoom level
> to next and not all members
>
> "Ron Coderre" wrote:
>
> > I'm pretty sure you should use the EssVSetSheetOption function. I've included
> > the declaration and the item/option settings for you:
> >
> > Declare Function EssVSetSheetOption Lib "ESSEXCLN.XLL" (ByVal sheetName As
> > Variant, ByVal item As Variant, ByVal sheetOption As Variant) As Long
> >
> > Item Option Expected Data Type
> > ---- -------------------------------------- ------------------
> > '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 formula on Retrieval 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
> > '20 (Not Used)
> > '21 Retain formula on Keep/Remove Only Boolean
> > '22 Enable Retain formula on Zooms Boolean
> > '23 Enable Formula Fill setting Boolean
> > '24 Enable Both Member Name and Alias Boolean
> > '25 Enable Repeat Member Labels setting Boolean
> > '26 Enable Sheet Query Designer Option Boolean
> > '27 Enable Latest Time Period Boolean
> > '28 Specify Latest Time Period Text
> >
> > I hope that helps?
> >
> > ***********
> > Regards,
> > Ron
> >
> > XL2002, WinXP-Pro
> >
> >
> > "Nigel" wrote:
> >
> > > Hopefully someone can answer this question
> > >
> > > I have declatred some functions in a module and one of these statements is
> > >
> > > Declare Function EssVSetGlobalOption Lib "ESSEXCLN.XLL" (ByVal Item As Long,
> > > ByVal globalOption As Variant) As Long
> > >
> > > I want to set the zoom option in essbase to next level (which is the first
> > > option on the zoom tab), and am not sure how to do this, any help would be
> > > appreciated
> > >
> > > I am not sure of the format using the EssVSetGlobalOption Function
> > >
> > > Thanks,
> > >
> > > If you wish to discuss by phone, email me number to call at [email protected]

aaaaaaaaaaah, to much eggnog

Thanks

Have a Happy New Year and enjopy your time off

Nigel

"Ron Coderre" wrote:

> That functionality is in what I posted....
> This statement sets the drill down option for the workbook to "next level"
>
> EssSetSheetOption "Sample.xls",1,1
>
> > Item Option Expected Data Type
> > ---- -------------------------------------- ------------------
> > '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
>
> (Note: Since I'm not at work today, I can't test it.)
>
> Does that help?
>
> ***********
> Regards,
> Ron
>
> XL2002, WinXP-Pro
>
>
> "Nigel" wrote:
>
> > thanks,
> >
> > on the code tho, I am trying to set the global options to set the zoom level
> > to next and not all members
> >
> > "Ron Coderre" wrote:
> >
> > > I'm pretty sure you should use the EssVSetSheetOption function. I've included
> > > the declaration and the item/option settings for you:
> > >
> > > Declare Function EssVSetSheetOption Lib "ESSEXCLN.XLL" (ByVal sheetName As
> > > Variant, ByVal item As Variant, ByVal sheetOption As Variant) As Long
> > >
> > > Item Option Expected Data Type
> > > ---- -------------------------------------- ------------------
> > > '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 formula on Retrieval 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
> > > '20 (Not Used)
> > > '21 Retain formula on Keep/Remove Only Boolean
> > > '22 Enable Retain formula on Zooms Boolean
> > > '23 Enable Formula Fill setting Boolean
> > > '24 Enable Both Member Name and Alias Boolean
> > > '25 Enable Repeat Member Labels setting Boolean
> > > '26 Enable Sheet Query Designer Option Boolean
> > > '27 Enable Latest Time Period Boolean
> > > '28 Specify Latest Time Period Text
> > >
> > > I hope that helps?
> > >
> > > ***********
> > > Regards,
> > > Ron
> > >
> > > XL2002, WinXP-Pro
> > >
> > >
> > > "Nigel" wrote:
> > >
> > > > Hopefully someone can answer this question
> > > >
> > > > I have declatred some functions in a module and one of these statements is
> > > >
> > > > Declare Function EssVSetGlobalOption Lib "ESSEXCLN.XLL" (ByVal Item As Long,
> > > > ByVal globalOption As Variant) As Long
> > > >
> > > > I want to set the zoom option in essbase to next level (which is the first
> > > > option on the zoom tab), and am not sure how to do this, any help would be
> > > > appreciated
> > > >
> > > > I am not sure of the format using the EssVSetGlobalOption Function
> > > >
> > > > Thanks,
> > > >
> > > > If you wish to discuss by phone, email me number to call at [email protected]

Thanks for the feedback....I'm glad I could help.

***********
Regards,
Ron

XL2002, WinXP-Pro


"Nigel" wrote:

> aaaaaaaaaaah, to much eggnog
>
> Thanks
>
> Have a Happy New Year and enjopy your time off
>
> Nigel
>
> "Ron Coderre" wrote:
>
> > That functionality is in what I posted....
> > This statement sets the drill down option for the workbook to "next level"
> >
> > EssSetSheetOption "Sample.xls",1,1
> >
> > > Item Option Expected Data Type
> > > ---- -------------------------------------- ------------------
> > > '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
> >
> > (Note: Since I'm not at work today, I can't test it.)
> >
> > Does that help?
> >
> > ***********
> > Regards,
> > Ron
> >
> > XL2002, WinXP-Pro
> >
> >
> > "Nigel" wrote:
> >
> > > thanks,
> > >
> > > on the code tho, I am trying to set the global options to set the zoom level
> > > to next and not all members
> > >
> > > "Ron Coderre" wrote:
> > >
> > > > I'm pretty sure you should use the EssVSetSheetOption function. I've included
> > > > the declaration and the item/option settings for you:
> > > >
> > > > Declare Function EssVSetSheetOption Lib "ESSEXCLN.XLL" (ByVal sheetName As
> > > > Variant, ByVal item As Variant, ByVal sheetOption As Variant) As Long
> > > >
> > > > Item Option Expected Data Type
> > > > ---- -------------------------------------- ------------------
> > > > '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 formula on Retrieval 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
> > > > '20 (Not Used)
> > > > '21 Retain formula on Keep/Remove Only Boolean
> > > > '22 Enable Retain formula on Zooms Boolean
> > > > '23 Enable Formula Fill setting Boolean
> > > > '24 Enable Both Member Name and Alias Boolean
> > > > '25 Enable Repeat Member Labels setting Boolean
> > > > '26 Enable Sheet Query Designer Option Boolean
> > > > '27 Enable Latest Time Period Boolean
> > > > '28 Specify Latest Time Period Text
> > > >
> > > > I hope that helps?
> > > >
> > > > ***********
> > > > Regards,
> > > > Ron
> > > >
> > > > XL2002, WinXP-Pro
> > > >
> > > >
> > > > "Nigel" wrote:
> > > >
> > > > > Hopefully someone can answer this question
> > > > >
> > > > > I have declatred some functions in a module and one of these statements is
> > > > >
> > > > > Declare Function EssVSetGlobalOption Lib "ESSEXCLN.XLL" (ByVal Item As Long,
> > > > > ByVal globalOption As Variant) As Long
> > > > >
> > > > > I want to set the zoom option in essbase to next level (which is the first
> > > > > option on the zoom tab), and am not sure how to do this, any help would be
> > > > > appreciated
> > > > >
> > > > > I am not sure of the format using the EssVSetGlobalOption Function
> > > > >
> > > > > Thanks,
> > > > >
> > > > > If you wish to discuss by phone, email me number to call at [email protected]