Ecxel will not be closed within ACCESS

[Winfried]

Hi,

I have a problem. I use Windows XP, SP2,ACCESS 2003 SP2.

In an ACCESS sub I open an Excel file to process it. Afterwards I close
it. Unfortunately the Excel remains open respectively is visible when I
have a look into the task manager. This causes further problems in my
program.

The small extract of my VBA code looks as follows:

-----------------------------------------------------
Sub TEST()

Dim objExcelApp As Excel.Application
Dim objExcelWb As Excel.Workbook

Dim lastRecordExcel As Integer
Dim i As Integer

Dim ImportFile As String

ImportFile = "c:\temp\testExcelfile.xls"

' Make connection to Excel
Set objExcelApp = New Excel.Application
objExcelApp.Application.Workbooks.Open ImportFile
Set objExcelWb = objExcelApp.Application.Workbooks(1)

' Find last blank record from top in column 1
lastRecordExcel = objExcelWb.Worksheets(1).Cells(Rows.count,
1).End(xlUp).Row
'...
' Here come further VBA commands
'....

' Close and destroy the Excel object
objExcelWb.Close
Set objExcelWb = Nothing
objExcelApp.Quit
Set objExcelApp = Nothing

End Sub

-----------------------------------------------------

But now the best: when I comment out the assignment to variable
lastRecordExcel respectively replace it by the following
'while'-command
-------------
i = 6
While objExcelWb.Worksheets(1).Cells(i, 1) <> ""
i = i + 1
Wend
lastRecordExcel = i - 1
-------------

Excel will be closed correctly.

Can someone give me a hint to solve the problem.

Best regards,
Winfried

(Remark: The behaviour is the same when I use this code inside Word
2003, if I use it inside Excel 2003 all works fine)

 

[RoyVidar]

Winfried wrote in message

Hi,

I have a problem. I use Windows XP, SP2,ACCESS 2003 SP2.

In an ACCESS sub I open an Excel file to process it. Afterwards I close
it. Unfortunately the Excel remains open respectively is visible when I
have a look into the task manager. This causes further problems in my
program.

The small extract of my VBA code looks as follows:

-----------------------------------------------------
Sub TEST()

Dim objExcelApp As Excel.Application
Dim objExcelWb As Excel.Workbook

Dim lastRecordExcel As Integer
Dim i As Integer

Dim ImportFile As String

ImportFile = "c:\temp\testExcelfile.xls"

' Make connection to Excel
Set objExcelApp = New Excel.Application
objExcelApp.Application.Workbooks.Open ImportFile
Set objExcelWb = objExcelApp.Application.Workbooks(1)

' Find last blank record from top in column 1
lastRecordExcel = objExcelWb.Worksheets(1).Cells(Rows.count,
1).End(xlUp).Row
'...
' Here come further VBA commands
'....

' Close and destroy the Excel object
objExcelWb.Close
Set objExcelWb = Nothing
objExcelApp.Quit
Set objExcelApp = Nothing

End Sub

-----------------------------------------------------

But now the best: when I comment out the assignment to variable
lastRecordExcel respectively replace it by the following
'while'-command
-------------
i = 6
While objExcelWb.Worksheets(1).Cells(i, 1) <> ""
i = i + 1
Wend
lastRecordExcel = i - 1
-------------

Excel will be closed correctly.

Can someone give me a hint to solve the problem.

Best regards,
Winfried

(Remark: The behaviour is the same when I use this code inside Word
2003, if I use it inside Excel 2003 all works fine)

I think, if you try to replace this

Set objExcelApp = New Excel.Application
objExcelApp.Application.Workbooks.Open ImportFile
Set objExcelWb = objExcelApp.Application.Workbooks(1)

With

Set objExcelApp = New Excel.Application
Set objExcelWb objExcelApp.Workbooks.Open(ImportFile)

you might be one step closer. Compining the assigning/instantiation of
the workbook object variable as you've done, will probably create an
implicit instantiation of Excel, which is left open (found in the
Task Managaer)

 

[freakazeud]

Hi,
this is a known issue.
You might want to concider late binding.
Have a look at this:
http://www.tushar-mehta.com/excel/vba/xl_doesnt_quit/
HTH
Good luck

 

[Winfried]

Hi all,

I got good ideas from your side that I use now in my program. Now it
prefectly runs as desired.

Thanks again and have a nice weekend
Winfried

 

[Ken Snell \(MVP\)]

This code step likely is the cause of the problem:
lastRecordExcel = objExcelWb.Worksheets(1).Cells(Rows.count,
1).End(xlUp).Row


Because you do not explicitly qualify the Rows object, ACCESS creates a new
reference to the worksheet on which the rows are located, and your code
obviously does not close that reference. Try this:
lastRecordExcel =
objExcelWb.Worksheets(1).Cells(objExcelWb.Worksheets(1).Rows.count,
1).End(xlUp).Row


See this Knowledge Base article for more details about qualifying objects in
VBA automation:
Excel automation fails second time code runs
http://support.microsoft.com/kb/178510/en-us

 

[Winfried]

Hi all,

Thank you very much for your help and support. Your answers helped me
to solve my problem.!!!!!

Best regards & have a nice weekend
Winfried