asked on

Using Excel in VB.NET

Hi everyone,

I am having a problem closing Excel from within my VB.NET app. I use a DataReader to return the results of an SQL and put the results into an Excel worksheet. I then print the worksheet and close excel - as follows:

'Declare Variables
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
xlBook = CType(xlApp.Workbooks.Open("c:\temp\data.xlt"), Excel.Workbook)
xlSheet = CType(xlBook.Worksheets(1), Excel.Worksheet)

xlSheet.Range("B3").Value = reader.item("total_qty")

'Print Sheet
xlSheet.Application.Visible = True
xlSheet.PrintOut()
xlApp.DisplayAlerts = False

'Close App
xlBook.Close()
xlApp.Quit()

Although Excel does appear to close correctly (i.e. its not visible!!!), I can see in Windows Task Manager that an instance of Excel is still running. Only when I completely exit my app does Task Manager show that Excel has closed.

Any help at all would be great

bramsquad

try this after xlApp.Quit()

xlApp = Nothing

~b

hendrix500

ASKER

Made no difference. Sorry

Arthur_Wood

not sure if the Excel.Application object supports .Dispose, but try this:

xlApp.Quit()
xlApp.Dispose()
xlApp = Nothing

you may be seeing 'latency' in the Garbage Collector - setting xlApp to Nothing simply tells the GC that this reference can be Garbage Collected whenever necessary, but that may not take place for a while - depends on the other system/storage needs of your app.

AW

hendrix500

ASKER

Dosen't support .Dispose.

computerg33k

instead of xlApp.quit use this:
xlApp.ActiveWindow.Close()

computerg33k

be sure to include xlApp=nothing as well though

hendrix500

ASKER

Doesnt solve the problem unfortunately. Excel is still running in the background

computerg33k

hmm..not sure--will have to try more code--what verison of excel are you running and what version of windows. for word this works:
'to open a wrd doc
wrdApp = CType(CreateObject("Word.Application"), Word.Application)
wrdApp.Visible = True

' Add a new document.
wrdDoc = wrdApp.Documents.Add()
wrdDoc.Select()

'to close a wrd doc

' Go to the end of the document.
wrdApp.Selection.GoTo(Word.WdGoToItem.wdGoToLine, _
Word.WdGoToDirection.wdGoToLast)

' Perform mail merge.
wrdMailMerge.Destination = _
Word.WdMailMergeDestination.wdSendToNewDocument
wrdMailMerge.Execute(False)

' Close the original form document.
wrdDoc.Saved = True
wrdDoc.ActiveWindow.Close()

' Release References.
wrdSelection = Nothing
wrdMailMerge = Nothing
wrdMergeFields = Nothing
wrdDoc = Nothing
wrdApp = Nothing

' Clean up temp file.
System.IO.File.Delete("C:\DataDoc.doc")

and i'm using windows xp and word version 2002--maybe this could shed some light?

hendrix500

ASKER

Managed to find a solution myself. For anyone who's interested - this is the link

http://www.thescarms.com/dotNet/ExcelObject.asp

Thanks to everyone who made suggestions. Now how do I close this without accepting a solution!!!!!

computerg33k

to close question with refund go to the community support page and post a "question" there. be sure to include account name (hendrix500). just to let you know a .kill is bad code and you might want to try a different route if the program is always going that route to close excel.

ASKER CERTIFIED SOLUTION

ee_ai_construct

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial