Argggg - Closing Excel

[RzB]

There appear to be a million post on this subject....
I have read most of them , however dimbo that I am
I just don't see what I'm doing wrong. Have I got "implicit"
references in the code below?

My problem is that after I run the code below, Excel is open
in the Task Manager.

I have reduced my code to the following snippet... Why oh
why don't it just close when I tell it too.... And - even more
to the point - if I tell it to close and it don't, why do I not get
an error! Hmmm - as you can see I'm getting frustrated and
beginning to loose it

Any help would be most appreciated...

Using XP Prof SP1, Access 2002 SP3, Excel 2002 SP3.
Roy
========================================
Private m_objXlApp As Excel.Application
Private m_objXlWkb As Excel.Workbook
Private m_objXlSht As Excel.Worksheet

Private Sub TestButton_Click()

'create and open
Set m_objXlApp = New Excel.Application
m_objXlApp.SheetsInNewWorkbook = 1
Set m_objXlWkb = m_objXlApp.Workbooks.Add
Set m_objXlSht = m_objXlWkb.Worksheets("Sheet1")

'save it
m_objXlWkb.SaveAs ("c:\AAA.xls")

'Close it
Set m_objXlSht = Nothing

m_objXlWkb.Close
Set m_objXlWkb = Nothing

m_objXlApp.Quit
Set m_objXlApp = Nothing

End Sub

 

[Andi Mayer]

Private m_objXlApp As Excel.Application
Private m_objXlWkb As Excel.Workbook
Private m_objXlSht As Excel.Worksheet

use late Binding

Private m_objXlApp As Object
Private m_objXlWkb As Object
Private m_objXlSht As Object

 

[Wayne Morgan]

I just tried your code and it worked. I made a couple of modifications for
quick testing and pasting into a module. Task Manager showed Excel opening
then closing again. Of course, to get it to compile I also had to add a
Reference to the Excel Object Library.

Public Sub TestExcel()
Dim m_objXlApp As Excel.Application
Dim m_objXlWkb As Excel.Workbook
Dim m_objXlSht As Excel.Worksheet

'create and open
Set m_objXlApp = New Excel.Application
m_objXlApp.SheetsInNewWorkbook = 1
Set m_objXlWkb = m_objXlApp.Workbooks.Add
Set m_objXlSht = m_objXlWkb.Worksheets("Sheet1")

'save it
m_objXlWkb.SaveAs ("c:\AAA.xls")

'Close it
Set m_objXlSht = Nothing

m_objXlWkb.Close
Set m_objXlWkb = Nothing

m_objXlApp.Quit
Set m_objXlApp = Nothing

End Sub

 

[Andi Mayer]

BTW: I tried his code also with Win2000 and Office2000, because I have
actually never tried it with early binding

Excel stays in memory , with late binding it disapears

 

[Dan Artuso]

Hi,
I copied and pasted your *exact* code into Access and it worked as it should.
There was no lingering instance of Excel.

Are you saying when you run the *exact* code you posted, and nothing else,
you have an instance of Excel still in memory?

The only difference is that I'm running it in 97.

 

[RzB]

Andy, Wayne and Dan,
Many thanks for your help. OK - to be very clear about
this I created a form with one button in a new blank database.
The following code is behind the button.... It makes no difference
if I use explicit declarations or late binding...
I still get an instance of Excel left running in the task manager.....

References...
Visual Basic for Applications
MS Access 10.0 Object Library
OLE Automation
MS ActiveX Data Objects 2.1 Library
MS Excel 10.0 Object Library

I have a bad sort of reinstallation feeling about this....
Thanks,
Roy

================================
Option Compare Database
Option Explicit

'Private m_objXlApp As Excel.Application
'Private m_objXlWkb As Excel.Workbook
'Private m_objXlSht As Excel.Worksheet

Private m_objXlApp As Object
Private m_objXlWkb As Object
Private m_objXlSht As Object

Private Sub Command0_Click()

Set m_objXlApp = New Excel.Application

m_objXlApp.SheetsInNewWorkbook = 1

Set m_objXlWkb = m_objXlApp.Workbooks.Add
Set m_objXlSht = m_objXlWkb.Worksheets("Sheet1")

m_objXlWkb.SaveAs ("c:\JJJ.xls")

Set m_objXlSht = Nothing

m_objXlWkb.Close
Set m_objXlWkb = Nothing

m_objXlApp.Quit
Set m_objXlApp = Nothing

End Sub
==========================

 

[Dan Artuso]

Sorry, still could not reproduce, but don't forget I'm testing on 97.

 

[Dan Artuso]

Okay, I tried with:
WinXp
Access 2003

Still can't reproduce.

 

[Andi Mayer]

I use "normally" this code

reference set to MS Access XX.X Object Library

no reference to Excel

Private Sub Command0_Click()

Dim objExcel As Object
Dim objWkBook As Object

Set objExcel = CreateObject("excel.application")
objExcel.SheetsInNewWorkbook = 1
Set objWkBook = objExcel.Application.Workbooks.add

With objWkBook
.SaveAs ("c:\JJJ.xls")
End With

objExcel.Quit
Set objWkBook = Nothing
Set objExcel = Nothing
End Sub

I never had

 

[Andi Mayer]

forgot to write:

tested on WK2 with Office2000
and
WinXP and Office 2002

 

[RzB]

Gents,
Many thanks for you help. I think I have something
screwing things up on my machine... It's been due for
a new C: drive and XP SP 2... I've been putting it off
for too long... I have managed to test on another machine
with similar versions and it works fine....
A real pain... spent all day chasing this one...
Thanks,
Roy

 

[Andi Mayer]

Gents,
Many thanks for you help. I think I have something
screwing things up on my machine... It's been due for
a new C: drive and XP SP 2... I've been putting it off
for too long... I have managed to test on another machine
with similar versions and it works fine....
A real pain... spent all day chasing this one...
Thanks,
Roy

try a repair of Office first

 

[John Nurick]

It's conceivable that there's an add-in or something on one machine that
is creating and dirtying a hidden workbook. Try something like this to
dispose of them all:

...
m_objXlApp.Workbooks.Close False
m_objXlApp.Quit