?
Solved

VBA - From MS Access How to determine if MS Word is running.

Posted on 2011-10-09
47
Medium Priority
?
1,107 Views
Last Modified: 2012-05-12
Using MS Access 2003. I open a Word document, but need to know how to determine if MS Word is closed before closing a MS Access form. The Word application object is 'wo'. I get the run-time error as mentioned below.

Private Sub cmdbCloseCC_Click()
On Error GoTo PROC_ERR
    Dim wn As String
    wn = "Word.Application"
    If Me.IsAppRunning(wn) = True Then
        wo.Quit
        Set wo = Nothing
    End If
   
    DoCmd.Close acForm, "frmCC_Status"
    Application.Quit
PROC_EXIT:
    Exit Sub
PROC_ERR:
    MsgBox Err.Description
    Resume PROC_EXIT
End Sub
___________________________________________

Function IsAppRunning(ByVal appName) As Boolean
    Dim oApp As Object
    On Error Resume Next
   
    Set oApp = GetObject(, appName) 'Error here - Run-time error '429': ActiveX component can't create object
    Debug.Print oApp
    If Not oApp Is Nothing Then
        Set oApp = Nothing
        IsAppRunning = True
    End If
End Function
0
Comment
Question by:jbotts
  • 19
  • 19
  • 7
  • +1
47 Comments
 
LVL 12

Expert Comment

by:danishani
ID: 36940003
Try to remove the Me. for the Function IsAppRunning.

So change this into this:
Private Sub cmdbCloseCC_Click()
On Error GoTo PROC_ERR
    Dim wn As String
    wn = "Word.Application"
    If IsAppRunning(wn) = True Then ' without the Me.
        wo.Quit
        Set wo = Nothing
    End If
    
    DoCmd.Close acForm, "frmCC_Status"
    Application.Quit
PROC_EXIT:
    Exit Sub
PROC_ERR:
    MsgBox Err.Description
    Resume PROC_EXIT
End Sub

Open in new window


Hope this helps,
0
 

Author Comment

by:jbotts
ID: 36940024
danishani:
The return of IsAppRunning(wn) is now True and should be False as there is no Word application running. The wo.Quit is for a modular variable which is a Word.Application.

Thanks
0
 
LVL 12

Expert Comment

by:danishani
ID: 36940047
You need to add an ELSE statement, in case it return false (cq Instance not open):
On Error GoTo PROC_ERR
    Dim wn As String
    wn = "Word.Application"
    If IsAppRunning(wn) = True Then
        wo.Quit
        Set wo = Nothing
   Else
       ' instance is not open
       Msgbox "Application is closed."
    End If
    
    DoCmd.Close acForm, "frmCC_Status"
    Application.Quit
PROC_EXIT:
    Exit Sub
PROC_ERR:
    MsgBox Err.Description
    Resume PROC_EXIT
End Sub

Open in new window


Hope this helps,
Daniel
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 12

Expert Comment

by:danishani
ID: 36940056
Make sure there is no Word Instance running by checking the Task Manager.
Sometimes the WORD instance is still in there, if its not closed propperly or 'hanging'.
0
 

Author Comment

by:jbotts
ID: 36940061
I checked and there is no Word instance listed in the Task Manager
0
 
LVL 12

Expert Comment

by:danishani
ID: 36940076
Hmm I run the code and it returns False when there is No word instance open and True when there is.

Did you copied the Funcion into a seperate Module?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36940094
>> Set oApp = GetObject(, appName)

GetObject will return error 429 if there is no instance of that application to "get" (ie: if there is no word application open).

To get the results you want, you need to handle error 429.  If it is raised by GetObject, there is no application running.  If it is not raised by GetObject, there IS an application running to 'get'.

This should do the trick:


Function IsAppRunning(ByVal appName) As Boolean
    Dim oApp As Object
    On Error GoTo EH
    
    Set oApp = GetObject(, appName) 'Error here - Run-time error '429': ActiveX component can't create object
    Debug.Print "oApp:" & oApp
    If Not oApp Is Nothing Then
        Set oApp = Nothing
        IsAppRunning = True
    End If
    Exit Function

EH:
    If Err.Number = 429 Then
        ' there is no Word Application open to 'get'
        IsAppRunning = False
    Else
        MsgBox "Error " & Err.Number & ": " & Err.Description
    End If
    
End Function

Open in new window

0
 

Author Comment

by:jbotts
ID: 36940100
The code is all in one module. I always get True, no matter if the Word application is running or not.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36940106
Before tesing the code I posted, make certain that there are no "hidden" instances of Word running from previous tests of this code.  You can do this through Task Manager, but the easiest way is to log out of your windows user account and log back in again (that will terminate any instances that may be running behind the scenes)
0
 

Author Comment

by:jbotts
ID: 36940127
mbizup:
I added the error handler and got the message "Error: 0".
jbotts
0
 

