Free Microsoft Excel 2013 Quick Reference

Hyperion Essbase VBA

It's been tricky finding solid tips for using VBA with Hyperion Essbase so I figured I would post my question here. I'm using the following code to refresh Essbase queries in my workbook. This is just a snippet, I run the same block of code for each of the 10 worksheets in the file with Essbase queries.

Code:
Sheets("LOBDetail").Select
Range("B1:X241").Select
Application.Run macro:="EssMenuRetrieve"
 
Sheets("MarketingSpend").Select
Range("A1:V132").Select
Application.Run macro:="EssMenuRetrieve"
When a user has already connected to Essbase (logged in) and then fires the macro, it works brilliantly. When a user is not yet logged in and runs the macro they will see the Log In dialog box right away. Perfect, right? They enter their login credentials, select the correct database to connect to and the code finishes running.

However, whichever query is first in the workbook will be skipped over if the user is asked to log in. I believe the code above selects the correct range, tries to run an Essbase Retrieve, then goes to the next worksheet specified in the code and prompts the user to log in.

Question: Does anybody know how to first check if the user has connected to Essbase before running the rest of the code?


I have the Hyperion Essbase Add In on my work PC.
Currently I have VBA code that selects the range Essbase needs to Retrieve data... however I would like the code to actually retrieve the data as well.

When I record the actions of retrieving the data nothing is stored in the VB Module.

I assume this can be written into the code since it is performed through choosing Essbase and then Retrieve on the tool bar...

Anyone know how to perform this task?

Hi we are using Hyperion Essbase to upload budget packs to essbase.

The only thin we do is to mark one cell in each sheet in a workbook
choose "Lock" and then "Send". (in essbase add-in (menu))

Is it possible to make a macro which does this for me, something lik
for each sheet in workbook. Mark cell "C2", then "Lock" then "send".

Thank

--
Ctec
-----------------------------------------------------------------------
Ctech's Profile: http://www.excelforum.com/member.php...fo&userid=2774
View this thread: http://www.excelforum.com/showthread.php?threadid=48788

Hi ....

I am using the Hyperion Essbase Excel Add-In to interface with an Essbase
cube. I am puzzled by the drill down behavior (it is different from a pivot
table) and wonder if someone out there can help me.

Assume only 3 dimensions: Years, Department, Account. Years is set to 2005
and is in the page section. Both Department and Account are in the row
section, and assume 5 member levels for each of these Dimensions.

Problem: I want to drill down so that: 1) I get only zero-level Accounts AND
zero-level Departments; 2) for each Department I want to see all zero level
accounts. In other words, I need to see Department_1 on the left, and then
all 50 Departments on the right (accompanied by Account balace) - one row
perAccount, then the next Department (and all Accounts), etc. etc. for all
zero-level Departments and Accounts.

This is a simple feat for an Excel pivot table, but I keep getting duplicate
rows when I try to get the job done in essbase.

Anybody know the solution?

Thanks for your help ...

bill morgan

Hi we are using Hyperion Essbase to upload budget packs to essbase.

The only thin we do is to mark one cell in each sheet in a workbook, choose "Lock" and then "Send". (in essbase add-in (menu))

Is it possible to make a macro which does this for me, something like for each sheet in workbook. Mark cell "C2", then "Lock" then "send".

Thanks

Hi All

The company I work for have recently purchased Hyperion Essbase which comes with an Excel Add-in. Unfortunately this add-in seems to conflict with an existing add-in we use from SunSystems called Vision XL (it provides a direct interface between Excel and Sun Accounts) and the two cannot be installed at the same time. Consequently I'm now trying to write some VBA to toggle between the two. The macro is below and logically should work however the 'AddIns("Vision5").Installed = False' statement seems to halt and exit the macro after the add-in has been disabled resulting in the other add-in not being enabled.

I'm pretty sure that this quirk is particular to Vision5 as I've tried the same code with other add-ins and it seems to work ok with them.

Does anyone have any suggestions on how to overcome this problem? I'm not a VBA expert and there may be a better way of going about resolving the problem.


	VB:
	
 Essbase_Vision_Switch() 
     
    Dim qResult As Integer 
     
    If AddIns("Vision5").Installed = True Then 
         
        qResult = MsgBox("You are about to switch from Vision to Essbase. Do you wish to continue?", vbYesNo + vbQuestion,
"Add-in Switch") 
         
        If qResult = 6 Then 
             
            [COLOR=YellowGreen]AddIns("Vision5").Installed = False[/COLOR] 
            AddIns("Essexcln").Installed = True 
            AddIns("Hyperion Essbase Query Designer Addin").Installed = True 
             
        Else 
             
            Exit Sub 
        End If 
         
    Else 
         
        If AddIns("Essexcln").Installed = True Then 
             
            qResult = MsgBox("You are about to switch from Essbase to Vision. Do you wish to continue?", vbYesNo +
vbQuestion, "Add-in Switch") 
             
            If qResult = 6 Then 
                 
                AddIns("Essexcln").Installed = False 
                AddIns("Hyperion Essbase Query Designer Addin").Installed = False 
                AddIns("Vision5").Installed = True 
                 
            Else 
                 
                Exit Sub 
            End If 
             
        Else 
             
        End If 
    End If 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Any assistance would be great as this proving to be really frustrating!!

Regards,

S_Stewart

Hello,

I have an excel report which requires data to be pulled from a web-based Hyperion platform (Hyperion Performance Suite 8.5 / Brio) as well as from Hyperion-Essbase, and then formatted.

I've been able to structure the report's formulas and create macros to automate the data grab from Essbase, as well as the formatting and running of the report.

Is there a way to use VBA to finish what's left of the report: open a webpage, enter in the login credentials to the Hyperion web-platform, select the desired fields, run the report, export to excel, and then import the worksheet into the current excel workbook?

I am thinking this is a strech, since the macro would have to compensate for the lag since it is a web application, but it would be a nice plus to my team if I could reduce the requirements of this report to truely be one-click.

If this is partly or even wholly feasible, any direction would be great. Thanks!

Another Essbase Excel Add-in question.

When I retrieve values into Excel from our Essbase Cube (using Hyperon Excel
Add-In), null values return as text characters. Even if I format the cells,
these values appear as "0". The only way I can change the text to numbers is
through a VBA procedure that changes "0" to value 0 for all cells in the used
range.

Can anything be done (either on user end or Admin side) to get null to
return as number 0 without having to use VBA?

Thanks ...

bill morgan

Another Essbase Excel Add-in question.

When I retrieve values into Excel from our Essbase Cube (using Hyperon Excel
Add-In), null values return as text characters. Even if I format the cells,
these values appear as "0". The only way I can change the text to numbers is
through a VBA procedure that changes "0" to value 0 for all cells in the used
range.

Can anything be done (either on user end or Admin side) to get null to
return as number 0 without having to use VBA?

Thanks ...

bill morgan

I am using the Essbase Excel Add-In to interface with an Essbase cube.

I need a list of Essbase functions (like EssVRetrieve() or
EssVSetSheetOption () - inlcuding syntax and examples - that can be used
inside VBA code.

Can anyone point me in the right direction?

Thanks for your help ...

bill morgan

Attempted to record a macro while sending data to Hyperion Essbase via the
Excel Add-In. However, the macro did not record my steps.

Is there a way to accomplish what I am trying to do? Is there generic code
to send directly to Essbase? What about something that will simply record
the keystrokes to activiate the send how a regular end-user would?

Thanks!

Would anyone know of a macro so I can automatically connect my
worksheets to ESSBASE 6.5?

I have Win 7 pro x64 with Excel 2007

I am having Oracle Hyperion Essbase addin stability issues.

anyone else have those issues?

sportsguy

We are currently on Office 2000, but our IT group are considering a company wide switch to 2007. I've played around with it just briefly, but noticed that it is night and day difference in both look and feel.

We use Hyperion Essbase, and have a custome built planning tool that utilizes Excel 2000, enhaced with a few thousand lines of VBA coding. One of the main purposes of the VBA code, is to control the Excel menus and replace with custom built menus that are specific to our Planning System.

Does anyone have any idea if VBA controlled custom menus from Excel 2000 will even work in 2007? Will I have to do complete rewrite?

One last question, If you are running 2007, is there a transition function (simlar) to prior versions, where you make the menus look like the legacy version?

Any insight will be helpful.

Hi

I am a new user to this board and I must say I am really thankful to what all help I've been able to receive from this website in order to learn and understand how Excel Macro's work.

I am trying to create a Macro, which can actually retrieve data from Hyperion Essbase system for my retrieve template (already completed this Macro).

Second Part:

Now I am working on creating this Macro to create an email using Outlook which will have the message as below:

-------------------------------------------------------------------------
Hi,

Please find the link to Jul-10 file for 06-08-10 for Group 1 :

link to the file:

"D:Documents and SettingsadminDesktopNew.xlsx"

Total = $ Debit 409k

1. Random 1 = $ Credit 50k (Need to check with Dept)
2. Random 4 = $ Credit 60k (Genuine)a. Random 2 = $ Credit 50k (Cause Known)b. Random 3 = $ Credit 10k (Unsure)3. Random 5 = $ Nil
4. Random 6 = $ Credit 100k (Expected)
5. Random 7 = $ Credit 1k (Immaterial)
6. Random 8 = $ Debit 10k (Investigation pending)
7. Random 9 = $ Debit 30k (Investigation pending)
8. Random 10 = $ Debit 550k (Investigation pending)
9. Random 11 = $ Debit 30k (Investigation pending)

Thanks and Kind regards,

Signature [Auto signature should come up]

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

The format is really critical, it has to have the format as stated above only and no change.

The numbers for each item gets populated from Column 'H' in the attached excel file, the Debits needs to appear in Red (Bold) and Credits in Green, the comment section in the attached file (Column BX) comes after each line in brackets in Black (Bold color only) as shown on the above format.

I am struggling with the formatting section when trying to code, any help would be useful.

Thanks and Kind regards

Rahul


	VB:
	
 ShellExecute Lib "shell32.dll" _ 
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _ 
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _ 
ByVal nShowCmd As Long) As Long 
 
