用C#测试Essbase宏

Question

我正在尝试自动化一些Excel报告。目前，我需要从Essbase Server检索一些数据，为了实现这一点，我创建了一个宏来检索和设置Excel表中的数据，我的VBA代码如下：

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

变量x应该返回状态代码(0表示成功，任何其他变量都失败)。因此，这里有一个窍门，每当我在Excel中运行这个宏时，它就会完美地运行，但是当我从C#使用xlApp.Run("Essbase_Update_Pulls");调用它时，它返回的状态代码是-3。通过一些研究，我发现，每当用代码创建Excel时，它都没有加载外接程序，因此必须手动加载https://community.oracle.com/thread/2480398。我遍历了xlApp.AddIns，发现"essexcln.xll“已经正确安装，所以我现在不知道该做什么。此外，我还发现可以在运行时添加外接程序，但这只会导致异常，下面是源代码：http://www.network54.com/Forum/58296/thread/957392331/Visual+Basic-Excel+Api+call+to+Essbase。

Answer 1

发现excel并没有加载连接到de服务器所需的所有dll和xll。为了使其工作，有必要启动excel作为一个进程，并获取实例并将其与interop类关联。我在这里找到了解决办法：

Excel interop loading XLLs and DLLs。

用户也有同样的问题，但在彭博社。我想补充的是，在SearchExcelInterop方法中，它需要一个Thread.Sleep()来等待Thread.Sleep()正确加载，在我的例子中，它抛出了一个StackOverflowException。