Hanging Instance of Excel

[Terry]

I have been trying some of the posts here to help with this but no luck so
far. I have an access DB that open Excel, processes the sheets there and
then closes. I have an instance of Excel still running that is only
accessible through the task manager. I need to figure out what I am doing
wrong with the following code.

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

ExcelTab = "Q1-Insvc_Disc Data"
Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Open("C:\temp\Video Churn

Report_v_2007_06_22", 0, False)
Set xlSheet = xlBook.Worksheets(ExcelTab)

 

[Jim Thomlinson]

The order in which you destry the objects is important. Destroy the book
prior to the application... More like this...

xlBook.Close savechanges:=True
Set xlBook = Nothing
xlApp.Quit ' When you finish, use the Quit method to close
Set xlApp = Nothing ' the application, then release the reference.

 

[Terry]

Jim

I switched the lines around but I am still getting the hanging instance of
Excel in the Task Manager. I added some stripped down code that I am
processing against the sheet.

The order in which you destry the objects is important. Destroy the book
prior to the application... More like this...

xlBook.Close savechanges:=True
Set xlBook = Nothing
xlApp.Quit ' When you finish, use the Quit method to close
Set xlApp = Nothing ' the application, then release the reference.

row = xlSheet.Cells(Rows.Count, "A").End(xlUp).Row

Do While Row > 1
If xlSheet.Cells(Row, 1).Value = FilterDate And xlSheet.Cells(Row,
4).Value = VideoComp Then
'MsgBox Row
Set BegRange = xlSheet.Cells(Row, 1)
Set EndRange = xlSheet.Cells(Row, 7)
xlSheet.Cells.Range(BegRange, EndRange).Delete (xlUp)
End If
Row = Row - 1
Loop

 

[Jim Cone]

In addition to Jim Thomlinson's good advice, make sure that every
reference to an object is qualified. So...

row = xlSheet.Cells(Rows.Count, "A").End(xlUp).Row
should read
row = xlSheet.Cells(xlSheet.Rows.Count, "A").End( -4162).Row

xlSheet qualifies Rows.
Access will not recognize xlUp, so the numeric value is used.

Set each object to nothing when done with it.
Avoid the use of With statements.
Do not use ActiveSheet, ActiveCell, Selection etc. They aren't qualified.
Not qualifying objects can lead to orphan references and Excel's refusal to quit.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Terry" <[email protected]>
wrote in message
I have been trying some of the posts here to help with this but no luck so
far. I have an access DB that open Excel, processes the sheets there and
then closes. I have an instance of Excel still running that is only
accessible through the task manager. I need to figure out what I am doing
wrong with the following code.

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

ExcelTab = "Q1-Insvc_Disc Data"
Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Open("C:\temp\Video Churn

Report_v_2007_06_22", 0, False)
Set xlSheet = xlBook.Worksheets(ExcelTab)

 

[Peter T]

Did you also -
Set xlSheet = nothing
before destroying the other ref's in order as Jim suggested

Regards,
Peter T

 

[Terry]

Jim:

Thanks for the help. I had narrowed the code down to that one line that
needed the ref to xlsheet as the problem. I added that in and everything is
working good again. I was able to leave the xlUp in the code with it still
working though.

Once again, thanks to everyone for helping

Terry

 

[Jon Peltier]

xlUp works because you're early binding, i.e., using:

Dim xlApp As Excel.Application

instead of

Dim xlApp As Object

- Jon