ACC2000: Automation Object Disappears When Code Finishes Running

ID: Q208388

The information in this article applies to:

Microsoft Access 2000

Moderate: Requires basic macro, coding, and interoperability skills.

SYMPTOMS

When you use Automation to control another application, such as Microsoft Word or Microsoft Excel, the application quits as soon as your procedure finishes running.

CAUSE

The application quits because its object variable loses scope.

RESOLUTION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

If you do not want the Automation server application to close when your code finishes running, use one of the following methods.

Method 1

Declare the object variable in the Declarations section of your code module as follows:

Open the sample database Northwind.mdb.

Create a module and type the following line in the Declarations section:


Dim xlApp as Object

Type the following procedure:


Sub LeaveXLOpen1()
   Set xlApp = CreateObject("Excel.Application")
   xlApp.Visible = True
End Sub

To test this function, type the following line in the Immediate window, and then press ENTER:


LeaveXLOpen1

Note that Microsoft Excel starts and remains visible after the procedure stops running. Microsoft Excel quits automatically when you close your database because that is when the object variable loses scope.

NOTE: If you declare your object variable in the Declarations section of a form or report module, Microsoft Excel remains open only as long as the form or report remains open.

Method 2

Declare the object variable as a Static variable at the procedure level as follows:

Open the sample database Northwind.mdb.

Create a module and type the following procedure:


Sub LeaveXLOpen2()
   Static xlApp as Object
   Set xlApp = CreateObject("Excel.Application")
   xlApp.Visible = True
End Sub

To test this function, type the following line in the Immediate Window, and then press ENTER:


LeaveXLOpen2

Note that Microsoft Excel starts and remains visible after the procedure stops running. Microsoft Excel quits automatically when you close your database because that is when the object variable loses scope.

NOTE: If you declare the Static variable as part of a procedure in a form or report module, Microsoft Excel remains open only as long as the form or report remains open.

MORE INFORMATION

Where and how you declare a variable in a code module determines its scope and its lifetime. "Scope" is defined as the availability of a variable, constant, or procedure for use by another procedure. "Lifetime" is defined as the time during which a variable retains its value.

When you declare an object variable within a procedure, the variable retains its value only as long as the procedure is running, as shown in the example in the "Steps to Reproduce Behavior" section.

Steps to Reproduce Behavior

Open the sample database Northwind.mdb.

Create a module and type the following procedure:


Sub OpenAndClose()
Dim xlApp as Object
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
End Sub

To test this function, type the following line in the Immediate Window, and then press ENTER:
```
OpenAndClose 
```
Note that Microsoft Excel starts and then quits. That is because its object variable, xlApp, loses scope as soon as the procedure stops running.

REFERENCES

For more information about the scope and lifetime of variables, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type "understanding the lifetime of variables" in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about using the CreateObject function, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type "CreateObject function" in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about declaring Static variables, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type "Static statement" in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about quitting Office applications after you run Automation code, please see the following article in the Microsoft Knowledge Base:

Q210129 ACC2000: Applications Run from Automation Don't Always Close

Additional query words:

Keywords : kbinterop kbdta IntpOlea
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbinfo