EPM Add-In DLLs

[former_member461591]

I have an existing input form with VBA that has been working reliablyy on the EPM Add-In for BPC V25. We have just installed a PC with Analysis for Office 2.4.2 and this comes with the EPM Add-in as an included component. There are three dlls

• EPMOfficeActiveX.dll
• FPMXLClient.dll
• FPMXLClient.OLAPUtilities.dll

that usually reside in C:\Program Files (X86) SAP BusinessOnbjects\EPM-AddIn\. On the new installation they reside in a different folder

C:\Program Files (X86) SAP BusinessObjects\OfficeAddin\Plugsins\EPM-AddIn

.

As a result the code no longer runs and throws an error saying the dlls can't be found. There is a SAP note that describes a solution but it is incorrect. Can someone describe how to make the Analysis for Office version of the EPM Add-In find the DLLs.

Thanks

Cliff

[former_member186338 · Accepted solution]

You can manually change references in the VBA editor...

Or you can change references in program code.

Or you can use late binding:

Set api = Application.COMAddIns("FPMXLClient.Connect").Object

[former_member461591 · Accepted solution]

Hi Vadim

Thank you for your reply. My VBA skills are limited so I have a few basic questions.

As this code must continue to run on other machines with the standard EPM Add-In I don't believe I can change the code.

When you say "You can manually change references in the VBA editor" do you mean the menu Tools/References?

If I go into this I can see the reference pointing at the "old" EPM Add-In location rather than the "new" Analysis for Office Location resulting in a "missing" error for the three dlls. I tried physically moving the files to the old location but this did remove the missing error. I also tried to browse and point to the new location. This did not appear to change anything.

I also read somewhere I could use RegSvr32 to register the dlls. However this aplies to COM dlls and none of these three are COM.

I hope you with excuse my ignorance on the topic but I really need more detail on how to fix the installation so existing VBAs will continue to work.

Thanks again

Cliff

[former_member186338 · Accepted solution]

When you open file in AO - do you see 2 lines in referencies for FPMXClient?

[former_member461591 · Accepted solution]

Hi Vadim,

Under Tools / References I see the two lines but they read "MISSING: FPMXL Client OlapUtilities" . For each the location says points at the location used by the EPM Add-In and not the location used by AO.

As an experiment on another machine, I moved the files to another location, did a cold boot and reopened Excel. Without starting the EPM Add-In I then went into VBA and it was still pointing at the original location. From this I conclude:

• the location is not loaded at the time the EPM starts up
• the location is not discovered by searching default directories
• the location is remembered based on a setting, probably set during installation.

So what probably happens is the location is stored somewhere in the registry file. If this is the case I'll need to locate it so I can correct it.

Cheers

Cliff

[former_member186338 · Accepted solution]

Looks like you have issues with installation. Perform complete uninstall and reinstall.

Sample code to change references using VBA:

Public Sub CorrectRef()
Dim chkRef As VBIDE.Reference
Dim strepmref As String
Dim blncorref As Boolean


strepmref = ""
If Dir(Environ("LOCALAPPDATA") & "\Programs\SAP BusinessObjects\EPM Add-In\FPMXLClient.Shim.dll") <> "" Then
    'AO is installed
    strepmref = Environ("LOCALAPPDATA") & "\Programs\SAP BusinessObjects\EPM Add-In\FPMXLClient.Shim.dll"
ElseIf Dir(Environ("ProgramFiles") & "\SAP BusinessObjects\EPM Add-In\FPMXLClient.Shim.dll") <> "" Then
    'EPM standalone is installed
    strepmref = Environ("ProgramFiles") & "\SAP BusinessObjects\EPM Add-In\FPMXLClient.Shim.dll"
End If

If strepmref <> "" Then
    ' Remove reference if incorrect
    blncorref = False
    
    For Each chkRef In ThisWorkbook.VBProject.References
        If chkRef.Name = "FPMXLClient" Then
            If chkRef.FullPath <> strepmref Then
                ThisWorkbook.VBProject.References.Remove chkRef
            Else
                blncorref = True
            End If
        End If
    Next
    ' Set reference
    If Not blncorref Then
        ThisWorkbook.VBProject.References.AddFromFile strepmref
        ThisWorkbook.Save
    End If
End If

End Sub

[arun_varghese · Accepted solution]

Hi Vadim,

I am working on EPM Add-In SP34.

When I move the templates I developed to a system having AO2.7 with EPM Add-In and I run my reports, I get similar error :
'MISSING FPMXLClient'.
Can I use the above piece of code to add the correct references based on whether it is a Sand-alone EPM Add-In or AO Plugin. If so, where do I need to place this code. Do we need to invoke in Workbook Open event?

[former_member186338 · Accepted solution]

2 arun.varghese

Use universal code with late binding: https://answers.sap.com/questions/528270/using-epm-addin-api-from-vbscript.html

[former_member186338 · Accepted solution]

2 arun.varghese

New version of universal code - without any references:

Public Sub SubmitRefreshData()

Dim objAddIn As COMAddIn
Dim epm As Object
Dim AOComAdd As Object
Dim blnEPMInstalled As Boolean
Dim lngTemp As Long
Dim strSheet As String
Dim submres() As Object

For Each objAddIn In Application.COMAddIns
    If objAddIn.progID = "FPMXLClient.Connect" Then
        Set epm = objAddIn.Object
        blnEPMInstalled = True
        Exit For
    ElseIf objAddIn.progID = "SapExcelAddIn" Then
        Set AOComAdd = objAddIn.Object
        Set epm = AOComAdd.GetPlugin("com.sap.epm.FPMXLClient")
        blnEPMInstalled = True
        Exit For
    End If
Next objAddIn

If Not blnEPMInstalled Then
    MsgBox "EPM is not installed!"
    Exit Sub
End If

epm.SetSilentMode True

strSheet = "abc"
submres = epm.SubmitAndRefreshWorkSheet(ThisWorkbook.Worksheets(strSheet))
For lngTemp = 0 To UBound(submres)
    Debug.Print strSheet & " " & CStr(submres(lngTemp).StatusDescr) & " " & CStr(submres(lngTemp).SubmitCount)
Next lngTemp

End Sub