Quit an Office App but make sure it exists (1 Viewer)

I have functions that open Office Apps, including Word, Excel, and Project. I've had the problem that sometimes when errors occur the Apps are not properly closed and that ruins the function until I either close the App process in Task Manager, or reboot the system.

To this end, I want to close the App in the On Error code at the end of my functions. The problem is, I don't know if the error occurs before or after the application is opened. I know I can Set prjApp1 = Nothing, but that does not close the app. If I use prjApp1.Quit, then I'm afraid I will run into situations where prjApp1 hasn't been created yet, or even Dim'ed, thus resulting in an error.

1. Is there a way to check if a variable has been Dim'ed yet? That is, check if a variable even exists, so I would have to use the name in the check even though it sometimes hasn't been mentioned yet.

2. What's the best way to make sure the App process has been ended in the On Error Goto code? Also, I have to consider the possibility that the user had the App open with another document before activating the function, in which case I don't want to close the app if they were working on something else totally unrelated.

Use
If objApp is nothing
' the app has been destroyed
Else
objApp.Quit
Set objApp= Nothing
End If

using Static variable declaration
ensures that your object application
remains in the memory, even when an error occurs.
so you don't need to worry whether the object
is destroyed or not. or wether to manually
destroy them.
try running the TEST() subroutine
and you will see that, there is only
once instance of Excel running (task manager)
when we open 2 instances and closes
the 2 intances.
when you close the database, the excel object
is automatically close. no need to put .Quit or
set it to Nothing.

in addition to arnelgp's comments, in answer to Q1, ensure you have Option Explicit at the top of each module - this will pick up whether or not a variable has been declared when you compile the code.

OK, let's be honest here. We can give you hints about whether something is still open, but the truth is that after a catastrophic failure of Access, you won't have the opportunity to fix anything. So what we tell you contains the underlying assumption that Access is going to die gracefully when it dies.

There are ways using GetObject to determine if a particular app is running, but there is a danger in closing it without knowing it was yours. After all, someone could have launched a copy of Word and left it open while suddenly having to work on something in your database.

The best solution is to declare some variables in a general module and if you have a switchboard or dispatcher form that stays open for the duration of your session, put all of the external-object variables in the declaration area of the general module. Then you manipulate the objects. A static declaration (as suggested by ArnelGP) makes the object variable persist even if things get a bit uncertain.

If you test the object, you need to know how to decide that the object is still active. This differs from one app to the next. For instance, if you can verify that Excel is still running, you might test it using

X = ExcelObject.Workbooks.Count

where if X is > 0 then at least one workbook is open. For Word, I think it is the Documents.Count test, and I have not tried this for PowerPoint. For Outlook, you HAVE to use a GetObject first since Outlook gets a bit picky about how you activated it and it barfs badly if you try to create a second Outlook object to do your cleanup.

Ok thanks everybody, good stuff. But I thought CreateObject will already use an App that's running, so what difference does GetObject make?

I don't think anybody understand my question 1. I meant, is it possible to check if a variable is declared before I perform a check on it. For example, if I don't Dim the Word object at the beginning of the function and an error occurs before the Dim line, but my error code in the Goto statement tries to quit the Word object even though it hasn't been Dim'ed yet. I suppose I could avoid the problem by using different Goto labels for before and after the object is Dim'ed, but it's really a broader question about checking whether variables have been declared before trying to set them to Nothing (or closing/quitting them). I get an error if I Set prjApp1 = Nothing in the error handler and the error occured before prjApp1 was Dim'ed.

To parrot CJ, include Option Explicit in the header of every code module. If variable is not declared this will trigger compile error.

with a few exceptions variables should be dimmed at the beginning of a sub or function not in the code - and as advised before use Option Explicit

Youll never get error in Dim Statement. If your var is firm or repirt wise visible, put it before any proc sub you make. If proc wide adter the oroc/func declaration. Dim statement just prepare the memiry what type if variable to allocate.

Here's code you can use to kill a windows process.
This one kills msaccess.exe, so if you run it inside msaccess.exe...

Mark

But I thought CreateObject will already use an App that's running, so what difference does GetObject make?

Click to expand...

Specifically in the case of Outlook objects, you MUST try to use GetObject first and only use CreateObject if you cannot find an extant active copy of MSOUTLOOK.EXE, because Outlook really doesn't like you to try to create another Outlook. Something about GetObject just doesn't work as you describe when the object is based on Outlook. I do not know why but it just doesn't. I've never seen an adequate explanation in any of the MS online documentation, either.

I believe you are right when it comes to Excel and Word so if that is all you were running, no issue.

It is much better to always use CreateObject than GetObject. You might accidentally closed Application and then your Access will have a runtime error.