Trigering Essbase Macro with C#

Question

I'm trying to automatize some Excel reports. Currently I need to retrieve some data from an Essbase Server, in order to achieve this I've created a macro to retrieve and set data in an Excel sheet, my VBA code is the following:

Option Explicit
Declare Function EssVRetrieve Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal range As Variant, ByVal lockflag As Variant) As Long
Declare Function EssVConnect Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal userName As Variant, ByVal password As Variant, ByVal server As Variant, ByVal application As Variant, ByVal database As Variant) As Long
Declare Function EssVDisconnect Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant) As Long

Sub Essbase_Update_Pulls()
Dim rangeString As String
rangeString = "B3:AC5033"
MsgBox ("Starting macro")
Dim wbSrc As Workbook
Dim m As Variant
Dim mySheetname As Variant, myUserName As Variant, myPassword As Variant, myServer, myApp As Variant, myDB As Variant
Dim lockflag As Integer
Dim myrng As range
Dim x As Integer
Dim y As Integer
Dim z As Integer
Dim strMsgTxt As String
Dim blnRetVal As Boolean

Set wbSrc = ActiveWorkbook

Set myrng = range(rangeString)

lockflag = 1
MsgBox ("Data set")
        mySheetname = "Sheet"
        myServer = "Server"
        myApp = "App"
        myDB = "DB"
        myUserName = "User"
        myPassword = "Pass"

MsgBox ("Trying connection")
        x = EssVConnect(mySheetname, myUserName, myPassword, myServer, myApp, myDB)
        MsgBox (CStr(x))
        If x < 0 Then
           blnRetVal = False
           strMsgTxt = "Essbase Login - Local Failure"
           MsgBox (strMsgTxt)
        ElseIf x > 0 Then
           blnRetVal = False
           strMsgTxt = "Essbase Login - Server Failure"
           MsgBox (strMsgTxt)
        Else
           blnRetVal = True
           strMsgTxt = "Success"
           MsgBox ("Connection Succeeded")
           y = EssVRetrieve(mySheetname, myrng, lockflag)
            If y = 0 Then
                MsgBox ("Retrieve successful.")
                z = EssVDisconnect(mySheetname)
                If z = 0 Then
                    MsgBox ("Disconnect Succeed.")
                    Else
                    MsgBox ("Disconnect failed.")
                End If
                Else
                MsgBox ("Retrieve failed.")
            End If
        End If
End Sub

Variable x is supposed to return the status code (0 is success any other is failed). So here comes the trick, whenever I run this macro within Excel it runs perfectly, however when I call it from C# using xlApp.Run("Essbase_Update_Pulls"); it returns a status code of -3. Doing some research I found out that whenever an Excel Application is created in code it doesn't have the add-ins loaded, so they have to be manually loaded https://community.oracle.com/thread/2480398 . I iterated over the xlApp.AddIns and found that the "essexcln.xll" was correctly installed so I have no idea what to do now. Also I found out that Add-Ins can be added during runtime but this just causes an exception, here is the source: http://www.network54.com/Forum/58296/thread/957392331/Visual+Basic-Excel+Api+call+to+Essbase

Answer 1

Found out that excel isn't loading all the dll's and xll's required to connect to de Essbase server. In order to make it work it is necessary to start excel as a process and the acquire the instance and relate it to the interop class. I found the solution here:
Excel interop loading XLLs and DLLs.
The user pretty much had the same problem but with Bloomberg. I would just add that in the SearchExcelInterop method it needs a Thread.Sleep() to wait for Excel to load properly, in my case it threw a StackOverflowException.