Problem quitting Excel in Acess VBA code

[John134]

I have the same problem that Mike has on a previous post. My code quits Excel
on the screen but leaves Excel running in the Task Manager. My project is to
transfer an Access Table to Excel and convert it to a Pivot Table. To do this
each time
I have to delete the Excel file and exit Excel. All my code is working
except for this issue with quitting Excel.

Here is part of my code referring to this issue:

Dim MySheetPath As String
MySheetPath = "E:\LA Snr Ctr\Activity Log.xls"
Dim XL As Object
Dim XLBook As Object
Dim XLSheet As Object
Set XL = CreateObject("Excel.Application")
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "Activity
Log", "E:\LA Snr Ctr\Activity Log.xls", True
Set XLBook = GetObject(MySheetPath)
XL.Visible = True
XLBook.Windows(1).Visible = True
Set XLSheet = XLBook.Worksheets(1)

(other code)

Fin: XLBook.Close False
XL.Quit
Kill "E:\LA Snr Ctr\Activity Log.xls"
Set XLSheet = Nothing
Set XLBook = Nothing
Set XL = Nothing

Any suggestions on where the error is would be greatly appreciated

John134

 

[Jeanette Cunningham]

John,
when I use transfer spreadsheet to export a query and then manipulate the
data in excel, I usually do it in 2 separate steps.
step1 - do the export and finish that code completely
step2 - open excel and manipulate the data in excel then close excel

You could try it like this, but I don't know if it will help.

Jeanette Cunningham

 

[Stefan Hoffmann]

hi John,

I have to delete the Excel file and exit Excel. All my code is working
except for this issue with quitting Excel.

You can close Excel only if all references to itself and its objects,
e.g. Worksheets, are cleared.

Fin: XLBook.Close False
XL.Quit
Kill "E:\LA Snr Ctr\Activity Log.xls"
Set XLSheet = Nothing
Set XLBook = Nothing
Set XL = Nothing

Any suggestions on where the error is would be greatly appreciated

Use this reorderd code:

XLBook.Close
Set XLSheet = Nothing
Set XLBook = Nothing
XL.Quit
Set XL = Nothing

Kill "E:\LA Snr Ctr\Activity Log.xls"

This should work.


mfG
--> stefan <--