To All,

I am working on an Accesst-to-Excel automation process and I am having
difficulty in getting the Excel application to fully close (i.e. I can
open up the Task Manager window and see the application running).
Closing Access will force Excel to close, so I know that Access is
holding on to the app.

Here is a snip of the code:

****Code Starts Here****
'Some Variables Defined
Dim dblocal As Database
Dim appEx As Object
Dim wbkNew1 As Excel.Workbook
Dim wksNew1 As Excel.Worksheet

'Open the Excel Application
Set appEx = CreateObject(Class:="Excel.Application")
appEx.Workbooks.Open FileName:="c:\Export.xls"
appEx.Worksheets("Export1").Activate
appEx.Visible = False

'Selecting and Copying my Range
With appEx.ActiveSheet.Range("R2")
    .Select
    .FormulaR1C1 = "=RC[-14]&RC[-2]&RC[-13]"
    .Copy
End With

With appEx.ActiveSheet.Range("R2:R150")
    .Select
    ActiveSheet.Paste
    .Copy
    .PasteSpecial Paste:=xlValues, Operation:=xlNone,
skipblanks:=False, Transpose:=False
End With

'I added this thinking it may work, but it has not
appEx.CutCopyMode = False

'Deleted Some Unneeded Columns
appEx.ActiveSheet.Columns("S").Delete Shift:=xlToLeft
appEx.ActiveSheet.Columns("O:P").Delete Shift:=xlToLeft

'Trying to Release the Excel Application
appEx.DisplayAlerts = False
appEx.ActiveWorkbook.SaveAs FileName:="c:\Export.xls",
FileFormat:=xlExcel9795, CreateBackup:=False
appEx.ActiveWorkbook.Close
appEx.DisplayAlerts = True
appEx.Application.Quit
appEx.Quit
Set appEx = Nothing
Set dblocal = Nothing
Set wksNew1 = Nothing
Set wbkNew1 = Nothing

*****Code Stops********

I have had success in automating Access-to-Excel Apps without the
copying and pasting (just formatting).  However, my attempts at
integrating a copy/paste combination have not worked.

What am I missing from the above code to get the Access application to
release Excel?  Is there something retained in the buffer?

Any suggestions would be GREATLY appreciated.

Thanks,

Windman

Hi,

Tell us what version of Access and Excel you are using. You should also
visit the microsoft.public.excel.programming newsgroup - participation there
is very intense.

I am using Access 2000, Excel 2000 and I suffer the same problem. I don't
remember having this problem in the 97 versions.

Here's what I have tried at least to comply with some recommendations.

When you first create the Excel.Application object, it is already
.visible=false.

Apparently use of WITH keyword in Excel 2000 causes Excel to stay in memory.
MSKB has an article on it.

I try to be conservative with .DisplayAlerts setting. Have you found any use
for

appEx.DisplayAlerts = False

???

Can't see why you need to do

appEx.Application.Quit

surely

appEx.Quit

does it already?

Other way around? It seems more logical to set the children to nothing
first?

Do you mean copy/paste has not been successful or that the final release of
Excel from memory has not been successful?

HTH
Ananda
http://members.optushome.com.au/anandasim/excel.htm

I've posted some stuff about this problem before.  It should be
available in the archives if you need more detail.

I've seen a few people solve this problem by doing away with "implicit
references."   In other words,  fully qualify every use of an Excel
property or method with an Excel object.

Here's a snip from the original post:

In the first and third lines,  the Excel object variable "appEx" is
used.

But in the second line,  the FileFormat property is referenced with an
*implicit* reference to an Excel workbook object.  I'm thinking that
you need to qualify it with your "wbkNew1" object:  

If there are other such references in the code you'll want to get them
too.   (As a last resort,  you can try doing away with the Withs as
Anada suggests,  but I don't think that's necessary.)

I know it sounds crazy,  but I've had the exact same problem and this
solved it for me.

My thanks to the guy who originally set me straight on this.  His name
escapes me. (Perhaps Dmitri Furman.... ?)

-Matt

On Thu, 14 Nov 2002 09:10:12 +1100, "Ananda Sim"

Hi Matthew,

Yes, that's also another tip I came across. I had such long code that it was
tedious to do that thoroughly but I'll give my own project a try as well.

Actually, that's not a good example. It looks like one line

appEx.ActiveWorkbook.SaveAs FileName:="c:\Export.xls", _
                                          FileFormat:=xlExcel9795, _
                                          CreateBackup:=False
appEx.ActiveWorkbook.Close

They're all explicit.

I can sorta see what you're getting at though.

Actually, it's Ananda. I like using Withs myself but the MSKB

http://support.microsoft.com/default.aspx?scid=KB;en-us;q199219

says it's not good.

Nothing in MS programming ever sounds crazy. It just is.

HTH
Ananda

AnaNda:

On Thu, 14 Nov 2002 18:40:48 +1100, "Ananda Sim"

My bad.  I got tripped up by the line wrap.

I took another look through the original code for an implicit
reference,  and think I found one embedded in the following:

It looks like that ActiveSheet.Paste could be the culprit.

Again,  my bad.  Sorry about the name typo.  I got through that a lot
with "Matthew".  Lots of people insist on spelling it with one "t",
even though I've never met anyone who actually has the "one-t"
spelling.  And in the US it's a very common name.

-MatThew

Hi Matthew,

Thanks for the support and interest. Will get round to looking at my own
code sometime.

Ananda

Here is my version of your code.
It opens and closes a new application.
The major change to the code involves setting references to all objects used:
    The application
    The workbook
    The worksheet
Then using the references.

All range callouts are preceded by the appropriate object.  If you don't, then
the objects may not be released, causing the application to remain open.

'****Revised Code Starts Here****

 Sub GoogleWontClosePost()
 Dim appEx As Excel.Application
 Dim wbkNew1 As Excel.Workbook
 Dim wksNew1 As Excel.Worksheet

'Open the Excel Application

 Set appEx = CreateObject(Class:="Excel.Application")
 Set wbkNew1 = appEx.Workbooks.Open(Filename:="c:\Export.xls")
 Set wksNew1 = wbkNew1.Worksheets("Export1")

'Default status is not visible
' appEx.Visible = True

'Copy Range, select destination range then paste
'Avoid using "ActiveSheet"
'Precede all range callouts with the appropriate object - prevents "orphans"
 With wksNew1.Range("R2")
     .FormulaR1C1 = "=RC[-14]&RC[-2]&RC[-13]"
     .Copy
 End With
 wksNew1.Range("R2:R150").Select
 wksNew1.Paste
 appEx.CutCopyMode = False

'Delete Some Unneeded Columns
 wksNew1.Columns("S").Delete Shift:=xlToLeft
 wksNew1.Columns("O:P").Delete Shift:=xlToLeft
 appEx.DisplayAlerts = False

'If changing file format then use:
 wbkNew1.SaveAs FileFormat:=xlExcel9795
 wbkNew1.Close savechanges:=False

'If not changing fileformat then use:
' wbkNew1.Close savechanges:=True

'Release the Excel Application
'Close in proper order
 Set wksNew1 = Nothing
 Set wbkNew1 = Nothing
 appEx.Quit
 Set appEx = Nothing
End Sub

'*****Revised Code Stops********

Jim Cone
San Jose, CA

***************************************************