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:
- In Microsoft Excel, from the Insert menu, choose Object.
- Select the Create New tab. On the Create New tab, select Microsoft Word 6.0 Document from the Object Type list, and choose OK.
- To return focus to Microsoft Excel, click any cell on the worksheet.
REFERENCES
The Microsoft Press book: "Excel 5 Visual Basic for Applications Reference," version 5.0, page 708
For more information about the Verb Method, choose Contents in Help, then choose Programming With Visual Basic and choose Search, and type:
verb
Additional query words: XL
Keywords: KB128711