Author Comment

by:jbotts
ID: 36940131
mbizup:
I also logged out and then got the original error: "Run-time error '429': ActiveX component can't create object"
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36940134
jbotts,

I believe you can also streamline the code I posted a bit.  GetObject will either return error 439, or it will return an Object, so with the handling for error 429 in place you don't need the check for "= Nothing".  You should be able to reduce the code to this:

Function IsAppRunning(ByVal appName) As Boolean
    Dim oApp As Object
    On Error GoTo EH
    
    Set oApp = GetObject(, appName) 'Error here - Run-time error '429': ActiveX component can't create object
    Debug.Print "oApp:" & oApp

    Set oApp = Nothing
    IsAppRunning = True

    Exit Function

EH:
    If Err.Number = 429 Then
        ' there is no Word Application open to 'get'
        IsAppRunning = False
    Else
        MsgBox "Error " & Err.Number & ": " & Err.Description
    End If
    
End Function 

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
ID: 36940137
Did you copy/paste the entire function?

These lines are critical for the error handling to work:

>>>     On Error GoTo EH
We don't want to resume to the next line

>>>     Exit Function

We want to exit normally if no errors are encountered.


Also, make sure that you have set error handling to "Break on unhandled errors"

Do this from the VB Editor:

Tools --> Options --> general --> Error Trapping



0
 

Author Comment

by:jbotts
ID: 36940157
mbizup:
I did the above. Without an instance of Word, IsAppRunning still returns True. Before I posted the question, I did copy and paste the whole function.
jsbotts
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36940197
Hmmm....  I've tested my code fairly extensively, and we are seeing very different results.

To make sure we are thinking on the same page, go ahead and post your function exactly as you are using it now, with my suggested modifications.
0
 

Author Comment

by:jbotts
ID: 36940208
Function IsAppRunning(ByVal appName) As Boolean
    Dim oApp As Object
    On Error GoTo EH
    On Error Resume Next
   
    Set oApp = GetObject(, appName) 'Error appears here
    Debug.Print oApp
    Set oApp = Nothing
    IsAppRunning = True
    Exit Function
   
EH:
    If Err.Number = 429 Then
        ' there is no Word Application open to 'get'
        IsAppRunning = False
    Else
        MsgBox "Error " & Err.Number & ": " & Err.Description
    End If

End Function

0
 
LVL 12

Accepted Solution

by:
danishani earned 1000 total points
ID: 36940209
Another thing you can try to see if there is nothing 'wrong' with your current Database.

Create a New Database. Copy the function into a new module. Save it.
Then create a New Form, place a Command Button on it.
In the Command Button place the following code:
   
    Dim wn As String
    wn = "Word.Application"
    If IsAppRunning(wn) = True Then
        MsgBox "Word is open"
    Else
    MsgBox "Word is not open"
    End If

Open in new window


See if return the right message.

Hope this helps,
Daniel
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36940231
Okay - Looking at your code, I see where we miscommunicated.

< I did copy and paste the whole function.>

My intent in that question was to ask whether you copy/pasted the function exactly as *I* had posted it.

There is more to it than just the error handling.

Try this again:

1. Log out of your windows account to kill any hidden Word processes.
2. Copy/paste and run the function exactly as posted  in either of my comments

Let me know the results...
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36940242
You want to get rid of that "On Error Resume Next" line in the code you just posted.  It is effectively cancelling out the custom error handling that we added.
0
 

Author Comment

by:jbotts
ID: 36940253
Here is the code as it stands now:
Function IsAppRunning(ByVal appName) As Boolean
    Dim oApp As Object
    On Error GoTo EH

    Set oApp = GetObject(, appName) 'Error appears here
    Debug.Print oApp
    Set oApp = Nothing
    IsAppRunning = True
    Exit Function
   
EH:
    If Err.Number = 429 Then
        ' there is no Word Application open to 'get'
        IsAppRunning = False
    Else
        MsgBox "Error " & Err.Number & ": " & Err.Description
    End If

End Function
0
 

Author Comment

by:jbotts
ID: 36940262
I also created the new database, and with no instance of Word, it gave me the message of "Word is open."
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36940273
That definitely works for me -

ie:

I open Word, and it returns true; close Word and it returns False.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36940277
Are you seeing any instances of WinWord.Exe in Task manager?
0
 
LVL 12

Expert Comment

by:danishani
ID: 36940279
Same here, even with the original code, it works as aspected...
0
 

Author Comment

by:jbotts
ID: 36940280
I have rebooted my computer and still get the same result on the new database and on the original one.
0
 

Author Comment

by:jbotts
ID: 36940283
I am working with MS Access 2003. Would that make a difference?
0
 
LVL 12

Expert Comment

by:danishani
ID: 36940294
No, Access version does not make a difference. Something very strange is going on.
Can you do the same test, and then change the instance into "Excel.Application".

