use of Macros in BEx-IP planning workbooks

[former_member199935]

Hi,

I added following macro code in sheet "Sheet4 (BEx)" of BExAnalyzer and trying to assign this macro to a VB button inserted in workbook. But, i'm unable to find macro test123. what could be the reason?

Sub test123()
  Dim lResult As Long
  lResult = Application.Run("SAPExecuteCommand", "Refresh")
End Sub

Then, i tried same macro code in "ThisWorkbook" listed under "Microsoft Excel Objects" of VBA project of BI workbook.  Now, i am able to see macro test123 and assigned to VB button. But, when i click on VB button, i'm getting VB error 400. There must be something simple am doing wrong. Could someone point me in right direction or could someone share a sample of macro code as i'm actually trying to achieve "execution of planning sequence with variables by taking values from user inputs".

[federico_blarasin]

I was actually using Analysis, maybe Bex Analyzer would use a different function instead of SAPExecuteCommand.

Unfortunately I don't have any analyzer workbook at hand, maybe you could try to check the syntax of to call the refresh in Analyzer.

[former_member199935]

Federico,

I'm new to BO & Analysis with my background BPC/IP/BPS. Can you guide me how to get BO Analysis? I've BO suite installed but i don't see ANALYSIS add-in in excel and also it is not listed under BO Menu.

also, Do we need special license to get Analysis? Is license per user? appreciate any inputs.

[federico_blarasin]

You can find Analysis from the service marketplace, but you'd need to check if your client has the licence for it.

My screenshot was from Analysis, but you can replicate it in BEx, you might just need the right syntax for the refresh function. It might be different from the one used in Analysis.

I don't have a BEx workbook at hand, but you could find the refresh function and the right way to use it in the BEx documentation.

[federico_blarasin]

Hello BPC,

I've got the same macro as

Public Sub Refresh_data(control As IRibbonControl)

Dim lResult As Long

Result = Application.Run("SAPExecuteCommand", "Refresh")

End Sub

You can remove the  (control As IRibbonControl) part, which is relevant only if you want to add it as a ribbon button.

[former_member199935]

Thanks Federico for your reply. I copied your code and removed "control As IRibbonControl" but still getting same error "400". Could you tell me where you inserted this code? as i stated in my original post, I may not be inserting this code in correct place of "VB project" of workbook. Appreciate if you could clearly list out which part of VB you are using this code in.

[federico_blarasin]

Sure, this is the location of the code: I've created a new module in the document.

[former_member199935]

Thanks Federico for screenshot. i moved code to Module1 and now getting below error. any further ideas? I made sure that macro settings are set to "enable all macros" and "trust VB project is selected" in excel settings. Same behaviour in Excel 2010 and 2013.