Skip to Content

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:

Ref.png

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

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply