ACC: Automation Does Not Close Microsoft Excel (145770)

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

Microsoft Excel does not automatically close after an application, such as one written in Microsoft Access, uses Automation to open Microsoft Excel and then closes. This article shows you how to close Microsoft Excel when you use Automation from Microsoft Access to open Excel.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access version 2.0. For more information about Access Basic, please refer to the "Building Applications" manual.

MORE INFORMATION

When you use Automation to control Microsoft Excel and you want the instance of Microsoft Excel to close, you must use the Quit method. Otherwise, the instance of Microsoft Excel will remain open.

If the instance of Microsoft Excel is hidden and remains open, a memory shortage could result. For example, as a developer, you may create several hidden instances of Microsoft Excel with the misunderstanding that the instances will automatically close when your client application is closed. It is not visibly apparent that hidden instances remain open and are using memory. To avoid possible memory shortages, you should use the Quit method to close the instance of Microsoft Excel, or you should unhide the instance (see the examples later in this article) so that the user can manually close it.

The syntax for the Quit method depends on your version of Microsoft Access.

Microsoft Access 2.0

In Microsoft Access 2.0, you must put brackets around methods that are common to both Microsoft Access and the application your are controlling through Automation in order for your code to compile. The Close and the Quit methods are examples of this. The following function creates a new instance of Microsoft Excel, unhides it, and then closes it with the Quit method. Note that brackets are placed around the Quit method.

   Function OLE_To_Excel ()
      Dim objExcel As Object
      Set objExcel = CreateObject("Excel.Application")
      objExcel.Visible = True
      objExcel.[Quit]           ' Brackets around Quit are required.
                                ' If you don't use brackets around Quit,
                                ' the code will not compile.

      Set objExcel = Nothing    'optional.
   End Function

Microsoft Access 7.0 and 97

Microsoft Access 7.0 and 97 do not require brackets around "common" methods in order for your Automation code to compile. In fact, some methods (such as the Close method) generate a run-time error if you enclose them in brackets. Brackets around the Quit method will not generate an error; however, with brackets around the Quit method, Microsoft Excel will not close. Therefore, in Microsoft Access 7.0 and 97, you should not use brackets in your Automation code. The following function creates a new instance of Microsoft Excel, unhides it, and then closes it with the Quit method.

    Function OLE_To_Excel()
      Dim objExcel As Object
      Set objExcel = CreateObject("Excel.Application")
      objExcel.Visible = True
      objExcel.Quit             ' Must not use brackets around Quit.
                                ' If you put brackets around Quit,
                                ' Microsoft Excel will not close.

      Set objExcel = Nothing    ' optional.
   End Function

Converting from Microsoft Access 2.0

If you have a database application created in Microsoft Access 2.0 that uses Automation and you want to use this application in Microsoft Access 7.0 or 97, you should convert the database and manually remove any brackets in your Automation code.