XL: Prompt to Save Workbook After Using GetObject Function

ID: Q115313

The information in this article applies to:

Microsoft Excel for Windows, versions 5.0, 5.0c
Microsoft Excel for Windows 95, version 7.0

SYMPTOMS

In a Microsoft Visual Basic application, if you use the GetObject function with a filename to activate a file in Microsoft Excel, you are prompted to save changes to the file when you use the Quit method, even if you have not made any changes to the file.

CAUSE

When you use the GetObject function to activate the workbook as an object linking and embedding (OLE) Automation object, a hidden instance of Microsoft Excel starts, and the workbook is opened as hidden. Therefore, because this change has been made to the workbook, you are prompted to save changes when you quit the OLE Automation object.

WORKAROUND

To avoid receiving a dialog box with a message to save the file after you have activated a workbook as an OLE Automation object, close the workbook and use the xlfalse argument to avoid saving any changes, or the xltrue argument to save changes to the workbook. Note that when you use this method, Microsoft Excel remains running if it was running before you activated the OLE Automation object. The following is an example of using this method:


   ' Dimension variable xlsheet as object type
   Dim xlsheet As object
   ' Activate BOOK1.XLS and assign to variable xlsheet.
   ' Note: use the appropriate path for your file
   ' (this example assumes BOOK1.XLS has been saved in
   ' C:\EXCEL5\FILES).
   Set xlsheet = GetObject("C:\EXCEL5\FILES\BOOK1.XLS")
   ' Close workbook
   ' If Microsoft Excel is running when you run this procedure, the
   ' following statement does not close the application.
   ' If Microsoft Excel is not running when you run this procedure, the
   ' following statement closes the application.
   ' Use the xltrue argument in the following statement instead of
   ' xlfalse if you DO want to save changes to the file
   xlsheet.Parent.[close] xlfalse
   ' Quit Microsoft Excel
   Set xlsheet = Nothing

Note that the DisplayAlerts method does not work in this case to avoid the save changes dialog box.

Microsoft provides examples of Visual Basic 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. This Visual Basic procedure is provided 'as is' and Microsoft does not guarantee that it can be used in all situations. Microsoft does not support modifications of this procedure to suit customer requirements for a particular purpose. Note that a line that is preceded by an apostrophe introduces a comment in the code--comments are provided to explain what the code is doing at a particular point in the procedure. Note also that an underscore character (_) indicates that code continues from one line to the next. You can type lines that contain this character as one logical line or you can divide the lines of code and include the line continuation character. For more information about Visual Basic for Applications programming style, see the "Programming Style in This Manual" section in the "Document Conventions" section of the "Visual Basic User's Guide."

MORE INFORMATION

For more information about using the GetObject function to open a Microsoft Excel workbook, query on the following words in the Microsoft Knowledge Base:

getobject and filename and opens

REFERENCES

For more information about the Close Method, choose the Search button in Help and type:

Close Method

Additional query words: 3.00 5.00c dirty

Keywords : kbole kbprg
Version : WINDOWS:5.0,5.0c,7.0
Platform : WINDOWS
Issue type :