Excel Automation Issue

[danasegarane]

Hi all,
I am trying to automate excel. And creating excel object as

oExcel = CreateObject("Excel.Application")

And trying to close/kill(Kill ) the Excel in Process window using

 For Each P as Process In Process.GetProcessByName("Excel") 
    if p.MainWindowHandle.ToInt32 == oExcel )
     p.kill
   end if
 Next

But the P.MainWindowHandle.ToInt32 is always 0

I have tried this one , Which works for simple methods and not for complex methods

[cicatrix]

CreateObject does NOT return a window handle!
It returns a reference to your object.

Use FindWindow API:

<DllImport("user32.dll")> Public Shared Function _
         FindWindow(ByVal strClassName As String, ByVal strWindowName _
             As String) As IntPtr
    End Function

And closing Excel like that is awfully wrong and may lead to memory leaks (do remember that it's built on COM technology).

To close your excel instance you should use:

oExcel.Quit method (option strict must be off to allow late binding).

If you want excel interoperability download Microsoft.Office.Interop assemblies pack to do it properly.

For Office 2003:
http://www.microsoft.com/downloads/d...displaylang=en

For Office 2007:
http://www.microsoft.com/downloads/d...displaylang=en

[danasegarane]

Thanks for the post cicatrix. If I use the Findwindow method I have to give the window name. It there are more request from server then all the Window name will be same ?

[cicatrix]

Thanks for the post cicatrix. If I use the Findwindow method I have to give the window name. It there are more request from server then all the Window name will be same ?

On the second thought you wouldn't probably know the exact window title. Only that it starts with 'Microsoft Excel'

Here's the article that covers the windows enumeration:
http://support.microsoft.com/kb/183009

But I still advise you not to kill Excel like that.
Use its native Quit method.

And one more important thing:
If you create an Excel instance with CreateObject it won't have a window at all unless you do:
oExcel.Visible = True

[koolprasad2003]

why don't you use following code

        Dim oXL As Excel.Application
        Dim oWB As Excel.Workbook
        Dim oSheet As Excel.Worksheet

it is easy to close and kill EXCEL object

        oSheet = Nothing
        oWB.Close()
        oWB = Nothing
        oXL.Quit()
        oXL = Nothing
System.GC.Collect()
System.GC.WaitForPendingFinalizers()

[cicatrix]

Probably because he doesn't have Microsoft.Office.Interop.Excel assembly installed.

[Siddharth Rout]

And trying to close/kill(Kill ) the Excel in Process window using

I would suggest you not to use this method but close it the proper way.

Here is an example.

Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles Button1.Click

        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet

        xlApp = New Excel.ApplicationClass
        
        '~~> Opening existing  workbook
        xlWorkBook = xlApp.Workbooks.Open("c:\MyFile.xlsx")
        '~~> Working with the first Sheet
        xlWorkSheet = xlWorkBook.Worksheets("sheet1")
        
        '~~> Writing to a cell A1
        xlWorkSheet.Cells(1, 1) = "danasegarane"
        
        '~~> Closing the workbook
        '~~> Need to include one more line if you want to save it via code
        xlWorkBook.Close()
        xlApp.Quit()
        
        '~~> Clean Up
        releaseObject (xlApp)
        releaseObject (xlWorkBook)
        releaseObject (xlWorkSheet)

    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject (obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

End Class

[cicatrix]

One addition:

xlWorkBook.Close(SaveChanges:=False)

Or else there will be a prompt for saving changes.

[danasegarane]

I would suggest you not to use this method but close it the proper way.

Here is an example.

Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles Button1.Click

        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet

        xlApp = New Excel.ApplicationClass
        
        '~~> Opening existing  workbook
        xlWorkBook = xlApp.Workbooks.Open("c:\MyFile.xlsx")
        '~~> Working with the first Sheet
        xlWorkSheet = xlWorkBook.Worksheets("sheet1")
        
        '~~> Writing to a cell A1
        xlWorkSheet.Cells(1, 1) = "danasegarane"
        
        '~~> Closing the workbook
        '~~> Need to include one more line if you want to save it via code
        xlWorkBook.Close()
        xlApp.Quit()
        
        '~~> Clean Up
        releaseObject (xlApp)
        releaseObject (xlWorkBook)
        releaseObject (xlWorkSheet)

    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject (obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

End Class

I tried this before. And the thing is that I have 30 sheets in a WorkBook and trying to copy to another workbook cause the excel not closing from task list. Let me try once again

[cicatrix]

Do you invoke Quit method?

And you should either save your changes using Save method
And use Workbook.Close method with parameters SaveChanges

[koolprasad2003]

I would suggest you not to use this method but close it the proper way.[/CODE]

exactly koolsid, by KILL method other excel object could get killed.....

so better to get reference of

Microsoft.Excel.Introp

library
and use the Application and Worksheet object. it has the saparate methods for Closing the EXCEL object

[danasegarane]

Do you invoke Quit method?

And you should either save your changes using Save method
And use Workbook.Close method with parameters SaveChanges

Yes I called that method ..

From MSDN :

When you automate a Microsoft Office application from Microsoft Visual Basic .NET or Microsoft Visual C# .NET, the Office application does not quit when you call the Quit method.

[danasegarane]

I was testing with this one.

Private oexcel As Excel.Application
    Dim oSourceWorkBooks_1 As Excel.Workbooks
    Dim oSourceWorkBook1 As Excel.Workbook
 
    Dim oDestinationWorkBooks_1 As Excel.Workbooks
    Dim oDestinationWorkBook_1 As Excel.Workbook
    Dim oSourceWorkSheets_1 As Excel.Sheets
 
    Dim oDataBaseSheet As Excel.Worksheet
    Dim oTemplateSheet As Excel.Worksheet
    Dim oDestinationWorkSheets_1 As Excel.Sheets
    Dim oDestinationWorkSheet_1 As Excel.Worksheet
 
 
    Sub New(ByVal file As String)
        Dim filename As String = Path.Combine(Path.GetTempPath, Path.GetTempFileName)
        Dim _filename As String = ""
        Dim b As Excel.Workbook
 
        oexcel = CreateObject("Excel.Application")
 
        oexcel.Visible = True
        oexcel.DisplayAlerts = False
        oDestinationWorkBooks_1 = oexcel.Workbooks
        oDestinationWorkBook_1 = oDestinationWorkBooks_1.Add
        oSourceWorkBooks_1 = oexcel.Workbooks
 
        oSourceWorkBook1 = oSourceWorkBooks_1.Open(file)
 
        oSourceWorkSheets_1 = oSourceWorkBook1.Sheets
        oTemplateSheet = oSourceWorkSheets_1("Template")
        oDataBaseSheet = oSourceWorkSheets_1("Database")
 
        oDestinationWorkSheets_1 = oDestinationWorkBook_1.Sheets
        oDestinationWorkSheet_1 = oDestinationWorkSheets_1("Sheet1")
 
      'Copy sheets from Book1 to 2
       [B][U] For index As Integer = 1 To 10
 
            oTemplateSheet.Copy(oDestinationWorkSheet_1)
        Next[/U][/B]
 
        'End With
        'oSourceWorkBook1 = oSourceWorkBooks1.Open(file)
        marshal(oTemplateSheet)
        marshal(oDataBaseSheet)
        marshal(oSourceWorkSheets_1)
        oSourceWorkSheets_1 = Nothing
        oDataBaseSheet = Nothing
        oTemplateSheet = Nothing
 
        oSourceWorkBook1.Close()
        marshal(oSourceWorkBooks_1)
        marshal(oSourceWorkBook1)
        oDestinationWorkBooks_1.Close()
        marshal(oDestinationWorkBooks_1)
        marshal(oDestinationWorkBook_1)
        oexcel.Quit()
        marshal(oexcel)
        System.IO.File.Delete(filename)
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(oexcel)
 
 
 Private Sub marshal(ByVal o As Object)
        If Not o Is Nothing Then
            System.Runtime.InteropServices.Marshal.ReleaseComObject(o)
            o = Nothing
        End If
    End Sub

It was releasing correctly upto the marked . While I try to copy sheets from WorkBook1 to WorkBook2 it was not releasing after adding that copy method

[Siddharth Rout]

I tried this before. And the thing is that I have 30 sheets in a WorkBook and trying to copy to another workbook cause the excel not closing from task list. Let me try once again

Did you try it? I tried copying sheets using my code (with slight amendments of course) and it works just fine....

[danasegarane]

I have replied in post no # 13

[RobDog888]

The basics of excel or office automation is to create object variables for each level down the object model. Then destroy each in reverse order. So parent to child creation. Then child to parent destruction.

Dont Implicitly create instances as those will retain an open reference to the object and prevent the object variable from being destroyed.

Dont create an object variable reference more then one level down. This is an example of two levels down and "could" be an issue.

xlWorkBook = xlApp.Workbooks.Open("c:\MyFile.xlsx")

Better as ...

xlWorkBooks = xlApp.WorkBooks
xlWorkBook = xlWorkBooks.Open("C:\MyBook.xlsx")

When properly created, destruction will be complete with no leftover open references. You should never really need to use .ReleaseComObject or .FinalReleaseComObject

[RobDog888]

Oh yea and if you need to install the Office Interop (PIA's) please see the link in my Office Dev FAQ.

[danasegarane]

I am attaching one sample program here.

The excel object removed from TaskManager without this lines

#
For index As Integer = 1 To 10
            oTemplateSheet.Copy(oDestinationWorkSheet_1)

       Next

With addition of these lines the excel is not release from TaskManager