Automation Excel with ASP.NET
Automation Excel with ASP.NET
Automation Excel with ASP.NET
Automation Excel with ASP.NET
Automation Excel with ASP.NET
Automation Excel with ASP.NET Automation Excel with ASP.NET Automation Excel with ASP.NET Automation Excel with ASP.NET Automation Excel with ASP.NET Automation Excel with ASP.NET Automation Excel with ASP.NET Automation Excel with ASP.NET
Automation Excel with ASP.NET Automation Excel with ASP.NET
Automation Excel with ASP.NET
Go Back  Xtreme Visual Basic Talk > > > Automation Excel with ASP.NET


Reply
 
Thread Tools Display Modes
  #1  
Old 07-14-2007, 04:46 AM
MadMarc MadMarc is offline
Newcomer
 
Join Date: Jul 2007
Posts: 6
Default Automation Excel with ASP.NET


I'm currently coding an ASP.NET Interface for an Intranet website with Visual Basic .NET in Codebehind.
I have to use the Automation Excel to control several xls files.
But I have a problem: the Excel Process doesn't want to close.
Each time a user uses my web application, there is another instance of Excel that opened in the task manager.

I have tried every solution suggested on the Internet and here: Automating Office Programs with VB.Net / COM Interop

But as the application is on a Intranet server, I cannot do a Process.Kill ( It doesn't work on remote computers). All the code of the application work, it is only the several instances of Excel which don't close.

I have tried the GetObject way, but it says that ActiveX can't create the component. I have also changed the ActiveX Properties in the Internet Explorer Security Options but it changed nothing.

Does anyone have a solution? I have spend too many days trying to resolve this problem.

Thanks in advance.

MadMarc

(Sorry if my English isn't perfect, it isn't my mother tongue)
Reply With Quote
  #2  
Old 07-14-2007, 10:59 PM
MKoslof's Avatar
MKoslofAutomation Excel with ASP.NET MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

Well unfortunately Excel Automation on a Web Server is something I always avoid . There are tons of issues revolving around it and you really should review the pros and cons of doing server side Excel Automation.

Review this past thread which should give you some points to chew on:

Create Excel file on web server
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #3  
Old 07-16-2007, 01:22 AM
MadMarc MadMarc is offline
Newcomer
 
Join Date: Jul 2007
Posts: 6
Default

I cannot avoid it now, my work placement is near the end, and the only problem that remains is this one.
There has to be a solution for the GetObject Method at least.
Reply With Quote
  #4  
Old 07-16-2007, 09:35 AM
MKoslof's Avatar
MKoslofAutomation Excel with ASP.NET MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

Well, I understand you have you already implemented a server side approach, but unfortunately I don't know of a good way to get around your problem. Server Side components are meant to be reentrant and scalable. Office is the complete opposite of this.

Regarding GetObject or or GetActiveObject, Office applications only register themselves in the Running Object Table (ROT) if they have gained and lost focus at least one time. If you are doing this in code, timing and focus are going to be big issues for you because the ROT table at the server is going to be a complete crap shoot for you in the first place. Regarding being unable to create the ActiveX object there could be numerous reasons for that problem.

Can you briefly show us the code on how you invoke the server side Excel Object? You might be able to get the application Id and store this id and then try to find the process id later via GetObject().
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #5  
Old 07-17-2007, 01:18 AM
MadMarc MadMarc is offline
Newcomer
 
Join Date: Jul 2007
Posts: 6
Default

All this code is in a method that is launched when the user clicks on a specific button.

I use lbl1 to display the errors or some results.
The paths specified in file1 and file2 are correct, I've verified them several times.

Here is the code as asked:
Code:
Sub updatefunction(ByVal sender As Object, ByVal e As EventArgs)

Dim xlapp As Excel.Application
Dim xlbooks As Excel.Workbooks
Dim xlbook As Excel.Workbook
Dim xlbook2 As Excel.Workbook
Dim xlsheet1 As Excel.Worksheet
Dim xlsheet2 As Excel.Worksheet
Dim ExcelAlreadyRun As Boolean
Dim file1 As String = Server.MapPath("upload/" & file11.Text)
Dim file2 As String = Server.MapPath("upload/" & file22.Text)

Try
xlapp = GetObject(, "Excel.Application")
lbl1.Text = "Existing Process"
ExcelAlreadyRun = False

Catch ex As Exception
xlapp = New Excel.Application
'xlapp = CType(CreateObject("Excel.Application"), Excel.Application)
lbl1.Text = "New Process" & ex.Message
ExcelAlreadyRun = True
End Try

xlapp.Visible = False
xlapp.DisplayAlerts = False
xlapp.UserControl = False

xlbooks = xlapp.Workbooks


If Not (file1 Is Nothing) And Not (file2 Is Nothing) Then
    Try
        xlbook = xlbooks.Open(file1)
        xlbook2 = xlbooks.Open(file2)
        xlsheet1 = xlbook.Sheets(1)

'A lot of code here
'A lot of code here

        xlbook.Close(savechanges:=True)
        xlbook2.Close(savechanges:=True)

    Catch exc As Exception
    lbl1.Text = "Files validated are not conventional" & exc.Message
    
    'To try to close all the Excel Process
    Finally

        'Garbage Collector
        System.GC.Collect()
        System.GC.WaitForPendingFinalizers()
        System.GC.Collect()
        System.GC.WaitForPendingFinalizers()

        If Not (xlbook Is Nothing) Then
        ReleaseComObject(xlbook)
        xlbook = Nothing
        End If

        If Not (xlbook2 Is Nothing) Then
        ReleaseComObject(xlbook2)
        xlbook2 = Nothing
        End If


        If Not (xlbooks Is Nothing) Then
            xlbooks.Close()
            ReleaseComObject(xlbooks)
            xlbooks = Nothing
        End If

        If Not (xlsheet1 Is Nothing) Then
            ReleaseComObject(xlsheet1)
            xlsheet1 = Nothing
        End If

        If Not (xlsheet2 Is Nothing) Then
            ReleaseComObject(xlsheet2)
            xlsheet2 = Nothing
        End If

        If Not (xlapp Is Nothing) Then
            xlapp.Quit()
            ReleaseComObject(xlapp)
            xlapp = Nothing
        End If


    ' End of the main "Try Catch Finally"
    End Try

'End of the "If" that check the files
End If

End Sub
I don't use System.Diagnostics.Process.GetProcesses anymore because I didn't find the way to get around the Access Denied message when it is launched on the server.
Reply With Quote
  #6  
Old 07-19-2007, 09:38 AM
MKoslof's Avatar
MKoslofAutomation Excel with ASP.NET MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

1) Just for clean up, avoiding re-entry for no reason you only have to call GC.Collect and WaitForPendingFinalizers once, not twice back to back

Besides that how you are trying to clean up your COM objects, from a .Net CLIENT perspective seems fine, besides one thing. If you are using .Net 2.0 you would use Marshal.FinalReleaseComObject. However, if you are using .Net 1.1 and ReleaseComObject you might need to run this in a loop until the COM obj count is 0. This is one of the reasons the FinalReleaseComObject method was introduced in 2.0.

Also I can think of tons of pain points and issues with trying to do this:

xlapp = GetObject(, "Excel.Application")

My advice is don't . Either way xlApp should be a new COM instance and then you do release mechanics on that xlapp instance and the Excel objects you have instantiated within that application instance. There is no way to guarantee that the instance you are trying to hook into via GetObject() is one the NETWORK SERVICE account or ASP.net Identity created and there is no way to get that information either. Running GetObject on your web server is just asking for trouble.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #7  
Old 07-23-2007, 04:09 AM
MadMarc MadMarc is offline
Newcomer
 
Join Date: Jul 2007
Posts: 6
Default

Thanks for the reply MKoslof, could you please me give an example about this fact: "However, if you are using .Net 1.1 and ReleaseComObject you might need to run this in a loop until the COM obj count is 0. "
Indeed, I am using the .Net FrameWork 1.1.
Reply With Quote
  #8  
Old 07-23-2007, 12:05 PM
MKoslof's Avatar
MKoslofAutomation Excel with ASP.NET MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

Code:
If Not comObject Is Nothing Then
  Do
    If System.Runtime.InteropServices.Marshal.ReleaseComObject(comObject) = 0 Then
      Exit Do
    End If
  Loop
End If
And I suggest some readings:

http://msdn2.microsoft.com/en-us/lib...comobject.aspx
http://blogs.msdn.com/cbrumme/archiv.../16/51355.aspx
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #9  
Old 07-25-2007, 07:11 AM
MadMarc MadMarc is offline
Newcomer
 
Join Date: Jul 2007
Posts: 6
Default

Thanks for reply and the links. I've tested with a loop as you showed me, but the Excel still hangs up.
It seems a problem without solution.
Reply With Quote
  #10  
Old 07-25-2007, 09:31 AM
mdent mdent is offline
Centurion
 
Join Date: Jun 2006
Location: Teesside, UK
Posts: 108
Default

Maybe not a solution to use if you are at the end of a project, but I use OLEDB with Excel and treat the Excel sheets as databases, but again there are a whole host of problems with doing it this way - at least you can close your connections though!

try http://zamov.online.fr/EXHTML/ASPNET/ASPNET3.html to get started and
http://www.dotnetjohn.com/articles.aspx?articleid=54 for some sample code, they were just the first few hits I had in Google - I'm sure you can find more if you look further.
__________________
"It compiles! Ship it!"
Reply With Quote
  #11  
Old 07-25-2007, 09:32 AM
MKoslof's Avatar
MKoslofAutomation Excel with ASP.NET MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

Well, again, this goes back to the inherit problems with doing Excel Automation at the server.

Another approach could be to use the System.Process class and get all currently running Excel Prog Ids and then after you instantiate your "new" vesion of excel get that prog id and basically figure out which version of Excel is the one you are running. Then in a finally construct (try/catch/finally) call the Process.Kill() method passing in the Prog Id of the instance of Excel you created.

Of course, the problem with this approach, and why it is better suited for WinForm or client based applications is the permissions aspect of the Process class from the server, and I'm not sure off hand how you would get around that, if even possible from the Web Server
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #12  
Old 07-27-2007, 01:30 AM
MadMarc MadMarc is offline
Newcomer
 
Join Date: Jul 2007
Posts: 6
Default

Thanks all for your reply.
As I said Process.Kill doesn't work on remote computers.

I have opted for a vbscript that launched itself on the server everyday after the workhours.
It is not the cleanest way to do it, but it works, and I don't have time for anything else.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Automation Excel with ASP.NET
Automation Excel with ASP.NET
Automation Excel with ASP.NET Automation Excel with ASP.NET
Automation Excel with ASP.NET
Automation Excel with ASP.NET
Automation Excel with ASP.NET Automation Excel with ASP.NET Automation Excel with ASP.NET Automation Excel with ASP.NET Automation Excel with ASP.NET Automation Excel with ASP.NET Automation Excel with ASP.NET
Automation Excel with ASP.NET
Automation Excel with ASP.NET
 
Automation Excel with ASP.NET
Automation Excel with ASP.NET
 
-->