XL97: Cannot Programmatically Close Referenced Workbook

• Microsoft Excel 97 for Windows

SYMPTOMS

When you set an object variable to a workbook that references another workbook, you may be unable to programmatically close the referenced workbook even when the workbook containing the reference is closed. You may receive the error:

   Run-time error '1004':
   This workbook is currently referenced by another workbook and
   cannot be closed.

   Sub OpenClose()
       Dim wb1, wb2

       'Open the workbooks and set the object variables
       Set wb1 = Workbooks.Open("Book1.xls")
       Set wb2 = Workbooks.Open("Book2.xls")

       'Close both workbooks
       wb2.close
       wb1.close      ' ---- This line produces the error

   End Sub

CAUSE

This problem occurs if the object variable is still set to the workbook that contains the reference to the workbook you are trying to close.

You cannot close a workbook in Microsoft Excel 97 while that workbook is referenced by another open workbook or by an object variable that is set to the referenced workbook.

RESOLUTION

To correct this problem, set the object variable for the workbook that contains the reference to Nothing prior to closing the referenced workbook. By setting the object variable to Nothing, it no longer refers to the workbook. The macro below demonstrates how to set the object variable to Nothing so that the referenced workbook can be closed programmatically:

   Sub OpenClose()
       Dim wb1, wb2

       'Open the workbooks and set the object variables
       Set wb1 = Workbooks.Open("Book1.xls")
       Set wb2 = Workbooks.Open("Book2.xls")

       'Close book2
       wb2.close

       'Set the object variable wb2 to Nothing
       Set wb2 = Nothing

       'Close book1
       wb1.close

   End Sub

STATUS

This is by design in Microsoft Excel 97.

MORE INFORMATION

Microsoft provides examples of Visual Basic for Applications procedures 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. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft support engineers can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.