Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Oct 2003
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Macros in Hyperion Essbase??? (excel 2002 sp-2)

    First let me say hi to everyone since I am new to the forum. My wife, Ncordero has said many good things about this site. Now for my problem:

    Anyone ever have any problems with macros that they recorded in the Esssbase Ver 6.5.1 Excel Add-in? Essentially I recorded the macro below (see 10/30/03 macro) and it seemed to work just fine. However, the next month when I attempted to use it again (see 11/14/03) it did not. Maybe it never worked at all since I first retrieved the information and then recorded the macro to be used in the following month. Any help would be greatly appreciated.

    Aluis

    Sub retrieve()
    '
    ' retrieve Macro
    ' Macro recorded 11/14/2003 by aluislugo
    '
    ' Keyboard Shortcut: Ctrl+r
    '
    Range("B38").Select
    Sheets("1").Select
    Range("B38").Select
    Sheets("Admin-Trading").Select
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    Range("N40").Select
    ActiveCell.FormulaR1C1 = "=SUM('1'!R[-2]C+'2'!R[-2]C+'3'!R[-2]C+'5'!R[-2]C)"
    Range("N40").Select
    Selection.Style = "Comma"
    Selection.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
    Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    Range("N40").Select
    End Sub

    Sub retrieve()
    '
    ' retrieve Macro
    ' Macro recorded 10/30/2003 by Aluislugo
    '
    ' Keyboard Shortcut: Ctrl+r
    '
    Sheets("5").Select
    Range("N35").Select
    Sheets("4").Select
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    Range("N38").Select
    Sheets("3").Select
    Range("N38").Select
    Sheets("2(B)").Select
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    Range("N38").Select
    Sheets("2(A)").Select
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    Range("N38").Select
    Sheets("2").Select
    Range("N38").Select
    Sheets("1(B)").Select
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    Range("N38").Select
    Sheets("1(A)").Select
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    Range("N38").Select
    Sheets("1").Select
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    Range("N38").Select
    Sheets("Admin-Trading").Select
    Range("N40").Select
    Selection.FormulaR1C1 = "="
    Sheets("Admin-Trading").Select
    ActiveCell.FormulaR1C1 = _
    "=+'1'!R[-2]C+'2'!R[-2]C+'3'!R[-2]C+'4'!R[-2]C+'5'!R[-2]C"
    Range("N40").Select
    ActiveCell.FormulaR1C1 = _
    "=+'1'!R[-2]C+'2'!R[-2]C+'3'!R[-2]C+'4'!R[-2]C+'5'!R[-2]C"
    Range("N40").Select
    Sheets("3").Select
    ActiveWindow.ScrollRow = 16
    ActiveWindow.ScrollRow = 15
    ActiveWindow.ScrollRow = 14
    ActiveWindow.ScrollRow = 13
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 11
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 1
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Sheets("4").Select
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    ActiveWindow.ScrollRow = 19
    ActiveWindow.ScrollRow = 18
    ActiveWindow.ScrollRow = 17
    ActiveWindow.ScrollRow = 16
    ActiveWindow.ScrollRow = 15
    ActiveWindow.ScrollRow = 14
    ActiveWindow.ScrollRow = 13
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 11
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 1
    Sheets("Admin-Trading").Select
    ActiveCell.FormulaR1C1 = "=+'1'!R[-2]C+'2'!R[-2]C+'3'!R[-2]C+'5'!R[-2]C"
    Range("N40").Select
    End Sub

  2. #2
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Missouri, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Macros in Hyperion Essbase??? (excel 2002 sp-2)

    Yep. By retrieving it beforehand when you recorded it, you never accessed Essbase. We have several who have had trouble accessing Essbase because they did what you did.

  3. #3
    Star Lounger
    Join Date
    May 2002
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Macros in Hyperion Essbase??? (excel 2002 sp-2)

    Ok Shades so how do I fix the problem?!?!?

  4. #4
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Missouri, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Macros in Hyperion Essbase??? (excel 2002 sp-2)

    Unfortunately, I am not able to "fix" the problem. The company does not see fit for me to have Essbase, so I have never used it. And the others who do have it gave up in frustration (they don't know much VBA, far less than me, and I am still learning). So, it is "unresolved" for them.

    My guess would be that you would have to include the Shell function to access Essbase, and you would have to have access to the Essbase Reference Library in VBA; and for the Shell function to work, you will need the TaskID # for Essbase. But that is only a guess.

    ---------------------

    BTW, on your code, you can most likely eliminate all of the "scroll" lines.

  5. #5
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Macros in Hyperion Essbase??? (excel 2002 sp-2)

    Given Shades answer, all I can do is post you a sample of essbase 5 code - we aren't on six yet, so you're probably going to have to do somealtering. Hopefully, however, it will get you started. In version 5 there isn't a macro recorder as you seem to indicate, and excel will not record essbase code through it's macro recorder.

    '################################################# ###################
    Sheets("Total Europe").Select

    x = EssVConnect(Empty, "User Name", "Password", "Server", "Application", "Database")

    Application.GoTo Reference:="Tot_PL_Ord"
    x = EssMenuVRetrieve()
    If x = 0 Then intchkgd = intchkgd + 1 Else intchkbd = intchkbd + 1

    Application.GoTo Reference:="Tot_PL_Rev"
    x = EssMenuVRetrieve()
    If x = 0 Then intchkgd = intchkgd + 1 Else intchkbd = intchkbd + 1

    x = EssVDisconnect(Empty)
    '################################################# ###################

    Note that all the commands are EssV***** - they may be EssVI***** for version 6 but a check of the help file will tell you for sure. FWIW, I have found the Helpfiles to be very user-friendly in comparison to other applications helpfiles.

    some comments to help with the above - again, all with reference to version 5.

    the connection line has the arguments passed in as you see them in the logon/connection box. be careful of the fifth - "Application" - as if you define it as a variable at the top of your code it will interfere with your intellisense. Arbour defined the name of it as Application and so excel gets confused between that and it's own application object.

    there are two retrievals here: all you have to do is select the area of the retrieval and then call the EssMenuRetrtieve function. the value passed back is o if succesful and 1 if not: this allows you to keep a tally of how many successful/unsuccessful retrievals have occured.

    Always disconnect - this stops essbase getting muddled if you have connections to more than one database open.

    finally, you will need to declare the functions. again, the helpfile will show you how to do this, but the three I've used above are demonstrated below. These should go at the top of the module before any sub or function.
    <pre>Declare Function EssVConnect Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, _
    ByVal username As Variant, _
    ByVal password As Variant, _
    ByVal server As Variant, _
    ByVal EssbaseApp 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

    </pre>


    Note that I have actually changed the name of the parameter "Application" to "EssbaseApp" in the connection function to get around the problem I mentioned earlier.

  6. #6
    Star Lounger
    Join Date
    Oct 2003
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Macros in Hyperion Essbase??? (excel 2002 sp-2)

    My guess would be that you would have to include the Shell function to access Essbase, and you would have to have access to the Essbase Reference Library in VBA; and for the Shell function to work, you will need the TaskID # for Essbase. But that is only a guess.

    What does this mean? What is a shell function? What is the Essbase Reference Library in VBA? And what is the TAskID # for Essbase?

    Aluis

  7. #7
    Star Lounger
    Join Date
    Oct 2003
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Macros in Hyperion Essbase??? (excel 2002 sp-2)

    Hi Brooke,

    I don't really understand this. What does this code do? Where does this code go? In the module? If so which one?

    ################################################## ##################
    Sheets("Total Europe").Select

    x = EssVConnect(Empty, "User Name", "Password", "Server", "Application", "Database")

    Application.GoTo Reference:="Tot_PL_Ord"
    x = EssMenuVRetrieve()
    If x = 0 Then intchkgd = intchkgd + 1 Else intchkbd = intchkbd + 1

    Application.GoTo Reference:="Tot_PL_Rev"
    x = EssMenuVRetrieve()
    If x = 0 Then intchkgd = intchkgd + 1 Else intchkbd = intchkbd + 1

    x = EssVDisconnect(Empty)
    '################################################# ###################

    What about this code? What does this code do? Where does this code go? In the module? If so which one?

    Declare Function EssVConnect Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, _ ByVal username As Variant, _ ByVal password As Variant, _ ByVal server As Variant, _ ByVal EssbaseApp As Variant, _ ByVal database As Variant) _ As LongDeclare Function EssVDisconnect Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant) _ As LongDeclare Function EssVRetrieve Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, _ ByVal range As Variant, _ ByVal lockFlag As Variant) _ As Long

  8. #8
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Macros in Hyperion Essbase??? (excel 2002 sp-2)

    part of the answer may depend on precisely how your retrieval zones are set out on the individual worksheets. Is it possible to post an example? overwrite any sensitve data within the retrieval zone with nonsense values - what I'm really looking for is whether you've got formulae in amoungst the retrieval zones and their general layout.

  9. #9
    Star Lounger
    Join Date
    Oct 2003
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Macros in Hyperion Essbase??? (excel 2002 sp-2)

    Brooke what is your email? I can send you a file to look at.

    Thanks
    Aluis

  10. #10
    Star Lounger
    Join Date
    Oct 2003
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Macros in Hyperion Essbase??? (excel 2002 sp-2)

    brooke nevermind I just saw your email. The file is on its way.

    Thanks!

  11. #11
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Macros in Hyperion Essbase??? (excel 2002 sp-2)

    Armando,

    Take a look at the attached file. You will need to open it up and hit [ALT] +[F11] to view it in the IDE. There is some code in the ThisWorkbook and mod_menu modules but all the essbase code is contained within the module mod_essbase.

    To use this on your workbook, you will need this file open, and then make your spreadsheet the active workbook. Then you can call the menu item that will refresh the retrieval zones in your workbook. However, you will need to make some changes first.

    The changes you will need to make are to the connection line and the retrieval line in the macro "Refresh_Sheet", and the Sheet Names in the macro "Refresh_Workbook". From looking at the code you had in the file you sent me, I don't think you will have a problem with this, although I was confused by your range: I'd check it shouldn't be "A6:Q37" instead of "B6:Q37".

    FWIW, the approach you were taking was probably confusing you because I have been talking throughout this thread about using vba, whilst you have been trying to convert my advice into the old version 4 macro code. This still works if you know what you are doing but I don't - I did inherit some essbase macro's that used excel4 macro code, and may be able to work through what they do, but anything new I have written is in vba: I would strongly recommend that you adopt the same approach.
    Also FWIW, I mentioned earlier that I was using essbase 5, and not 6 as you are. I have since upgraded to version 6 and I have tested the code in the attached file since.

    To answer two of your earlier questions,

    a) the functions such as "EssVConnect" are not native to Excel, and so need to be declared to Excel by a call to the library that they are contained in, detailing the parameters that need to be passed. This is the declaration code at the top of the essbase module.

    [img]/forums/images/smilies/cool.gif[/img] the code that contains the variables "intchkgood" etc in a previous post of mine is just a version of the code in the attached file, with error checking included. When you call one of the essbase functions, not only does it perform the requested action, but it also returns a value indicating whether the call was successful or not. This enables you to write code comparing the number of retrievals performed against the number of succesful retrievals. If these two do not match then you can generate some kind of error report that all is not well, allowing you to fix the problem before sending inaccurate data to your boss/colleagues.

    If you have any more problems or questions, feel free to ask!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •