Excel automation: excel.exe still exists in task manager

[AC]

Hi

I have some code which manipulates an Excel workbook. I am new to
this and do not think I have done it correctly as I can still see an
Excel.exe process in the task manager after I shut the workbook down
and quit excel. This process disappears once I shut my Access dbase
down so its clearly my code.

My code is below. A (new) Excel window pops up as the code executes
and this window disappears again once it is finished - which is
exactly what I want. However clearly some "excel" is still floating
around as its in the task manager. However the main idea here is to
(1) open an excel workbook and make it visible so we can see it, (2)
do some stuff, (3) shut down the workbook AND the excel app we opened
once we are done.

Note that initially I coded it only using wbDataWorkbook and no
myExcelApp, but this left the Excel application window open (and
focused) even when I closed the workbook, which is why I moved to
using myExcelApp in the first place.

Im certain I have screwed up.

All help appreciated
Andy C



Dim myExcelApp As Excel.Application
Dim wbDataWorkbook As Excel.Workbook

'Open the data file in Excel, and make it visible
Set myExcelApp = Excel.Application
myExcelApp.Workbooks.Open filename:=strDataFile, UpdateLinks:=0
Set wbDataWorkbook = myExcelApp.ActiveWorkbook
myExcelApp.Application.Visible = True

'Turn off calculations, large datasets can be really slow if
recalc is on
myExcelApp.Calculation = xlCalculationManual

'DOES SOME STUFF, this seems to work fine

'Clean up
myExcelApp.Calculation = xlCalculationAutomatic
wbDataWorkbook.Close SaveChanges:=False
myExcelApp.Quit

 

[Tom Wickerath]

Hi AC,

myExcelApp.Quit
Set myExcelApp = Nothing <---add this line of code


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[AC]

Hi AC,

    myExcelApp.Quit
    Set myExcelApp = Nothing   <---add this line of code

Tom Wickerath
Microsoft Access MVPhttp://www.accessmvp.com/TWickerath/
__________________________________________















- Show quoted text -

Thanks Tom, I appreciated it.

Can you give me a bit of insight into what this line of code does, im
a bit of a noob at vba but trying to learn.

Cheers
Andy C

 

[Tom Wickerath]

Hi Andy,

It destroys the object variable that you set at the beginning of the
procedure, by using these lines of code:

Dim myExcelApp As Excel.Application
Set myExcelApp = Excel.Application

Here are some resources that you may want to download. The first one is a
compiled Help file, so if you are using Vista, I believe you will need to
download some software that allows you to open .chm files:

The Office XP Automation Help file is available for download
http://support.microsoft.com/?id=302460

OFFAUTMN.exe discusses Office 97 and 2000 Automation
and provides sample code
http://support.microsoft.com/?id=253235

Also, the code you used is what is known as early bound (aka early binding).
This is fine for writing and debugging VBA automation code, because it gives
you Intellisence. However, it also requires a checked reference to a version
specific object library. After your code is working the way you want it to,
try converting it to use late binding, and deselect the checked reference to
the Excel Object library. I have a sample that uses late binding that you can
download:

Automation with Late Binding (Calling Excel's NormInv function)
http://www.accessmvp.com/TWickerath/downloads/Statistics.zip

This particular sample is designed to keep a global object variable open
whenever the database is open, so you should see the same behavior that you
reported, ie. an instance of Excel.exe in Task Manager until you close the
Access app. However, it would have been just as easy to include code to Quit
and set the object variable = Nothing when the form was closed. I just didn't
implement such a method.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[AC]

Also, the code you used is what is known as early bound (aka early binding).
This is fine for writing and debugging VBA automation code, because it gives
you Intellisence. However, it also requires a checked reference to a version
specific object library. After your code is working the way you want it to,
try converting it to use late binding, and deselect the checked referenceto
the Excel Object library. I have a sample that uses late binding that youcan
download:

      Automation with Late Binding (Calling Excel's NormInv function)
     http://www.accessmvp.com/TWickerath/downloads/Statistics.zip

This particular sample is designed to keep a global object variable open
whenever the database is open, so you should see the same behavior that you
reported, ie. an instance of Excel.exe in Task Manager until you close the
Access app. However, it would have been just as easy to include code to Quit
and set the object variable = Nothing when the form was closed. I just didn't
implement such a method.

Tom Wickerath
Microsoft Access MVPhttp://www.accessmvp.com/TWickerath/
__________________________________________

Thanks Tom

I had never heard of early/late binding before, so I will look into
this. It may well answer a question I was postponing and going to
deal with further down the track...

I intended to compile all this up into an mde file and distribute to
other people to use. Would doing so still require the user to have to
tick the Excel library reference on in their version of Access, or
does this all get included when making the mde compiled version?

If it still requires reference ticking at their end maybe I need to
move towards this "late binding " concept you discussed, although to
be honest I get a bit nervous about implementing using methods (late
binding in this case) I am not that familiar with in case I screw it
up

Andy C

 

[AC]

Hi AC,

    myExcelApp.Quit
    Set myExcelApp = Nothing   <---add this line of code

Tom Wickerath
Microsoft Access MVPhttp://www.accessmvp.com/TWickerath/
__________________________________________

I have just had a chance to test this, and it does not seem to do what
I am after. The Excel instance stays in the task manager.

I actually added 2 new lines of code:
Set wbDataWorkbook = Nothing
Set myExcelApp = Nothing

When executing these lines of code I keep my eye on the task manager
and the EXCEL.EXE simply stays there.

Am I missing something?

Thanks
Andy C

 

[AC]

I have just had a chance to test this, and it does not seem to do what
I am after.  The Excel instance stays in the task manager.

I actually added 2 new lines of code:
    Set wbDataWorkbook = Nothing
    Set myExcelApp = Nothing

When executing these lines of code I keep my eye on the task manager
and the EXCEL.EXE simply stays there.

Am I missing something?

Thanks
Andy C

I screwed up, I was missing the NEW command on the line:
Set myExcelApp = Excel.Application

it should be set myExcelApp = NEW Excel.Application

Once I added that the cleanup code seems to work fine.

 

[Tony Toews [MVP]]

I intended to compile all this up into an mde file and distribute to
other people to use. Would doing so still require the user to have to
tick the Excel library reference on in their version of Access, or
does this all get included when making the mde compiled version?

1) Late binding means you or the users do not need the reference.

2) The users can't change references in an MDE.
INFO: How to guarantee that references will work in your applications
http://trigeminal.com/usenet/usenet026.asp?1033 (The title is

although to
be honest I get a bit nervous about implementing using methods (late
binding in this case) I am not that familiar with in case I screw it
up

Late binding requires changing about three lines of code usually
although maybe a few more if you are using some Excel constants.
These lines of code are usually just the Dim statements so it's
relatively painless and easily tested.

And it works!

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/