Re: Excel Automation Issue
CreateObject does NOT return a window handle!
It returns a reference to your object.
Use FindWindow API:
vb.net Code:
<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
Re: Excel Automation Issue
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 ?
Re: Excel Automation Issue
Quote:
Originally Posted by
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 ?
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
Re: Excel Automation Issue
why don't you use following code
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
Code:
oSheet = Nothing
oWB.Close()
oWB = Nothing
oXL.Quit()
oXL = Nothing
System.GC.Collect()
System.GC.WaitForPendingFinalizers()
Re: Excel Automation Issue
Probably because he doesn't have Microsoft.Office.Interop.Excel assembly installed.
Re: Excel Automation Issue
Quote:
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.
Code:
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
Re: Excel Automation Issue
One addition:
vb Code:
xlWorkBook.Close(SaveChanges:=False)
Or else there will be a prompt for saving changes.
Re: Excel Automation Issue
Quote:
Originally Posted by
koolsid
I would suggest you not to use this method but close it the proper way.
Here is an example.
Code:
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
Re: Excel Automation Issue
Do you invoke Quit method?
And you should either save your changes using Save method
And use Workbook.Close method with parameters SaveChanges
Re: Excel Automation Issue
Quote:
Originally Posted by
koolsid
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
Code:
Microsoft.Excel.Introp
library
and use the Application and Worksheet object. it has the saparate methods for Closing the EXCEL object
Re: Excel Automation Issue
Quote:
Originally Posted by
cicatrix
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 :
Quote:
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.
Re: Excel Automation Issue
I was testing with this one.
vb Code:
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
Re: Excel Automation Issue
Quote:
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....
Re: Excel Automation Issue
I have replied in post no # 13
Re: Excel Automation Issue
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
Re: Excel Automation Issue
Oh yea and if you need to install the Office Interop (PIA's) please see the link in my Office Dev FAQ.
1 Attachment(s)
Re: Excel Automation Issue
I am attaching one sample program here.
The excel object removed from TaskManager without this lines
Code:
#
For index As Integer = 1 To 10
oTemplateSheet.Copy(oDestinationWorkSheet_1)
Next
With addition of these lines the excel is not release from TaskManager