PDA

View Full Version : Solved: Excel instance doesn't close



dhutch75
07-22-2008, 01:36 PM
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?

dhutch75
07-22-2008, 01:49 PM
Sorry for posting twice. I noticed a typo and accidently resubmitted the question when I attempted to edit. {Not a newbie VBA coder, but a little unfamiliar with this site.}

TomSchreiner
07-22-2008, 06:16 PM
I would like to see all of your code, but with what little I have seen, you may need to release the reference to the application object.

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
xlApp.UserControl = True
xlApp.IgnoreRemoteRequests = False
Set xlApp = Nothing

dhutch75
07-24-2008, 07:31 AM
Thanks, Tom. This did the trick. Setting UserControl and IgnoreRemoteRequests allows the app to stay open after the sheet is closed. I tested it without the xlApp=Nothing line and it worked as I wanted it to. I've added the final cleanup line in, but just wanted to explore which lines actually resolved the question.