I cannot get Excel to quit

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

When running code in Access I open an excel workbook to eventually be
exported to a pdf file. Upon completion of this, I wnat to close the excel
workbook and it indeed appears to be closed and I am returned to Access
application. However, when I attempt to repeat the process I get a 'global'
error and I look in my running processes and find that Excel is still running
even though I have set the variables to zero, quit the application and closed
the workbook.. What more can I do other than throw a bomb at it?

HELP
Mike
 
Hi Mike,

Make sure you have released all the other object variables
as well -- for instance, your sheet and workbook variables

when you are cleaning up your variables, did you do this:
set obvar = nothing

if this does not resolve the problem, can you post the code
you are using to
1. dimension your variables
2. clean them up

?

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
(e-mail address removed)
 
This problem is most often caused by insufficient object referencing. When
you establish the Excel object, you must make sure that all references to the
Excel object are fully qualified. If you establish another object you think
is attached to your Excel Application object and it is not fully qualified,
Access tries to figure out where to assign it. If it cannot, it will
actually create another instance of Excel without your knowing it is
happening.

Then, at least two problems arise. One you have seen. That is that when
you issue your xlApp.Quit, You are quitting the instance you created, but
Access doesn't know to quit the one it created, so you will see it in the
Processes tab of Task Manager. The other thing that can happen is that
Access will get confused on which object it is suppossed to act on when you
address a method or property of your Excel object.

So, the bottom line is be sure you fully qualify all your references to the
Excel Application object. Be sure when you are shutting it down, you do it
in the correct order.
1.Close the workbook
2.Quit the application
3.Set all your object references to Nothing.
 
Hi Crystal,

I have the same problem that Mike has. 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 file and exit 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)

.......

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
 
What, exactly is it you are wanting to do?
Is there any code where the ........ is? In other words, are you
manipulating the worksheet at all, or are you just using this method to open
the spreadsheet for the user to view?

If so, I wouldn't create it as a object, rather I would use the Shell
command to open it after creating it with the TransferSpreadsheet.
 
The pivot table query is very limited in its formatting. The Excel pivot
table is far superior in clarity.

John134
 
True enough.

Well, then since you didn't post all the code, it is difficult to tell if
there is an unseen problem. I do see one place the error could happen.

Set XLBook = GetObject(MySheetPath)

There is no reference to the XL object you created.

This line really should be:

Set xlBook = XL.Workbooks.Open(MySheetPath, 0, True)


Also, you dont' really need to do the TransferSpreadsheet. That may also be
an issue. Instead, you could use the CopyFromRecordset method to load the
data into your spreadsheet.

The most usual cause of an instance of Excel being left behind is incomplete
object referencing. You have to be very careful to associate all the Excel
objects all the time. What can happen is if Access can't determine which
Excel object a reference belongs to, it will instansiate another instance of
Excel on its own. Then when you Quit the instance you created, the one
Access created will be left open.
 
Thanks, Mike, for your replies. I have finally solved the problem through
lots of trial and error with each line of code. I didn't realize, since this
is my first time with this application, that each Acccess coded statement
operating on the Excel worksheet must have a reference object. With these in
the proper places, I no longer have Excel running in the Task Manager after
completing the subroutine.

I hope this reply may be of help to others.

Thanks again,
John134
 
Back
Top