Microsoft KB Archive/128711

= Visual Basic Example Using the Verb Method =

Article ID: 128711

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition

-



This article was previously published under Q128711



SUMMARY
In Microsoft Excel you can use the Verb method to activate an embedded OLE object. For example, you can use the verb Open or Primary (represented by the xlOpen and xlPrimary constants, respectively).



MORE INFORMATION
When you use the Open verb (xlOpen is the constant for this verb), it opens the application in its own window. When you use the Primary verb (xlPrimary is the constant for this verb) it activates the object and prepares it for in-place editing. The following example uses a Microsoft Word version 6.0 object.

Sample Visual Basic Code
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."

The following Visual Basic procedures use both the xlOpen and xlPrimary constants. The example also includes a subroutine that clears out the embedded Word object. Sub Embedded_Ole_Object_Using_xlPrimary 'Activates the object ActiveSheet.DrawingObjects("Picture 1").Verb (xlPrimary) Set myobj = CreateObject("word.basic") 'Creates a Word Basic object With myobj .Insert "Hello"                     'Word Basic commands .Insertpara .Insertpara .Insert "Goodbye" End With

Set myobj = Nothing                     'clears the object Range("a1").Select                      'selects cell A1 End Sub

Sub Embedded_Ole_Object_Using_xlOpen 'Opens up Word ActiveSheet.DrawingObjects("Picture 1").Verb (xlOpen) Set myobj = CreateObject("word.basic") 'creates a Word Basic object With myobj .Insert "Hello"                    'Word Basic commands .Insertpara .Insertpara .Insert "Goodbye" End With

'Sends keystrokes to close Word SendKeys "%fx{TAB}{ENTER}", True Set myobj = Nothing                       'clears the object Range("a1").Select                        'selects cell A1 End Sub

Sub Clearit 'Activates the object ActiveSheet.DrawingObjects("Picture 1").Verb 'Creates a Word Basic object Set myobj = CreateObject("word.basic") With myobj .EditSelectAll                      'Word Basic commands .EditClear End With

Set myobj = Nothing 'clears the object Range("a1").Select 'selects cell A1 End Sub To use these subroutines, do the following to set up the workbook:


 * 1) In Microsoft Excel, from the Insert menu, choose Object.
 * 2) Select the Create New tab. On the Create New tab, select Microsoft Word 6.0 Document from the Object Type list, and choose OK.
 * 3) To return focus to Microsoft Excel, click any cell on the worksheet.