Sub SendEMail() 
    Dim Email As String, Subj As String 
    Dim Msg As String, URL As String 
    Dim Today As Date 
    Today = Date 
    Dim r As Integer, x As Double 
    For r = 2 To 4 'data in rows 2-4
         '       Get the email address
        Email = Cells(r, 2) 
         
         '       Message subject
        Subj = Cells(r, 3) 
         
         '       Compose the message
        Msg = "" 
        Msg = Msg & "Please find the link to " & Cells(r, 4) & Format(Today, "DD-MMM-YY") & " " & vbCrLf 
         'ActiveWorkbook.SaveAs Filename:="My_File" & Format(Today, "YYYY_MM_DD")
         'Msg = Msg & "FOF variance" & Cells(r, 4) - EEM -    Audience = $  Credit 25kMsg & Cells(r, 3).Text & "." &   vbCrLf
&  vbCrLf
         
         
         
         '       Replace spaces with %20 (hex)
        Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20") 
        Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20") 
         
         '       Replace carriage returns with %0D%0A (hex)
        Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A") 
         
         '       Create the URL
        URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg 
         
         '       Execute the URL (start the email client)
        ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus 
         
         '       Wait two seconds before sending keystrokes
         'Application.Wait (Now + TimeValue("0:00:02"))
         'Application.SendKeys "%s"
    Next r 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi all

I have a Userform (Treevw_frm) which utilises the Treeview component and upon making a selection and pressing OK executes the code below.

We use Hyperion Essbase and the VB needs usernames (varUser) and passwords (varPasswd) in order to retrieve the data. If these values = "" then a second userform is presented to gather this data.

The problem I have is that at the end of the macro my Userform (Treevw_frm) should remain on screen in case another selection is required. If the VB below runs without having to gather varUser and varPasswd (they have already been entered) then all is well. However if these variables need to be gathered my Userform (Treevw_frm) Unloads when it reaches the last Exit Sub.

I have no idea why this happens, as far I can tell nothing is telling it to Unload so why does is disappear?


	VB:
	
 ok_btn_Click() 
     
    On Error Goto Update_Problem 
     
    Dim Activ_sheet As String, x As Long 
     
    If varCCentre = "" Then 
         
        MsgBox "You have not selected a Cost Centre. Please try again", vbOKOnly, "No Cost Centre" 
         
        Exit Sub 
         
    Else 
         
         'Gathers variables for Retrieving from Essbase
        varUser = Worksheets("login data").range("usernme") 
        varPasswd = Worksheets("login data").range("passwd") 
        strServer = Worksheets("Login Data").range("server") 
         
        If varUser = "" Or varPasswd = "" Then 
            EssBase_Login.Show 
             
            varUser = Worksheets("login data").range("usernme") 
            varPasswd = Worksheets("login data").range("passwd") 
             
            If varUser = "" Or varPasswd = "" Then 
                MsgBox "You failed to enter your login details. Please start again", vbOKOnly, "Error" 
                Exit Sub 
            End If 
        End If 
         
        application.ScreenUpdating = False 
         
        Activ_sheet = ActiveSheet.Name 
         
        Worksheets("control Sheet").range("d6").Value = varCCentre 
         
         'Sets global Essbase options
        x = EssVSetGlobalOption(5, 3) 
        x = EssVSetGlobalOption(6, False) 
         
         
        For i = 1 To 3 
             'Set sheet name/app/db
            strSheet = ThisWorkbook.Worksheets("Login Data").range("a4").Offset(i - 1, 0) 
            strApp = ThisWorkbook.Worksheets("Login Data").range("a4").Offset(i - 1, 1) 
            strDb = ThisWorkbook.Worksheets("Login Data").range("a4").Offset(i - 1, 2) 
             ' Set worksheet options
            x = EssVSetSheetOption("Ret_MRA-HMA Data", 10, False) 
             'Update sheet
            sts = UpdateSheetFromEssbase(strSheet, strServer, varUser, varPasswd, strApp, strDb) 
             'if unsuccessful exit macro
            If sts  0 Then 
                ProcessSingleTemplate = sts 
                Exit Sub 
            End If 
        Next 
         
        Worksheets(Activ_sheet).Select 
         
        application.ScreenUpdating = True 
         
    End If 
     
    Exit Sub 
     
Update_Problem: 
    Worksheets(Activ_sheet).Select 
    application.ScreenUpdating = True 
    MsgBox "An error has occurred.  You may only Update from the Monthly Accounts file.", vbOKOnly, "Error" 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
All variables are properly declared as far as I can tell. If any futher info is required please let me know.

If anyone can offer any ideas it would be greatly appreciated.

Regards,

Steve

Hi,

I am creating a hyperion essbase VB application in Excel. I am using VB
APIs provided by hyperion. I have a tree structure, and i want to
display that tree in the parent child relations in list box. I mean, i
want to display that tree in the same way the windows explorer is
allowing us to browse through the file directories in the left frame (
by showing list of folders and '+' sign is added to the folders that
have another directories below it. If you click on the '+' sign, it
will show you directories below it. In the same way, I have a parent,
and below it there are some children, and those children are again
parent of another children and so on..). my question is, is it possible
to display it in list box, or any other component? and if yes, how?

TIA

King.

Hi all,

In Windows Excel (2002 edition) my right click button seems to not be
working (it works everywhere else except in the cells). In the cells,
the right click acts exactly as a left click.

The problem is when I want to edit a macros button (which requires me
to right click) but everytime I try to do that by right clicking on it,
it sees it as a left click and runs the macros. Any help would be
appreciated.

BTW, my operating system is Windows XP.

Thanks.

PS: not sure if this is pertinent, but I have a hyperion essbase
database retrieval program installed in Excel as an add-on.

I've got a very strange issue where on some computers, the workbook opens
just fine, but on others, Excel crashes as soon as it opens and says:
"Microsoft Excel has encountered a problem and needs to close. We are sorry
for the inconvenience."
If I change the order of some of some hidden worksheets on one of the
computers where it does still work, and then reopen it on the ones where it
had crashed, it's okay. While I do have this work-around for the time being,
I'm also seeing some similar issues, and would like to try and figure out the
underlying cause.
We make heavy use of macros, and the third party components from Hyperion
Essbase.

Thanks!
Stephanie

I have a worksheet/file that is 20mb because it contains a lot of links,
formulas, and apparently some embedded coding. I think the coding is there
because the file uses Hyperion Essbase to retrieve data from a financial
general ledger.

In order to reduce the file size, I highlight the entire sheet, select copy,
then paste values into a new worksheet in a new file. This leaves me with
approximately 8,000 rows and 20 columns of data. The size of the new file is
12mb.

I would expect the new file size to be much less than 12mb. Is there a way
to further reduce the file size, or am I stuck with a 12mb file because of
the large number of rows/columns?

Thanks,
Scott

I need a quick solution to a data input issue. I have a sales forecast where
the "end user" had to type sales projections for the rest of the year. They
input the raw number, instead of the formula with the number. Example 5
instead of =5. Since I am using hyperion essbase it will remove all of the
forecast work unless it recogonizes the cell as a formula.

