Excel instance doesn't close

[dhutch75]

I'm opening an Excel spreadsheet from within MS Access. I open a spreadsheet and write database info to the spreadsheet using the following code:


Dim xlApp As Object
Dim xlBook As Object
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open ("L:\Development\Workscope.xls")
'[Report writing code]
xlApp.Visible = True

Everything works properly, Excel opens, code executes, and the completed spreadsheet appears on the screen. If the user closes the 'Workscope.xls' file, without actually exiting Excel, the Excel app disappears from the screen and appears to have closed. It is still running in Task Mgr, however. Running several reports generates multiple instances of Excel, all of which persist until the user exits Windows.

How can I keep the Excel app visible if the user closes the spreadsheet?

[Oorang]

If you uses the Excel.Application.Quit method, once the procedure that called the instance falls of the stack the instance will be destroyed properly. In the alternative, if you don't need to maintain control of the instance you can just shell to it.


Shell "Excel.exe L:\Development\Workscope.xls

Another trick that you can use (as the situation merits) it to try GetObject first before you use create object. In this way you will capture any already open instance of excel, then if that fails have your code use createobject (or shell) to make a new one.

[dhutch75]

Thanks, Aaron. I can't Quit because the user needs to print/analyze the spreadsheet after it is generated. I'm not sure that the shell command will work for me either, but I plan to stick it into my bag of tricks for future reference. I accidently posted this question twice and got a response on the other thread that resolved this issue using

xlApp.Visible = True
xlApp.UserControl = True
xlApp.IgnoreRemoteRequests = False
Set xlApp = Nothing
I appreciate your suggestions. They will be useful in the future.

[Oorang]

Thanks for posting back, I could see that being useful to me in the future:)