Hanging Instance of Excel
 

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)

Hanging Instance of Excel
 

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.
--
HTH...

Jim Thomlinson


"Terry" wrote:

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)

.
.
Process sheets here
.
.

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


Thanks for your help all

Terry

Hanging Instance of Excel
 

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.

"Jim Thomlinson" wrote:

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.
--
HTH...

Jim Thomlinson


"Terry" wrote:

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)


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


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


Thanks for your help all

Terry

Hanging Instance of Excel
 

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"
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)

Hanging Instance of Excel
 

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

Regards,
Peter T


"Terry" wrote in message
...
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.

"Jim Thomlinson" wrote:

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.
--
HTH...

Jim Thomlinson


"Terry" wrote:

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)


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


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


Thanks for your help all

Terry

Hanging Instance of Excel
 

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

"Jim Cone" wrote:


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"
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)

.
.
Process sheets here
.
.

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


Thanks for your help all

Terry

Hanging Instance of Excel
 

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

Dim xlApp As Excel.Application

instead of

Dim xlApp As Object

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Terry" wrote in message
...
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

"Jim Cone" wrote:


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"
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)

.
.
Process sheets here
.
.

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


Thanks for your help all

Terry