OLE Automation: GetObject Function with Filename Opens File

ID: Q108074

The information in this article applies to:

Microsoft Excel for Windows, versions 5.0, 5.0c

SYMPTOMS

In a Microsoft Visual Basic application, if you use the GetObject function with a filename to activate a file in Microsoft Excel, you may receive the following error message in Microsoft Excel:

Book1.xls is already open. Reopening will cause your changes to be discarded. Do you want to reopen?

CAUSE

When you use the GetObject function with a filename to activate a workbook in Microsoft Excel, the file is opened, whether or not the file is already open. If the file is already open, and contains changes you have not saved, the above error message is returned.

WORKAROUND

To activate the workbook BOOK1.XLS as an object linking and embedding (OLE) Automation object and avoid opening BOOK1.XLS if it is already open, do the following:

Activate the Microsoft Excel Application object.

Check for an open workbook called BOOK1.XLS.

Activate BOOK1.XLS if it is open.

-or-

Open BOOK1.XLS if it is not open.

The following sample code demonstrates the workaround described above. The code scans the open workbooks in Microsoft Excel and activates BOOK1.XLS if it is open.

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."


   'Dimension variable xl as object type
   Dim xl As Object
   'Activate Microsoft Excel and assign to variable xl
   Set xl = GetObject(, "Excel.application.5")
   'Set n to number of open workbooks
   n = xl.Workbooks.Count

      'Loop through each open workbook
      For c = 1 To n
         'Test to see if workbook is BOOK1.XLS
         'If true, activate BOOK1.XLS and exit loop
         'BOOK1.XLS in this line of code must be capitalized
         If xl.Workbooks(c).Name = "BOOK1.XLS" Then
            xl.Workbooks("BOOK1.XLS").Activate
            Exit For
         End If
      Next c

MORE INFORMATION

You can use the Visual Basic GetObject function to access an OLE automation object and assign the object to an object variable. You can then use the object variable to reference the OLE automation object in your Visual Basic code.

For more information about the GetObject Function or OLE Automation, choose the Search button in Visual Basic Help and type:


   OLE Automation

Additional query words:

Keywords : xlwin
Version : 5.00
Platform : WINDOWS
Issue type :