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

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

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

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Thanks
Aluis

Reply | Quote

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

Thanks
Aluis

Reply | Quote

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

Thanks!

Reply | Quote

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.

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!

Reply | Quote

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.

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!

Reply | Quote

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

Thanks!

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

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

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.

Reply | Quote

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

Reply | Quote