Question? Does anyone know of a quick way of turning 5 into =5 in the
cells?

Thanks

Hi

I am a new user to this board and I must say I am really thankful to what all help I've been able to receive from this website in order to learn and understand how Excel Macro's work.

I am trying to create a Macro, which can actually retrieve data from Hyperion Essbase system for my retrieve template (already completed this Macro).

Second Part:

Now I am working on creating this Macro to create an email using Outlook which will have the message as below:

-------------------------------------------------------------------------
Hi,

Please find the link to Jul-10 file for 06-08-10 for Group 1 :

link to the file:

"D:Documents and SettingsadminDesktopNew.xlsx"

Total = $ Debit 409k

1. Random 1 = $ Credit 50k (Need to check with Dept)
2. Random 4 = $ Credit 60k (Genuine)a. Random 2 = $ Credit 50k (Cause Known)
b. Random 3 = $ Credit 10k (Unsure)
3. Random 5 = $ Nil
4. Random 6 = $ Credit 100k (Expected)
5. Random 7 = $ Credit 1k (Immaterial)
6. Random 8 = $ Debit 10k (Investigation pending)
7. Random 9 = $ Debit 30k (Investigation pending)
8. Random 10 = $ Debit 550k (Investigation pending)
9. Random 11 = $ Debit 30k (Investigation pending)

Thanks and Kind regards,

Signature [Auto signature should come up]

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

The format is really critical, it has to have the format as stated above only and no change.

The numbers for each item gets populated from Column 'H' in the attached excel file, the Debits needs to appear in Red (Bold) and Credits in Green, the comment section in the attached file (Column BX) comes after each line in brackets in Black (Bold color only) as shown on the above format.

I am struggling with the formatting section when trying to code, any help would be useful.

Thanks and Kind regards

Rahul

:

Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long

Sub SendEMail()
    Dim Email As String, Subj As String
    Dim Msg As String, URL As String
    Dim Today As Date
    Today = Date
    Dim r As Integer, x As Double
    For r = 2 To 4 'data in rows 2-4
'       Get the email address
        Email = Cells(r, 2)
        
'       Message subject
        Subj = Cells(r, 3)

'       Compose the message
        Msg = ""
        Msg = Msg & "Please find the link to " & Cells(r, 4) & Format(Today, "DD-MMM-YY")
& " " & vbCrLf
        'ActiveWorkbook.SaveAs Filename:="My_File" & Format(Today, "YYYY_MM_DD")
         'Msg = Msg & "FOF variance" & Cells(r, 4) - EEM -   Audience = $  Credit 25kMsg & Cells(r,
3).Text & "." &  vbCrLf &  vbCrLf


        
'       Replace spaces with %20 (hex)
        Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")
        Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")
                
'       Replace carriage returns with %0D%0A (hex)
        Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")

'       Create the URL
        URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg

'       Execute the URL (start the email client)
        ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus

'       Wait two seconds before sending keystrokes
        'Application.Wait (Now + TimeValue("0:00:02"))
        'Application.SendKeys "%s"
    Next r
End Sub


We are using Hyperion Essbase and when we altdouble click to retrieve further
data the research tool bar comes up on the right hand side. I cannot find a
way to turn this off. It is not needed. I have managed to turn off the
Getting started toolbar that used to pop up but this one continues to drive
me crazy!!! Can anyone who has knowledge of Hyperion Essbase please help me.

Sincerely

I'm trying to create a macro using VBA. However, I don't get the retrieval that I'm looking for in Sheet2. Below is my codes to retrieve data after I connect to Essbase.

Sub ConnRetrieve()
X = EssVConnect("[Book7.xls]Sheet2", "admin", "password", "local", "Sample", "Basic")
X = EssVRetrieve("[Book7.xls]Sheet2", Null, 1)
End Sub

Any help on this is appreciated!

I want to run my existing Query I wrote using Essbase Query Designer, in Excel using VBA. But all it does is open the Query Designer wizard for me, but doesn't wait for my response and proceeds to the next step. What should I do?
Currently I have:

	VB:
	
qmenu = EssMenuVQueryDesigner() 
If qmenu = 0 Then 
    MsgBox ("query successful!") 
Else 
    MsgBox ("Try again!") 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
And I get "Try again" message box.
Any help to make this process simple is appreciated! Basically I want to open already saved Query and run that before retrieving data.
Thanks!