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?
-
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 ObjectSub 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