Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Essbase Add-in VBA programming question
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. -- Thanks, PTweety |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Essbase Add-in VBA programming question
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. -- HTH... Jim Thomlinson "pickytweety" wrote: 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. -- Thanks, PTweety |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Programming question - VB, VBA, C#, C++ | Excel Programming | |||
Essbase Addin Question | Excel Discussion (Misc queries) | |||
Programming question | Excel Programming | |||
Programming Question | Excel Programming | |||
Help with programming question | Excel Programming |