closing excel application

[bratuha]

Hi all,

How do i close xlapplication. I try to use

dim xlApp as new excel.application

xlapp.quit

but the process seems to be hanging there until i quit from my app.

also how do i make it visible in .NET. In vb 6 it was just
xlApp.Visible = true it does not work with .Net

Thanks

[Iouri]

after you quit excel
xlapp.quit
you have to set object to nothing
xlapp = Nothing

That will kill the excel app

[bratuha]

i've tryed it, doesn't work. it is still three event after i quit my app.

i have to create large number of xlapps and at the end they all there hanging...

[BJG]

Is there a component that I need to add to be able to issue the command:

dim xlApp as new excel.application

If I try this it is an error, because excel.application is not defined. Any thoughts?

[ilk]

you need to add reference / project/add reference/com/ microsoft excel ....

[hedge73060]

You may want to try getting the new COM objects from microsoft that are for office. This may help...

[ilk]

Are they not meant to come with office you install. I'm using office 2000 but tried office xp. the same problem.

Does it work for you? if you check task manager before closing your application.

I start to think that this is ms **** automatic garbage collector problem.

[Iouri]

You can prevent Excel hanging around by explicitly cleaning up any Excel
objects you instantiated with Marshal.ReleaseComObject and then invoking the
..NET garbage collecter before exiting.

Something like:

While (Marshal.ReleaseComObject(m_excelObj) <> 0) ' Repeat until no
more COM references
End While
m_excelObj = nothing
..... repeat for any other Excel objects allocated

' Invoke garbage collector before termination
GC.Collect()
GC.WaitForPendingFinalizers()

[ilk]

Thanks Iouri, it worked for excel,

now the same doesnot work for outlook. also if i try to quit outlook from olapp.quit i get

'Quit' is ambiguous across the inherited interfaces 'Outlook._Application' and 'Outlook.ApplicationEvents_Event'.

[BJG]

When I use that code to close out my Excel Appliction, it seam so work fine. byut when I try to open up the file I created, somtimes it opens up excel, but I can't veiw the cells. I can only see the toolbars, and where the cells should be I can see my desktop. Also any thoughts on how to caclulate 2 cells, and put the total in a different cell. And formating a cell, so it is a percentage, stuff liek that.

[gknierim]

Funny you mentioned that you can see your desktop because just yesterday when I attempted to open an excel spreadsheet, it did the same thing - no matter which worksheet.

Fix: make sure that excel.exe is not running in your processes in Task Manager. If it is, end the process and try opening your spreadsheet again. That should fix that problem.

[BJG]

With VB.NET what code would I use to chage the Fill Color of a cell, Freeze Panes, calculate cells, and add borders? Is their a good web site for any of this? Gknierim, I closed the Excel.EXE and it worked fine. What had happed is that I gave an invaild file name to SaveAS, so the program never got all the way through closing Excel. Thanks.

BJG

[BJG]

In my Task Manager..why won't my Excel.EXE close until I close stop running my VB project.

[gknierim]

I think that you could use the following to get you started. These are just a few:


Dim oSheet as Excel.WorkSheet

'For a border - specify start and end for range
oSheet.Range("A1", "D12").BorderAround()

'For bold etc..
oSheet.Range("A1", "D12").Font.Bold = True


Look at the range property in your help for the Excel object and it will give you several examples on how to format your cells.

HTH,
Greg

[BJG]

Here is my code for creating and closing Excle through VB.NET. If I close or exit my VB Applictaion I can use excel fine, and EXCEL.exe is deleted from my Task Manager. What I want to do is be able to use excel while my VB Application is still running. So how do I get that EXCEL.exe to be end from Task Manager.


Dim xlapp As New Excel.Application()
xlapp.Workbooks.Add()

''' Do a bunch of random things here(adding totals, writing to cells, etc.)

xlapp.Workbooks(1).Close(False)
xlapp.Quit()

While(System.Runtime.InteropServices.Marshal.ReleaseComObject(xlapp) <> 0) ' Repeat until no more COM references
End While

xlapp = Nothing
' Invoke garbage collector before termination
GC.Collect()
GC.WaitForPendingFinalizers()

PLEASE HELP!
BJG

[ejazashraf]

I did following to get rid of Excel application - even though vb.net application is still running. Let me know if this works.....

'dim scheme
Dim oExcel As Excel.ApplicationClass
Dim oBook As Excel.WorkbookClass
Dim oBooks As Excel.Workbooks
Dim oXLWs As Excel.WorksheetClass
Dim oXLWsheet As Excel.Worksheet

'opening and connecting to Excel
oExcel = CreateObject("Excel.Application")
oExcel.Visible = True
oBooks = oExcel.Workbooks
oBook = oBooks.Open("C:\PETSAutomation\Test.XLS") 'PLS CHANGE PATH
oXLWsheet = oBook.Sheets("sheet1")



'quitting excel application

System.Runtime.InteropServices.Marshal.ReleaseComObject(oXLWsheet)
oXLWsheet = Nothing
'System.Runtime.InteropServices.Marshal.ReleaseComObject(oXLWs)
oXLWs = Nothing
oBook.Close()
System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook)
oBook = Nothing
System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks)
oBooks = Nothing
oExcel.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)
oExcel = Nothing
GC.Collect()
GC.WaitForPendingFinalizers()

[Zeb]

I'm having the same problem

I've done everything as stated in all the solutions above, and found this msdn page:
http://support.microsoft.com/default.aspx?scid=kb;en-us;317109

which gives some info but it still doesn't work! excel does not shut down until after my app finishes!

thing is though, if I call the routine that opens excel and then closes it again a second time, it creates a new instance of EXCEL.EXE in the task manager and then closes it, but the first instance stays there for good.

code:

Dim exl As New Excel.ApplicationClass
Dim exlBooks As Excel.Workbooks
Dim exlBook As Excel.WorkbookClass
Dim exlSheet As Excel.Worksheet

exlBooks = exl.Workbooks
' colarray is a list of all the fields to import from the text file - it works...
exlBooks.OpenText("C:\test.txt", , , 1, Excel.XlTextQualifier.xlTextQualifierNone, , , , , , True, "\", colArray)
exlBook = exlBooks(1)
exlSheet = exlBook.Sheets(1)
...
' do some formatting
...
'close
System.Runtime.InteropServices.Marshal.ReleaseComObject(exlSheet)
exlSheet = Nothing
exlBook.Close(False)
System.Runtime.InteropServices.Marshal.ReleaseComObject(exlBook)
exlBook = Nothing
System.Runtime.InteropServices.Marshal.ReleaseComObject(exlBooks)
exlBooks = Nothing
exl.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(exl)
exl = Nothing
GC.Collect()
GC.WaitForPendingFinalizers()
please help. this is sending me crazy!

[LinkTree]

not that I am able to help - but I'm useing VB 6.0 and I dont seen to have the System class or GC (its only in .NET - an I right?)

Can any one offer help to 6.0 user?

[LinkTree]

I dont know if this will help any of you but it helped me.
I notice that the problem with my code was the Range object.
MEanning that when I needed to set a Range I used it like that :


Range(oWsData.Cells(wsInitRow, wsInitCol), oWsData.Cells(m_numberOfLectins + wsInitRow - 1, wsInitCol))


this was creating (I assume) a global range object that later I could not get closed...
what I did was use the my worksheet attached range :


ws.Range(oWsData.Cells(wsInitRow, wsInitCol), oWsData.Cells(m_numberOfLectins + wsInitRow - 1, wsInitCol))


that way when I was closing the worksheet everything was closed too.

:)
worked for me - hope it will help you too...

[Gizmo001]

"What I want to do is be able to use excel while my VB Application is still running."

Once you have defined the new excel object in your vb.net code, you make it visible:

xlApp.visible = true

xlApp is loaded and shown on screen. You can do whatever you want.

There are two ways to close the application.

1. Don't quit and set applicaiton to nothing in the VB code. This will make the xlApp independent of the code (use this only option only if all changes once you make it visible are to be done outside the application). And you can close xlApp whenever you want like a normal Office application.

2. If you want to quit within the code after viewing everything, make sure that you don't quit/close while you are browsing or editing in Excel; otherwise, your vb code will cause an exception. Also keep the xlApp.quit statement in a different procedure than the one you open it from. Otherwise, the application will close when the code comes to xlApp.quit, whether you are editing or not.

Hope this helps. I have had no problems, opening/closing multiple Excel files, viewing and editing some while in VB or outside, etc.

Good luck.

[Zeb]

:thumb: :thumb: :thumb:

gizmo - thank you so much. you didn't state it exactly, but you pointed me in the right direction. I think because I was declaring exl as New Excel.Application, and doing the garbage collection in the same Sub, it wasn't cleaning it because the variable didn't go out of scope until the Sub ended, so I move the garbage collection to after the sub has finished and it fixes it.

thanks a bucket load. I fell pretty stoopid now!

[pedroccda]

I try all this things but nothing to do... excel don't quit anyway.... I tried also to use all named variable.

Some one has an alternativ idea? (i think also to kill process from code but it's not so nice)

[pedroccda]

I try all your method to make excel to quit but nothing to do!! I report here my code. If some one can help me please.....


Private Sub Xls()

Try

'creo l'applicazione EXCEL
Dim MyExcel As Microsoft.Office.Interop.Excel.Application
MyExcel = New Microsoft.Office.Interop.Excel.Application
'Dim MyExcel As Excel.Application
'MyExcel = New Excel.Application

'creo i due documenti
WriteXls(MyExcel, dsAll, "Etichette")
WriteXls(MyExcel, dsSFAll, "Sottofascia")

'chiudo EXCEL
MyExcel.Quit()
NAR(MyExcel)

GC.GetTotalMemory(False)
GC.Collect()
GC.WaitForPendingFinalizers()
GC.Collect()
GC.GetTotalMemory(True)

Catch ex As Exception

End Try

End Sub

Private Sub WriteXls(ByRef MyExcel As Microsoft.Office.Interop.Excel.Application, ByVal ds As DataSet, ByVal s As String)

Try

Dim dv0 As New DataView
Dim i, j As Integer
'Dim MyWorkBook As Excel.Workbook
'Dim MyWorkSheet As Excel.Worksheet
'Dim MyWorkBooks As Excel.Workbooks
Dim MyWorkBook As Microsoft.Office.Interop.Excel.Workbook
Dim MyWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
Dim MyWorkBooks As Microsoft.Office.Interop.Excel.Workbooks
Dim cells As Object

dv0 = ds.DefaultViewManager.CreateDataView(ds.Tables(0))


MyWorkBooks = MyExcel.Workbooks
MyWorkBooks.Add()
MyWorkBook = MyExcel.ActiveWorkbook()

MyExcel.Visible = False 'non visualizzo excel
MyWorkSheet = CType(MyWorkBook.ActiveSheet, Microsoft.Office.Interop.Excel.Worksheet)

Dim dr As DataRowView
Dim dc As DataColumn
i = 1
j = 1
For Each dr In dv0
For Each dc In ds.Tables(0).Columns

cells = MyWorkSheet.Cells(i, j)
Try
If dr.Item(j - 1) Is System.DBNull.Value Then
cells = ""
'MyWorkSheet.Cells(i, j) = ""
Else
cells = dr.Item(j - 1)
'MyWorkSheet.Cells(i, j) = dr.Item(j - 1)
End If
Catch ex As Exception

Finally
j = j + 1
'finalizzo
'NAR(cells)
End Try
Next
j = 1
i = i + 1
Next

'Autofit the cells
MyWorkSheet.Cells(1, 1).CurrentRegion.EntireColumn.AutoFit()

'Save & Close
MyWorkBook.SaveAs(dir & "\" & sessionId & "_" & s & "_" & docCount & ".xls")

NAR(MyWorkSheet)

MyWorkBook.Close(False, Nothing, Nothing)
NAR(MyWorkBook)

MyWorkBooks.Close()
NAR(MyWorkBooks)

Catch ex As Exception

End Try

End Sub

'metodo per rilasciare gli oggetti com
Private Sub NAR(ByVal o As Object)

Try
While (System.Runtime.InteropServices.Marshal.ReleaseComObject(o) > 0)

End While

Catch

Finally

o = Nothing

End Try

End Sub

[Zeb]

try moving the gc.??? lines out of the XLS sub an put them just after the line that calls the XLS sub. it worked for me.

[moranc80]

hi ZEB.

can u send here a sample of your code.
I did what u said. it didnt help.

may be there is something i forgot.

it will be a big help.

Thanks!

[Zeb]

sorry buddy - I don't have any of that code left anymore! but everything listed above fixed it for me.

keep in mind:
- declare each excel object (i.e. workbook, application, etc) implicitly, and remove each on implicitly by doing the following code:
System.Runtime.InteropServices.Marshal.ReleaseComObject(???)
??? = Nothing
- in a different sub to where your excel objects are declared, call the garbage collection:
GC.Collect()
GC.WaitForPendingFinalizers()

the second, to my knowledge, shouldn't make any difference, but for some reason it made it work for me. and i've looked @ your code and nothing stands out as being the cause to me... sorry I can't help more.