DEVELOPERS FORUM - Creating an Excel Instance in an Aras Method

CycleOp - Thursday, August 5, 2010 3:27 AM:

Hi,

For integration purposes - How do I use something like:  CreateObject("Excel.Application") in either Server or Client side Aras method ? 

This is a general question since I could just as well use this to connect to any other COM compatible component such as an ERP system.

Thanks.

Sagi

Yoann Maingon - Thursday, August 5, 2010 3:46 AM:

hi Sagi,

Watch the Ms Office Light Integration project, you don't have to install it just check the Js method we've built on the client side and you should get your answer.

You're free to post it here then if you want. It's just that i don't have it open on my laptop right now.

Best Regards,

Yoann Maingon

http://www.prodeos.fr

Yoann Maingon - Thursday, August 5, 2010 4:17 AM:

Other thing,

Based on my experience using ActiveX is not always the best choice, plus, you need to modify the security settings to allow them. For example, in our Autocad connector, as Autocad is too big, the Activex launch was failing most of the time. So we made it another way. We created a new object in Aras Flagged for Opening which is a polyitem. And then, Autocad finds it. But we don't start Autocad from Aras. In that case our code is not depending on Internet Explorer, just in case some day Aras becomes available on other browser.

Best Regards,

Yoann Maingon

http://www.prodeos.fr

CycleOp - Thursday, August 5, 2010 4:20 AM:

Thanks Yoann,

Found it - In Javascript you used - var oApplication = new ActiveXObject("Word.Application");

My follow-up question would be - How to do the same with Server Side (VB.NET preferably) - This is for an automated task (like releasing an item and transmitting its details to the ERP system).

Any ideas here ?

Sagi

Yoann Maingon - Thursday, August 5, 2010 4:23 AM:

Well, on the server side you should be able to access APIs?

Let us know what is the ERP you want to target and what are the available interfaces.

Yoann Maingon

http://www.prodeos.fr

CycleOp - Thursday, August 5, 2010 4:30 AM:

I have tried this as a VB.NET server method in Aras : Dim myExcel As Object = CreateObject("Excel.Application") but I get the error - Cannot create ActiveX component 

any ideas ?

Sagi

Yoann Maingon - Thursday, August 5, 2010 4:52 AM:

Activex is definitely a Client Side tool to integrate a Com app in another one.

If you want to run Excel on the server from aras, you first need to import the Excel dll to Aras Innovator, call it in your class and then have something like this:

Dim xlsApp As Excel.Application
xlsApp = New Excel.Application

Yoann Maingon

http://www.prodeos.fr

Brian - Saturday, August 7, 2010 7:30 AM:

Per the Wiki on Consuming Web Services this should be relatively easy to do. Unfortunately so far I have had no real success in getting this to work.

The Excel dll to be using is the "Microsoft.Office.Interop.Excel.dll" which you can get with Visual Studio as this is the way that C++ connects to Excel to use office automation techniques.

Once you have that and have registered it you need to change the 'method-config.xml' in

Aras/InnovatorSolutions/Innovator/Server/Bin directory (basically where Innovator runs from)

Add the excel.dll file into the Referenced Assemblies

  <ReferencedAssemblies>
    <name>System.dll</name>
    <name>System.XML.dll</name>
    <name>System.Web.dll</name>
    <name>System.Data.dll</name>
    <name>$(binpath)/Microsoft.Office.Interop.Excel.dll</name>
    <name>$(binpath)/IOM.dll</name>
    <name>$(binpath)/InnovatorCore.dll</name>
    <name>$(binpath)/CoreCS.dll</name>
    <name>$(binpath)/SPConnector.dll</name>
  </ReferencedAssemblies>

and add the "Imports Microsoft.Office.Interop.Excel" statement.

Depending on the lanuguage you are using you may have to add this in different places though the file.

Imports Microsoft.VisualBasic
Imports System
Imports System.IO
Imports System.Text
Imports System.Xml
Imports System.Collections
Imports System.Collections.Generic
Imports System.Data
Imports System.Net
Imports System.Web
Imports System.Web.SessionState
Imports System.Globalization
Imports Microsoft.Office.Interop.Excel
Imports Aras.IOM

In theory this should be enough.

This code works from VisualStudio

Dim exl As Microsoft.Office.Interop.Excel.Application
Dim wb As Microsoft.Office.Interop.Excel.Workbook

exl = New Microsoft.Office.Interop.Excel.Application
wb = exl.Workbooks.Add( )
exl.visible = True
wb.Activate( )

At this point I am getting the error:

Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80070005.Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80070005.

This seems to be related to permissions on the Interop.Excel.dll and/or the DCOM permissions for launching the application (Excel). So far I have not been able to find a way past this (even giving permission to every user registered on the system did not fix this).

I can imagine another way of doing this. Write a VB or C# DLL to do the actual work that you want and then treat this as a Web Service. Since you are the author of the service you should have more control over how it runs and be able to use the full VisualStudio tools to develop/debug. The Wiki on web services tells you how to install one for use/consumption by Innovator. If I get a chance I will continue to follow this up but would be interested if anyone has any further success on either of these two paths.

Cheers,

Brian.

CycleOp - Sunday, August 8, 2010 2:59 AM:

Thanks Brian,

I saw the security issue pop-up in numerous posts not specifically related to Aras, but that talk about an IIS user executing an external application. If I see that again I'll post the link. 

In the meantime - I have managed to find a way to make this work (as far as I can tell).

Dim p As New System.Diagnostics.Process

p.StartInfo.FileName = "C:ArasConsoleSampleApp SAGI)ConsoleSampleAppinReleaseConsoleSampleApp.exe"

 'Some Args ii you need any '''''p.StartInfo.Arguments = """C:path with spaces to my argumentsmyfile.txt"""

p.StartInfo.UseShellExecute = True

 

p.Start(p.StartInfo)

Basically this worked since all my app this is to create an empty file, but it should work with any other job to do. Excel can be run with no GUI if you set Excel.Application.Visible = False once you create an instance of Excel.Application. 

I guess I would have liked your method better (since you call Excel immediately and not via an external app), and if I stumble on the post that explains how to bypass the security issue - I'll post it here.

Sagi

CycleOp - Sunday, August 8, 2010 4:20 AM:

OK - Found a good enough link to follow in order to get rid of the security error that Brian got - http://blog.crowe.co.nz/archive/2006/03/02/589.aspx. Did not try this for myself and will appreciate it if anyone can check this out and let us know if it works.

Thanks.

Sagi