See what message returns?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36940309
How exactly are you "killing" your Word Application objects?

>> Set objWord = Nothing

Just setting it to nothing doesnt necessarily do the trick if you create an oblect in code; it just clears the memory taken up by the object to my understanding.

To fully kill a word (or Excel or anything else ) application, you need to do something like this

objWord.Quit  '<--- Closes Word
Set objWord = Nothing  '<-- Frees the object variable
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36940311
Although it looks like you may already be doing both.
0
 

Author Comment

by:jbotts
ID: 36940317
I get the same result using Excel.Application. I am using Windows 7. Does that make a difference?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36940318
No - I'm testing this on a Windows 7 machine myself.
0
 

Author Comment

by:jbotts
ID: 36940320
I do use objWord.Quit and then Set objWord = Nothing.
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 1000 total points
ID: 36940338
Move the function out of your form's module  to a seperate public module.  Make sure you save that module with any name other than the same name of the function. (modGenericFunctions would be a decent choice).

Then for sanity's sake log out and back in to windows, restart your database in design view, and without using any other functions, forms or anything else in your database, try this from the immediate window at the bottom of the VBA Editor (ie: running it completely independently of other functions in your database):

?  IsAppRunning(ByVal appName)


Then open Word and do the same

?  IsAppRunning(ByVal appName)


Then close Word and do the same

?  IsAppRunning(ByVal appName)

0
 
LVL 61

Expert Comment

by:mbizup
ID: 36940342
Sorry -

Those lines should all be:

? IsAppRunning("Word.Application")
0
 

Author Comment

by:jbotts
ID: 36940373
In the Immediate Window the function returns true under all circumstances.
I did a 'Debug.Print oApp' in the function. When an instance of Word is open oApp is Microsoft Word, and when there is not an instance of Word oApp is evidently Null as nothing is printed.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36940390
Agree with danishani that there is "something very strange going on", and I can't quite place a finger on it...

I think dani and I are both seeing 'expected results'.

I need to get some sleep,  but will check back in the morning to see whether this has gotten any further.
0
 

Author Comment

by:jbotts
ID: 36940394
Thanks so much for the efforts mbizup and danishani. I will work on it a little longer.
jbotts
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36941542
<In the Immediate Window the function returns true under all circumstances. >

Just verifying... You did try danishani's suggestion to place the function in a brand new database with nothing else, and the results were the same, correct?

Try this --

1.  In a new windows login session Ctrl-alt-del, bring up Task Manager.  Are there any instances of  Microsoft Word showing in Task Manager under the Applications tab, or "WINWORD.EXE" under the processes tab?

2.  Open a brand new database in Access 2003.  Open Task Manager again, and repeat the same checks with Access open.

Post back with your findings.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36941583
Also try this from the immediate window in your new, empty Access Database:


?  GetObject(, "word.application")

What does that print in the debug window (or does it give you an error) ?


Also, if possible, try this function on a different computer.
0
 
LVL 77

Expert Comment

by:peter57r
ID: 36941680
Are you absolutely sure that there is no copy of Word running when you execute this command.
I don't know how you have been checking, but you should use Ctrl-Alt-del to get into Task Manager and sort the processes alphabetically; look for WinWord.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36941753
Pete,

I'm wondering if he doesn't have an Add-In for Word running.  Do you think that could cause the behavior he's seeing?
0
 
LVL 77

Expert Comment

by:peter57r
ID: 36941801
Well I did wonder whether he is using another product/application which itself uses Word as a component.  I think that is pretty much what you are asking also?
 
But I don't have any simple means of testing it.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36941860
<  I think that is pretty much what you are asking also?>

Yes - I'm not sure how to conclusively test for it either.

___

One other thought here... If I'm understanding this correctly, the goal in using this function is to close out Word objects that were opened through Access code.  I'm not sure that this is the best approach.  If Word is open it doesn't necessarily prove that the object/instance opened through the application's code was left open.

Maybe a better check would be to test whether objWord (or whatever it is named) has a value.
0
 

Author Comment

by:jbotts
ID: 36941977
I have completed all the recommendations. The Task Manager did not list MS Word in Access or with Access not running. ?GetObject("Word.Application") in the immediate window returned "Microsoft Word". Using the following in the function I get the appropriate results:

   Set oApp = GetObject(, appName)
    If (Err.Number = 0) Then
        MsgBox "Word is open"
    Else
        MsgBox "Word is not open"
    End If
0
 

Author Comment

by:jbotts
ID: 36941996
I retract that. Last night it worked. Now it doesn't.
0
 

Author Comment

by:jbotts
ID: 36942027
Now it does work. I had to log off and log back on before it worked. Will keep testing it and see if I can find why it works intermittently.
0
 

Author Closing Comment

by:jbotts
ID: 36953300
I appreciate the time and learned something from both.
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question