EPM Summit Network Forum

Find answers, ask questions, and connect with our
community around the world.

Landing Page Forums SAP Business Planning and Consolidation (BPC) Reporting Is relying on VBA for reporting requirements a sensible idea? Reply To: Is relying on VBA for reporting requirements a sensible idea?

  • Deb Silverman, Report Coach

    Moderator
    February 24, 2021 at 8:34 AM
    13

    VBA has become an integral part of EPM solutions, for without we wouldn’t be able to fully harness some truly powerful API’s that come with the tool. Automation has become far more accessible than ever before!

    I really like how you handled the validations – we don’t want to waste time or lose our input if something was overlooked. I sometimes use the standard BPC function BEFORE_SAVE() so the data doesn’t get wiped out if there are any events following the save. If i were to use your code it would look something like this for both MS and NW versions (you can copy paste this code and it will work as it is – the object declares go at the top of the module):

    Option Explicit

    Public api As Object
    Public z As Object

    Sub SaveAndRefreshData()

    On Error Resume Next

    If Range(“SAVE_VAL”) = “OK” Then

    Set api = Application.COMAddIns(“SapExcelAddIn”).Object

    If Not api Is Nothing Then

    Set z = api.GetPlugin(“com.sap.epm.FPMXLClient”)

    Else

    Set z = Application.COMAddIns(“FPMXLClient.Connect”).Object

    End If

    z.SaveAndRefreshWorksheetData

    Else

    MsgBox “Save validations are not met, please correct your input”

    Exit Sub

    End If

    ‘…. may have more code here??

    End Sub