XL: Controlling Alerts and Updating in MS Excel OLE Server

ID: Q153043

The information in this article applies to:

SYMPTOMS

When Microsoft Excel is being used as an OLE Server during an OLE Automation session, the ScreenUpdating property and the DisplayAlerts property are always set to TRUE. Attempts by the controller application to reset either of these properties to FALSE through code in the controller application ("in-process"), will be ignored.

CAUSE

This happens because, during OLE Automation, each line of code that is sent to Microsoft Excel to be run from an OLE controller is being treated as a separate Microsoft Excel macro. The screen updating or alerts would only be turned off for that one line of code and be turned back on for the next line of code that is sent to Microsoft Excel to be run. Therefore, using the DisplayAlerts property or the ScreenUpdating property in this case is not effective.

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 the Microsoft fee-based consulting line at (800) 936-5200. 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/refguide/
In order to effectively disable screen updating or displaying alerts in Microsoft Excel while Microsoft Excel is an OLE server in an OLE Automation session, you must either make sure that the calls are made from Microsoft Excel by running a Microsoft Excel macro to perform the tasks or write the code in such a way as to prevent the occurrence in the first place.

Workaround 1: Running a Microsoft Excel Macro from an OLE Controller

You could use the Run method from the OLE controller to tell Microsoft Excel to run a macro that exists in Microsoft Excel. From this macro you can effectively include DisplayAlerts or ScreenUpdating before the commands in question.

If Microsoft Visual Basic version 4.0 is being used as the OLE controller application, you could alternatively place the Visual Basic code that controls Microsoft Excel into an in-process (DLL) OLE Server. The classes provided by the in-process server must be created in a Microsoft Excel macro, not by a Visual Basic program, unless the Visual Basic program is another DLL loaded by Microsoft Excel.

For additional information, please see the following article in the Microsoft Knowledge Base: 

   ARTICLE-ID: Q124494
   TITLE     : XL5: OLE Automation Example: Running Macro in Visual Basic
               3.0

Workaround 2: Write Code to Avoid the Behavior

You can anticipate what methods may cause a dialog box to appear and write the code to avoid the dialog box. This could also be true for screen updating, but it sometimes can't be avoided.

Following are two examples of code using Microsoft Project as the OLE controller application that will avoid prompts for user input. This code could easily be applied to other OLE controller applications that support OLE Automation.

The following Visual Basic for Applications macro uses OLE Automation to delete a sheet in a newly created workbook and to save the workbook to the hard drive. It avoids using the Delete method (which produces a warning message) and uses the Move method, instead, to avoid the message.

1. In Microsoft Project, create a new module sheet. To do this, on the 

   Tools menu, click Macros, and then click New. For the Macro Name, type
   "Delete_WorkSheet" and click OK.
2. In the module, on the Tools menu, click References.

3. In the References dialog box, select the Microsoft Excel 5.0 Object 

   Library check box and click OK.
4. On the new module sheet, enter the following macro (notice that the 
   first and last line of code already exists from step 1 and does not need
   to be repeated):

      Sub Delete_Worksheet()

         ' Dimension variables.

         ' This assumes that a reference has been made to
         ' the Microsoft Excel 5.0 Object Library.
         Dim oXL As Excel.Application
         Dim oWBook As Object

         ' Starts a new invisible instance of Microsoft Excel.
         Set oXL = CreateObject("Excel.Application")
         ' Adds a new workbook to the running instance of Microsoft Excel.
         Set oWBook = oXL.Workbooks.Add
         ' Moves the first sheet of the workbook into a new workbook
         ' and makes the new workbook active.
         oWBook.Sheets(1).Move
         ' Closes the new workbook containing the undesired sheet
         ' without saving changes.
         oXL.ActiveWorkbook.Close False
         ' Save the original workbook minus the first sheet with
         ' name as listed below. If running the procedure on the
         ' Macintosh, you will need to change the next line to a valid
         ' location on the hard drive similar to the following:
         ' oWBook.SaveAs FileName:="Macintosh HD:test.xls"
         '
         oWBook.SaveAs FileName:="C:\my documents\test.xls"
         ' Closes the original workbook without saving changes.
         oWBook.Close False
         oXL.Quit    ' Closes the invisible instance of Microsoft Excel.

         ' Clear memory by removing the contents of the two object
         ' variables created.
         Set oXL = Nothing
         Set oWBook = Nothing

      End Sub
The following Visual Basic for Applications macro uses OLE Automation to save a workbook to the hard drive with the same name as an existing workbook. It avoids the prompt "Replace existing file?" by saving the file as another file name and renaming the file back to the desired name. The same algorithm could be used when you're opening a Microsoft Excel file that's in an earlier file format and re-saving it as the same name in the normal Microsoft Excel format.

1. In Microsoft Project, create a new module sheet. To do this, on the 

   Tools menu, click Macros, and then click New. For the Macro Name, type
   "Avoid_Replace_Existing" and click OK.
2. In the module, on the Tools menu, click References.

3. In the References dialog box, select the Microsoft Excel 5.0 Object 

   Library check box and click OK.
4. On the new module sheet, enter the following macro (notice that the 
   first and last line of code already exists from step 1 and does not need
   to be repeated):

      Sub Avoid_Replace_Existing()

         ' Dimension variables.

         ' This assumes that a reference has been made to
         ' the Microsoft Excel 5.0 Object Library.
         Dim oXL As Excel.Application
         Dim oWBook As Object
         Dim Fname As String

         ' Assign workbook file & path that will be replaced to string
         ' variable. If running the procedure on the Macintosh, you will
         ' need to change the next line to a valid location on the hard
         ' drive similar to the following:
         ' Fname = "Macintosh HD:test.xls"
         '
         Fname = "C:\my documents\test.xls"
         ' Starts a new invisible instance of Microsoft Excel.
         Set oXL = CreateObject("Excel.Application")
         ' Adds a new workbook to the running instance of Microsoft Excel.
         Set oWBook = oXL.Workbooks.Add

         ' Checks to see if the file already exists.
         If Dir(Fname) <> "" Then
            ' Turn off error checking in case the file, "temp.xls"
            ' does not exist and causes an error when we try to delete it.
            On Error Resume Next
            ' Delete the temporary file (if it exists).
            ' If running the procedure on the Macintosh, you will need
            ' to change the next line to a valid location on the hard
            ' drive similar to the following:
            ' Kill "Macintosh HD:temp.xls"
            '
            Kill "C:\temp.xls"
            ' Disables "On Error Resume Next" and will allow
            ' Microsoft Excel to halt with an error for the remainder
            ' of the code.
            On Error GoTo 0
            ' Save the file in the normal format as 'temp.xls'
            ' If running the procedure on the Macintosh, you will need
            ' to change the next line to a valid location on the hard
            ' drive similar to the following:
            ' oXL.ActiveWorkbook.SaveAs FileName:="Macintosh HD:temp.xls"
            '
            oXL.ActiveWorkbook.SaveAs FileName:="C:\temp.xls"
            ' Close the workbook without saving changes.
            oXL.ActiveWorkbook.Close savechanges:=False
            Kill Fname 'Deletes the original file from the Hard Disk.
            ' Renames "temp.xls" as the file & path in the variable Fname.
            ' If running the procedure on the Macintosh, you will need
            ' to change the next line to a valid location on the hard
            ' drive similar to the following:
            ' Name "Macintosh HD:temp.xls" As Fname
            '
            Name "C:\temp.xls" As Fname
         Else
            ' Otherwise, if the file did not already exist in the path
            ' given, just save it there normally.
            oXL.ActiveWorkbook.SaveAs FileName:=Fname
         End If
         oXL.Quit ' Closes the invisible instance of Microsoft Excel.
         Set oXL = Nothing 'Removes the variable from memory.
         Set oWBook = Nothing 'Removes the variable from memory.
      End Sub

STATUS

This behavior is by design.

MORE INFORMATION

There are some tasks in Microsoft Excel that will cause Microsoft Excel to interact with the user. For example, closing a workbook without saving changes will cause Microsoft Excel to ask if the user wants to save changes; deleting a sheet in a workbook will ask if the user is sure he or she wants to delete it; saving a workbook to a name that already exists will ask the user if the user is sure he or she wants to replace it. These prompts may be inappropriate and confusing to the user. These prompts can be disabled in a Visual Basic for Application macro in Microsoft Excel by using the following line of code: 

   Application.DisplayAlerts = FALSE
For additional information, please see the following article in the Microsoft Knowledge Base: 

   ARTICLE-ID: Q129153
   TITLE     : How to Avoid "Save Changes?" When You Close a Workbook
Also, performing some tasks in Microsoft Excel will cause frequent screen updates in Microsoft Excel and cause the screen to flash. This behavior may also be inappropriate and confusing to the user. This may also slow down Visual Basic for Applications macros that are run in Microsoft Excel. This screen updating can be disabled in a Visual Basic for Application macro in Microsoft Excel by using the following line of code: 

   Application.ScreenUpdating = FALSE
However, neither of these properties are effectively set to FALSE when they're being run in a line of code from an OLE controller application (for example, Microsoft Project version 4.1 for Windows 95, Microsoft Project version 4.0 for the Macintosh, Microsoft Word version 7.0 for Windows 95, Microsoft Word version 6.0 for the Macintosh, Microsoft Visual Basic version 4.0 for Windows 95). This is because each line of code is being treated as a separate Microsoft Excel macro when commands are sent to Microsoft Excel through OLE Automation.

REFERENCES

"Object Programming with Visual Basic 4," version 4.0, Chapter 9, "Miscellaneous Gotchas: Surprises in Working with Microsoft Excel and Visual Basic"

"Using MS Excel as an OLE Automation Object" by Tim Tow, version 5.0, Microsoft Technet CD, April '96, p. 21

For more information about OLE Automation in Microsoft Excel version 7.0, click Answer Wizard on the Help menu and type: 

   OLE Automation
For more information about DisplayAlerts in Microsoft Excel version 7.0, click Answer Wizard on the Help menu and type: 

   DisplayAlerts
For more information about ScreenUpdating in Microsoft Excel version 7.0, click Answer Wizard on the Help menu and type: 

   ScreenUpdating
Additional query words: 5.00 5.00a 5.00c 7.00 
Keywords          : kbcode kbprg PgmHowto 
Version           : WINDOWS: 5.0, 5.0c, 7.0; MACINTOSH: 5.0, 5.0a
Platform          : MACINTOSH WINDOWS
Issue type        : kbprb

Last Reviewed: May 17, 1999