Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

BUG: VB Hangs While Automating Excel Using OLE Control


View products that this article applies to.

Symptoms

Your application hangs while automating Microsoft Excel 97 or later via the OLE control.

↑ Back to the top


Cause

This problem occurs while automating an Excel object that is using in-place activation in an OLE control. The problem does not occur with all methods or properties. This problem has been reported with the following method/properties in the Microsoft Excel object library:

PrintOut
Delete
Dialog.Show

↑ Back to the top


Resolution

A workaround is to not activate the object while running the automation code. However, if you need to activate the object, do so in a separate window instead of using in-place activation. For example, this line activates the object in a separate window:
OLE1.DoVerb vbOLEOpen
				
Another workaround is to deactivate the object before calling the automation code that causes Visual Basic to hang. For example, the PrintOut and Delete methods cause this problem to occur. Before using these methods, you can deactivate the object prior to calling the method, then optionally reactivate the object after calling the method. See the MORE INFORMATION section later in this article for an example.

↑ Back to the top


Status

Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article.

↑ Back to the top


More information

Steps to Reproduce Behavior

  1. Start a new Standard EXE project in Visual Basic. Form1 is created by default.
  2. On the Project menu, click References, and then select the Microsoft Excel 8.0 object library. For Excel 2000, select the version 9.0 object library.
  3. Place an OLE control on Form1 and create a new Microsoft Excel worksheet. The OLE control properties should be:

    PropertyValue
    NameOLE1
    ClassExcel.Sheet.8
  4. Place a CommandButton on Form1.
  5. Copy the following code to the Code Window of Form1:
          Private Sub Command1_Click()
             Dim xlBook As Excel.Workbook
             Set xlBook = OLE1.object
             OLE1.DoVerb vbOLEShow 'or OLE1.DoVerb vbOLEUIActivate
             xlBook.Worksheets("Sheet1").Range("A1").Value = "Hello"
             xlBook.Worksheets("Sheet1").PrintOut
             Set xlBook = Nothing
          End Sub
    					
  6. On the Run menu, click Start, or press the F5 key to start the program.
  7. Click the CommandButton. Visual Basic hangs after it encounters the PrintOut method. The object will be correctly printed, but Visual Basic stops responding. You must select End Task in the task list (by pressing the CTRL+ALT+DEL keys) to end the Visual Basic project and Excel. Repeat until both Excel and the Visual Basic project are removed from the task list.
  8. Repeat the steps again with the code modified as follows:
          Private Sub Command1_Click()
             Dim xlBook As Excel.Workbook
             Set xlBook = OLE1.object
             OLE1.DoVerb vbOLEShow 'or OLE1.DoVerb vbOLEUIActivate
             xlBook.Worksheets("Sheet1").Range("A1").Value = "Hello"
             Command1.SetFocus 'causes deactivation of object
             xlBook.Worksheets("Sheet1").PrintOut
             OLE1.DoVerb vbOLEShow 'optional: reactivates object
             Set xlBook = Nothing
          End Sub
    					
    The problem is avoided by deactivating the object before calling the PrintOut method. Setting focus to any control on the form will deactivate the OLE control object.

↑ Back to the top


Keywords: kbbug, kbctrl, kbpending, KB181889

↑ Back to the top

Article Info
Article ID : 181889
Revision : 7
Created on : 1/22/2007
Published on : 1/22/2007
Exists online : False
Views : 450