Results 1 to 13 of 13
  1. #1
    Lounger
    Join Date
    Jan 2007
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Run a Excel module from ASP.NET (2003)

    I'm not sure how familiar you are with Hyperion Essbase, but I am trying to utilize the API in ASP.NET.
    I have an excel workbook that connects to the database and retrieves the data I ask for. I would like to use ASP.NET to launch excel and to run that particular module. I put something together, but it generates the following error message when the module is called:

    "The server threw an exception. (Exception from HRESULT: 0x80010105 (RPC_E_SERVERFAULT))"

    Here's what I have in ASP.NET button action.

    ************************************************** ***************
    strFileName = "C:TempBook1.XLS"
    xlWorkBook = xlApplication.Workbooks.Open(strFileName, False, False)
    xlApplication.Visible = True
    xlWorkBook.Sheets("Sheet1").Activate()

    'ERROR HAPPENS HERE
    xlApplication.Run("'" & strFileName & "'!Module1.essbaseRetrieve")

    ************************************************** ****************************
    and the essbaseRetrieve module in Book1.xls is:

    ************************************************** ***

    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
    Declare Function EssMenuVRetrieve Lib "ESSEXCLN.XLL" () As Long

    Sub essbaseRetrieve()
    'Connect to essbase server and retrieve data

    User = ""
    Password = ""
    Server = ""
    AppName = ""
    DbName = ""

    'Connect
    sts = EssVConnect("Sheet1", User, Password, Server, AppName, DbName)
    'Retrieve
    x = EssMenuVRetrieve()
    'Disconnect
    sts = EssVDisconnect(Null)

    End Sub

    ************************************************** **

    Any help is greatly appreciated.

    Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Run a Excel module from ASP.NET (2003)

    I know nothing about essbase at all, but does it work if you omit the path and filename:
    <code>
    xlApplication.Run "essbaseRetrieve"
    </code>
    Assuming that the name essbaseRetrieve is unique (in the open workbooks), you don't need to specify the name of the workbook, nor that of the module.

  3. #3
    Lounger
    Join Date
    Jan 2007
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run a Excel module from ASP.NET (2003)

    No luck. I get a message saying that the module can not be found. The syntax I have for calling the moudle is correct, but I believe its tyring to run something that is not loaded.

    Essbase is an excel add-in that we use to connect to our database and retrieve information. I noticed that when the workbook is opened the addin is not loading properly. Is there a way to force the add-in to load before calling the module?

    Regards

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Run a Excel module from ASP.NET (2003)

    I don't know if this will work:

    Dim xlAddIn As Object
    For Each xlAddIn In xlApplication.AddIns
    If xlAddIn.Name = "Essbase.xla" Then
    xlAddIn.Installed = True
    Exit For
    End If
    Next xlAddIn
    Set xlAddIn = Nothing

    Substitute the appropriate name for the Essbase add-in.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Run a Excel module from ASP.NET (2003)

    I found several recommendations in newsgroups to use EssBase Objects, or Hyperion Objects as it now seems to be called.. Apparently, this lets you create/populate a spreadsheet without using Excel itself. It might be worth lloking into.

  6. #6
    Lounger
    Join Date
    Jan 2007
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run a Excel module from ASP.NET (2003)

    I read about Hyperion Objects but I didn't want to rewrite the code that I have in my Excel workbook.
    I ended up doing the following and it worked locally, but once uploaded to the server it crashes.

    xlApplication.AddIns.Item("Hyperion Essbase OLAP Server DLL").Installed = False
    xlApplication.AddIns.Item("Hyperion Essbase OLAP Server DLL").Installed = True

    On the server I get the following error when it's trying to reset the addin:

    "Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX)) "

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Run a Excel module from ASP.NET (2003)

    Can the following be done from ASP.Net?
    - Use Shell or ShellExecute to start Excel as if it was started interactively - the Essbase add-in should load normally.
    - Use GetObject instead of CreateObject to get hold of the already running instance of Excel.

  8. #8
    Lounger
    Join Date
    Jan 2007
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run a Excel module from ASP.NET (2003)

    I'm slowly getting to work. For some reason on my server it wasn't recognizing the addin. So when I was trying to restart it was giving me an index error because it didn't see it. So I included in the code to add it:

    xlApplication.AddIns.Add("C:HyperionEssbaseBinesse xcln.xll")

    It works fine now. But I looked into runing a batch file from ASP.NET and it's doable. Here's what I got:

    wshell = CreateObject("WScript.Shell")
    wshell.run("c:Tempfile.bat")
    wshell = Nothing

    In the "file.bat" file I start excel. Once it starts I use sub Auto_Open() to do what I want. The only problem I have there is I can't get a hold of the excel process to terminate it. In the procedure I have Application.Quit at the end, but the EXCEL.EXE is still visible in the task manager processes.

    Is there a way to terminate excel process by getting hold of it's process id or handle once it's created?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Run a Excel module from ASP.NET (2003)

    You should be able to use GetObject:

    Set xlApplication = GetObject(, "Excel.Application")
    xlApplication.Quit

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Run a Excel module from ASP.NET (2003)

    Oh, and make *absolutely* sure that you use your Excel.Application reference (or an object derived from it) for all Excel code. If you use something like

    Sheets("Sheet1").Range("A1")

    instead of

    xlApplication.Workbooks(1).Sheets("Sheet1").Range( "A1")

    you'll create a second instance of Excel that will remain in memory after xlApplication has been destroyed.

  11. #11
    Lounger
    Join Date
    Jan 2007
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run a Excel module from ASP.NET (2003)

    It still leaves the process running. Here's what I have:

    In the ASP.Net button action I have:

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim wshell As Object

    wshell = CreateObject("WScript.Shell")
    wshell.run("c:Tempfile.bat")
    wshell = Nothing

    End Sub

    The file.bat is calling Book2.xls and running the Auto_Open() procedure which calls "essbaseRetrieve"

    Sub essbaseRetrieve()
    User = ""
    Password = ""
    Server = ""
    AppName = ""
    DbName = ""
    sts = EssVSetGlobalOption(6, False)
    sts = EssVConnect(Null, User, Password, Server, AppName, DbName)
    Application.Workbooks(1).Sheets("Sheet1").Activate
    x = EssMenuVRetrieve()
    sts = EssVDisconnect(Null)
    sts = EssVSetGlobalOption(6, True)
    Application.Quit

    End Sub

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Run a Excel module from ASP.NET (2003)

    This kind of problem is difficult to investigate at first hand, let alone from a distance.
    Shouldn't the workbook be saved, or is that done by one of the API calls?

  13. #13
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run a Excel module from ASP.NET (2003)

    <hr>I ended up doing the following and it worked locally, but once uploaded to the server it crashes.<hr>
    Are you saying Excel is run on the server? Be aware, that Excel is NO server application and that if this is something multiple users are going to use you may get into trouble of Excel applications remain "running" on the server. When a certain number of open Excel apps is reached (dunno how many), your server may stall (or at best become very unresponsive).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •