How To Dismiss a Dialog Box Displayed by an Office Application with Visual Basic

Article translations Article translations
Article ID: 259971 - View products that this article applies to.
This article was previously published under Q259971
Expand all | Collapse all

SUMMARY

When you are automating an Office application from Visual Basic (VB), the Office application may display a dialog box. The dialog box causes the Visual Basic application to appear to stop responding (or hang) because VB is waiting for the dialog box to be dismissed. The dialog box must be dismissed before the Visual Basic application can continue.

This article discusses how you can use the object models for Office applications to avoid dialog boxes during Automation. It also provides a step-by-step example of how to simulate user input to programmatically dismiss a dialog box that cannot be avoided by using the usual properties and methods exposed in the object models.

MORE INFORMATION

At times, you may want to automate an Office application but not require any user interaction with the Office application. In this case, if the Office application displays a dialog box, your application appears to stop responding until a user can dismiss the dialog box. However, there may not be a user sitting in front of the computer who can dismiss the dialog box.

Office applications were not designed for unattended execution. Therefore, an application that automates Office may sometimes encounter a dialog box displayed by the Office application. From normal testing of the application, you can usually determine which dialog boxes occur and write your code to avoid those particular dialogs boxes.

Following are some recommended strategies for avoiding dialog boxes while automating an Office application:
  • Determine if the property or method you are using (the one that is causing the dialog box) has optional arguments that you can pass to it. Sometimes, by passing all arguments to the property or method, you can avoid a dialog box. For example, if you are using the Open method to open an Excel workbook and that workbook is password protected, Excel displays a dialog box asking the user to enter the password if you do not provide the password argument when calling the Open method. To avoid the dialog box, provide a value for the Password argument when calling the Open method. Similarly, when using the Close method to close a document, it often helps to specify the SaveChanges argument to avoid a dialog box asking the user to save changes. For additional information about how to determine what arguments are available for the property or the method that you are calling, click the following article number to view the article in the Microsoft Knowledge Base:
    222101 How To Find and use Office object model documentation
  • Study the object model of the Office application to see if there may be a property that prevents certain dialog boxes. For example, the Excel Application object has AskToUpdateLinks and AlertBeforeOverwriting properties.
  • Set the Application.DisplayAlerts property (Excel, Project, Word) or use Application.DoCmd.SetWarnings False (Access only) to turn off the display of alert messages. Most, but not all, dialog boxes can be avoided using this setting.
  • Set the Application.FeatureInstall property (Office 2000 and later) to handle possible "This feature is not installed..." dialog boxes when accessing a component that may not be installed on the user's system.
  • Use the On Error statement to avoid run-time error messages that could occur, such as when trying to set the Application.ActivePrinter when no printer driver is installed on the user's system.
  • Test your application thoroughly to help anticipate when dialog boxes may occur. For example, suppose you call the SaveAs method of an Office application to save to a file. If that file already exists, a dialog box may appear asking confirmation to replace the existing file. If you modify your code to check for the file before calling the SaveAs method, you can avoid the possibility of the dialog box appearing. For example, if the file already exists, delete it using the Kill statement before calling the SaveAs method.
Note Even if you use these techniques and carefully design your application to avoid dialog boxes, you may still be faced with a situation where a dialog box cannot be avoided with the methods and properties exposed in the Office application's object model. In such situations, it might be necessary to programmatically dismiss a dialog box by simulating user input. The following demonstration illustrates how this can be accomplished with a Visual Basic Automation client.

Sample

The steps in this section demonstrate Automation of Microsoft Word to print a document. The Automation client calls the PrintOut method for the Word Document object. If the user's default printer is configured to print to the FILE port, then a call to PrintOut produces a dialog box prompting the user to enter a file name. To determine if the PrintOut method causes this dialog box to appear, the Visual Basic Automation client uses a Timer control to detect idle time after calling the PrintOut method. Prior to calling PrintOut, the Timer is enabled and set to fire in five seconds. When PrintOut completes, the Timer is disabled. Therefore, if the PrintOut method completes within five seconds, the Timer event never occurs and no further action is taken. The document is printed and the code execution continues beyond the PrintOut method. However, if the Timer event occurs within the five second interval, it is assumed that the PrintOut method has not completed and that the delay is caused by a dialog box waiting for user input. When the Timer event occurs, the Automation client gives focus to Word and uses SendKeys to dismiss the dialog box.

Note For demonstration purposes, this sample uses the PrintOut method in such a way that it displays a dialog box intentionally when it prints to a printer set to a FILE port. Please note that the PrintOut method has two arguments, OutputfileName and PrintToFile, that you can provide to avoid this dialog box.

Additionally, when using this "timer" approach, you can customize the wait time to be greater or less than five seconds, as well as customize the keystrokes you send to the dialog box.

This demonstration consists of two Visual Basic projects:
  1. An ActiveX EXE that provides a Timer class used to detect a delay. The reason to use an ActiveX EXE for the Timer class is to run the Timer code in a separate process and, therefore, a separate thread. This makes it possible for the Timer class to raise an event during a suspended automation call.
  2. A Standard EXE that uses automation to Word and calls the PrintOut method to print a document. It uses the ActiveX EXE to detect a delay when calling the PrintOut method.
Create the ActiveX EXE Project
  1. Start Visual Basic and create an ActiveX EXE project. Class1 is created by default.
  2. On the Project menu, click to select Properties, and then change the Project name to MyTimer.
  3. Copy and paste the following code into the Class1 module:
    Option Explicit
    
    Public Event Timer()
    Private oForm1 As Form1
    
    Private Sub Class_Initialize()
        Set oForm1 = New Form1
        oForm1.Timer1.Enabled = False
    End Sub
    
    Private Sub Class_Terminate()
        Me.Enabled = False
        Unload oForm1
        Set oForm1 = Nothing
    End Sub
    
    Public Property Get Enabled() As Boolean
        Enabled = oForm1.Timer1.Enabled
    End Property
    
    Public Property Let Enabled(ByVal vNewValue As Boolean)
        oForm1.Timer1.Enabled = vNewValue
        If vNewValue = True Then
            Set oForm1.oClass1 = Me
        Else
            Set oForm1.oClass1 = Nothing
        End If
    End Property
    
    Public Property Get Interval() As Integer
        Interval = oForm1.Timer1.Interval
    End Property
    
    Public Property Let Interval(ByVal vNewValue As Integer)
        oForm1.Timer1.Interval = vNewValue
    End Property
    
    Friend Sub TimerEvent()
        RaiseEvent Timer
    End Sub
    					
  4. On the Project menu, choose Add Form to add a new Form to the project.
  5. Add a Timer control to the form.
  6. Copy and paste the following code into the code module for Form1:
    Option Explicit
    
    Public oClass1 As Class1
    
    Private Sub Timer1_Timer()
        oClass1.TimerEvent
    End Sub
    					
  7. Save this project in a new subfolder named Server.
  8. On the File menu, choose Make MyTimer.Exe to build and register the component.
Create the Automation Client
  1. Create a new Standard EXE project in Visual Basic. Form1 is created by default.
  2. Add a CommandButton control to the form.
  3. On the Project menu, select References. Add references to the Microsoft Word 8.0 (or 9.0, or 10.0) Object Library, and to MyTimer.
  4. Copy and paste the following code to the form module:
    Option Explicit
    
    Private oWord As Word.Application
    Private strWordCaption As String
    Private WithEvents oMyTimer As MyTimer.Class1
    
    Private Sub Form_Load()
        'Create MyTimer object, and then disable it by default:
        Set oMyTimer = New MyTimer.Class1
        oMyTimer.Enabled = False
    End Sub
    
    Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
        'Terminate MyTimer object when the form is closed:
        oMyTimer.Enabled = False
        Set oMyTimer = Nothing
    End Sub
    
    Private Sub Command1_Click()
        On Error GoTo ErrorHandler
        
        'Create a new Word instance and put text in the new document:
        Set oWord = CreateObject("Word.Application")
        oWord.Visible = True
        oWord.Documents.Add
        oWord.Selection.TypeText "Hello World!"
        
        'Prepare Timer to "watch out" for a delay in calling PrintOut:
        strWordCaption = GetWordCaption 'for use with AppActivate
        oMyTimer.Interval = 5000 'allow 5 second wait time
        oMyTimer.Enabled = True
            
        'Call the PrintOut method, which may prompt the user to select
        'an output file name if the default printer is set to FILE:
        oWord.PrintOut Background:=False
    
    Done:
        On Error Resume Next
        'Turn off Timer:
        oMyTimer.Enabled = False
        
        'Close document and quit the Word instance:
        oWord.ActiveDocument.Close SaveChanges:=False
        oWord.Quit
        Set oWord = Nothing
        Exit Sub
    ErrorHandler:
        Resume Done
    End Sub
    
    Private Sub oMyTimer_Timer()
    'If this event occurs, there was a delay in calling PrintOut.
    'You can assume that the delay is caused by a dialog box prompting
    'for an output file name because the user has the printer
    'configured to print to FILE. SendKeys is used to provide the
    'output file name and dismiss the dialog box.
        Dim strKeys As String
        
        On Error Resume Next
        
        'Make sure that Word has the focus before using SendKeys to it:
    
        AppActivate strWordCaption 'Set focus to Word.
        
        'Send keystrokes to enter the output file name:
        If Right$(App.Path, 1) = "\" Then
            strKeys = App.Path & "MyOutput.prn"
        Else
            strKeys = App.Path & "\MyOutput.prn"
        End If
        Kill strKeys 'make sure file does not already exist
        strKeys = strKeys & "~" '~ represents the OK button to dismiss dialog
        SendKeys strKeys, True
           
        'Disable MyTimer:
        oMyTimer.Enabled = False
    End Sub
    
    Private Function GetWordCaption() As String
    'Returns the Word Caption. For use with the AppActivate statement
        Dim s As String
        On Error Resume Next
        If Left$(oWord.Version, 1) = "8" Then
            'Word 97 logic:
            s = oWord.Caption
        Else
            'Word 2000 or 2002 logic:
            Err.Clear
            s = oWord.ActiveWindow.Caption
            If Err.Number = 0 Then 'no error
                s = s & " - " & oWord.Caption
            Else
                s = oWord.Caption
            End If
        End If
        GetWordCaption = s
    End Function
    					
  5. Save this project in a new subfolder named Client.
  6. Press the F5 key to run the project. Form1 appears.
  7. Click Command1 on the form. This automates Word, adds a new document with some text, and then sends it to the printer by using the PrintOut method. You do not see a dialog box if your printer is configured to print to a printer.
  8. In the Windows Control Panel, change your default printer so that it is configured to print to the FILE port.
  9. Click Command1 again,and note that a dialog box appears in Word. Do not dismiss the dialog; wait five seconds and the dialog box is programmatically dismissed when the Timer event occurs. An output file named MyOutput.prn is created in the Client subfolder.
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 a Microsoft Certified Partner or Microsoft Advisory Services. For more information, visit these Microsoft Web sites:

Microsoft Certified Partners - https://partner.microsoft.com/global/30000104

Microsoft Advisory Services - http://support.microsoft.com/gp/advisoryservice

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

REFERENCES

For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
257757 INFO: Considerations for server-side automation of Office
226118 OFF2000: Programming resources for Visual Basic for Applications
253235 FILE: Offautmn.exe discusses Office 97 and 2000 automation and provides sample code
For additional information about Office automation, visit the Office Development Support Center at the following Microsoft Web site:
http://support.microsoft.com/ofd

Properties

Article ID: 259971 - Last Review: January 17, 2007 - Revision: 6.7
APPLIES TO
  • Microsoft Office Access 2003
  • Microsoft Access 2002 Standard Edition
  • Microsoft Access 2000 Standard Edition
  • Microsoft Access 97 Standard Edition
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft Office PowerPoint 2003
  • Microsoft PowerPoint 2002 Standard Edition
  • Microsoft PowerPoint 2000 Standard Edition
  • Microsoft PowerPoint 97 Standard Edition
  • Microsoft Office Word 2003
  • Microsoft Word 2002
  • Microsoft Word 2000
  • Microsoft Word 97 Standard Edition
  • Microsoft Visual Basic 5.0 Professional Edition
  • Microsoft Visual Basic 6.0 Professional Edition
  • Microsoft Visual Basic 5.0 Enterprise Edition
  • Microsoft Visual Basic Enterprise Edition for Windows 6.0
Keywords: 
kbautomation kbhowto kbprogramming KB259971

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com