Purpose of the document
If your going to create BPC 10 or BPC 10.1 reports and input schedules using the EPM API for several functions like refreshing sheets or saving data you may realize that the EPM throws a VBA error if you try to use methods from the EPM API. The reason for this is the missing FPMXLCLient reference. The same behavior can be experienced if users of the reports or input schedules execute them for the first time unless they activate the reference in the VBA editor. To avoid this behaviour the following guide will provide you a possible solution.
Step by Step Procedure
Activating FPMXLClient
If you are going to create an input schedule or report in BPC using the EPM Add-In and you plan the usage of the VBA API provided you have to enable the FPMXLClient reference. You can achive this by open the VBA editor (press ALT - F11 or simply activate the developer tools in the MSExcel options) and choose from menu Extras --> Reference ... . Activate the FPMXLClient reference by setting a flag shown in the following screen and press OK:
After doing this you are able to use the EPM classes and methods within VBA.
Caution:
Any user who is going to use an input schedule or report which includes elements of the EPM API will have to activate the FPMXLClient reference. Otherwise VBA errors will occur during the usage of the linked functionalities.
Creating VBA
After activating the reference you will have to create a VBA coding which checks and enables the reference dynamically anytime the input schedule or the report is executed - indepentent from user or machine.
1. Create Module
The first you will have to do is creating a module within your VBA project. Simply right click on your VBAProject and choose Insert --> Module.
2. Create VBA Coding
Copy the following VBA code in your newly created module:
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Private Const cVerweisname As String = "FPMXLClient"
Private Const cEPMDateiname As String = "C:\Program Files (x86)\SAP BusinessObjects\EPM Add-In\FPMXLClient.tlb"
Public var As Boolean
' Function - check if FPMXLClient has loaded.
' Return Value:
' true: if loaded
' false: if not loaded
Private Function EPMCheckReference() As Boolean
Dim lReturn As Boolean
Dim lVerweisname As String
lReturn = False
Set VBE = Application.VBE.ActiveVBProject
With VBE
For x = 1 To .References.Count
If UCase(.References(x).Name) = UCase(cVerweisname) Then
lReturn = True
End If
Next x
End With
EPMCheckReference = lReturn
End Function
' Function to load FPMXLClient
' Return Value:
' true: Loading OK
' false: Loading not OK
Private Function EPMLoadReference() As Boolean
Dim lReturn As Boolean
lReturn = False
On Error Resume Next
Application.VBE.ActiveVBProject.References.AddFromFile cEPMDateiname
lReturn = True
EPMLoadReference = lReturn
End Function
' Function - check if user has logged on
' Return Value:
' true: logged on
' false: not logged on
Private Function CheckEPMConnection() As Boolean
Dim lString As String
lString = ""
Dim epm As New FPMXLClient.EPMAddInAutomation
On Error Resume Next
lString = epm.GetActiveConnection(ActiveSheet)
If lString <> "" Then
lBoolean = True
Else
lBoolean = False
End If
CheckEPMConnection = lBoolean
End Function
' Function - check if FPMXLClient has loaded.
' If not - load.
' Check if active connection exists.
' Return Value:
' - true: FPMXLClient loaded and active connection
' - false: FPMXLClient loaded, but no connection (Comment: Info- Dialogue will pop up)
Public Function EPMActivate()
Dim lBoolean As Boolean
lBoolean = EPMCheckReference()
If lBoolean = False Then
lBoolean = EPMLoadReference()
End If
If lBoolean And CheckEPMConnection Then
lBoolean = True
Else
MsgBox "No active EPM Connection." & Chr(13) & "Please log in again.", vbInformation, "Caution"
lBoolean = False
End If
EPMActivate = lBoolean
End Function
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Now use this functionality within your functions and macros. Please see the example for refreshing the sheet below:
----------------------------------------------------------------------------------------------------------------------------------------------------------------
' Function - create refresh
Public Function runEPMRefreshWorkSheet()
Dim epm As New FPMXLClient.EPMAddInAutomation
epm.RefreshActiveSheet
End Function
' Macro for calling the refresh function
' To be used wihtin the sheet for buttons and more
' Make sure the EPMActivate reference has included
Sub EPM_RefreshWorkSheet()
If EPMActivate Then
runEPMRefreshWorkSheet
End If
End Sub
----------------------------------------------------------------------------------------------------------------------------------------------------------------
After including the coding referencing EPMActivate in each macro anytime a user executes a macro the FPMXLClient reference will be checked and reloaded if required. No manual activating the reference will be needed anymore.
Best regards,
Karsten
- SAP Managed Tags:
