How to switch BPC application in VBA macro

[a_khanevich]

Hi to all!

We are on SAP BPC 7.5NW SP14.

We need to run DM Package by pushing the button in the input schedule. DM Package can be located in different application than in CV , so we need to switch it before running DM Package.

How to switch BPC application in VBA macro?

Regards,

Oleg.

[former_member186338 · Accepted solution]

Hi Alex,

Alternative solution (we used it many times):

Use RUNLOGIC BADI to launch script from one cube in another cube.

B.R. Vadim

[a_khanevich · Accepted solution]

Hi Vadim.

Yes, it's known workaround with the use of RUNLOGIC BADI, but it's not very nice. In that case much redundant scripts need to be created.

Is there any other options? Some sample vba code?

Thanks,

Oleg.

[former_member186338 · Accepted solution]

Hi Aleh,

Try the following code - it will change the application lock:

Option Explicit

Public Sub ChangeApp(strNewAppName As String, strCurrentSheetName As String)

Dim wshSheet As Worksheet

Dim wshCurrentSheet As Worksheet

Dim namName As Name

Dim blnNoLimitedCV As Boolean

' Find other worksheet to temporary activate (the workbook has to contain at least 2 visible worksheets)

For Each wshSheet In ThisWorkbook.Worksheets

    If wshSheet.Name <> strCurrentSheetName And wshSheet.Visible = xlSheetVisible Then GoTo OtherFound

Next

MsgBox "Only one visible worksheet in the book!"

Exit Sub

OtherFound:

Set wshCurrentSheet = ThisWorkbook.Worksheets(strCurrentSheetName)

' Test if the sheet already has lock

blnNoLimitedCV = False

For Each namName In wshCurrentSheet.Names

    If namName.Name = strCurrentSheetName & "!Ev__LimitedCV" Then GoTo NameFound

Next

blnNoLimitedCV = True

Set namName = wshCurrentSheet.Names.Add("Ev__LimitedCV", "", False)

NameFound:

namName.Value = "Application:" & strNewAppName & "|Application:" & strNewAppName

wshSheet.Activate

wshCurrentSheet.Activate

If blnNoLimitedCV Then

    namName.Delete

End If

Set namName = Nothing

Set wshSheet = Nothing

Set wshSheet = Nothing

End Sub

Public Sub ChangeAppCurrentSheet()

ChangeApp "ADVSALES", ThisWorkbook.ActiveSheet.Name

End Sub

B.R. Vadim

Message was edited by: Vadim Kalinin P.S. Some code have to be corrected if the sheet has dimension lock...

[former_member186338 · Accepted solution]

Correction:

...

NameFound:

Dim strAppName as String

strAppName = Mid(namName.Value,Instr(namName.Value,":")+1,Instr(namName.Value,"|")-Instr(namName.Value,":")-1)

namName.Value = Replace(namName.Value, strAppName, strNewAppName)

B.R. Vadim

[a_khanevich · Accepted solution]

Hi Vadim,

The code you provided works only in case of locked application.

If application is not locked the sub ChangeApp fails on the following line:

     Set namName = wshCurrentSheet.Names.Add("Ev__LimitedCV", "", False)

Could you please review this case.

Thanks in advance.

Regards,

Oleg.

[former_member186338 · Accepted solution]

Hi Oleg,

Some corrections has to be done. Look here:

Option Explicit

Public Sub ChangeApp(strNewAppName As String, strCurrentSheetName As String)

    Dim wshSheet As Worksheet

    Dim wshCurrentSheet As Worksheet

    Dim namName As Name

    Dim blnNoLimitedCV As Boolean

    Dim strCurrAppName As String

    ' Find other worksheet to temporary activate (the workbook has to contain at least 2 visible worksheets)

    For Each wshSheet In ThisWorkbook.Worksheets

        If wshSheet.Name <> strCurrentSheetName And wshSheet.Visible = xlSheetVisible Then GoTo OtherFound

    Next

    MsgBox "Only one visible worksheet in the book!"

    Exit Sub

OtherFound:

    Set wshCurrentSheet = ThisWorkbook.Worksheets(strCurrentSheetName)

    ' Test if the sheet

    blnNoLimitedCV = False

    For Each namName In wshCurrentSheet.Names

        If namName.Name = strCurrentSheetName & "!Ev__LimitedCV" Then GoTo NameFound

    Next

    blnNoLimitedCV = True

    Set namName = wshCurrentSheet.Names.Add("Ev__LimitedCV", "Application:" & strNewAppName & "|Application:" & strNewAppName, False)

    GoTo SwitchSheet

NameFound:

    strCurrAppName = Mid(namName.Value, InStr(namName.Value, ":") + 1, InStr(namName.Value, "|") - InStr(namName.Value, ":") - 1)

    namName.Value = Replace(namName.Value, strCurrAppName, strNewAppName)

SwitchSheet:

    wshSheet.Activate

    wshCurrentSheet.Activate

    If blnNoLimitedCV Then

        namName.Delete

    End If

    Set namName = Nothing

    Set wshSheet = Nothing

    Set wshSheet = Nothing

End Sub

Public Sub ChangeAppCurrentSheet()

    ChangeApp "INFILE", ThisWorkbook.ActiveSheet.Name

End Sub

B.R. Vadim

[a_khanevich · Accepted solution]

Hi again!

There is one more problem that block the use of provided macro. The main purpose of such workbooks with app switching functionality is to link it to the step of BPF. If such workbook is opened from BPF then app switching functionality doesn't work (app switches to app from bpf step CV settings).

Any ideas?

Regards,

Oleg.

[former_member186338 · Accepted solution]

Ups,

BPF is a different story... I can't test this scenario now, but is it possible to change app manually within BPF step?

Anyway, in our system we use RUNLOGIC for this task without significant efforts to support a lot of scripts. You will need additional launch script and you can pass $$ and %% variables to the calling script ($$ variables are automatically available to the calling script using the latest version of the RUNLOGIC BADI).

B.R. Vadim