Linking and Embedding with VBA

The InsertLetter procedure demonstrates how to programmatically embed a Word document in an Excel spreadsheet. Replace the reference to C:\Hello.doc with your own document name. The InsertLetter procedure uses the AddOleObject method. This method creates an OLE object and returns the Shape object that represents the new OLE object. To find additional arguments that the AddOLEObject method can use, look it up in the online Visual Basic documentation.

1. Insert a new module into the current project and rename it OLE.

2. Enter the InsertLetter procedure, as shown below:

Sub InsertLetter()


ActiveSheet.Shapes.AddOLEObject FileName:="C:\Hello.doc"

End Sub

The above procedure opens a new workbook and embeds the indicated Word document in it. To link a document, you must specify an additional argument, Link, as shown below:

ActiveSheet.Shapes.AddOLEObject _

FileName:="C:\Hello.doc", Link:=True

0 0

Post a comment