P
Peter Graney
Option Explicit
'2004/08/11
'
'Peter Graney
'Carillon Consulting, Inc.
'(e-mail address removed)
'
'Acknowledgements: Sam <[email protected]>
'
'Definitive Example
'Control a Word Document that is embedded in Excel using
VBA
'Keywords: Microsoft
' Excel
' Word
' Embed, Embedded
' Document
' Automate
' Control
'
'
'
'Steps:
'1) Create a new Word Document.
'2) Insert a single word field by typing [Ctrl]+[F9].
'3) Save document. (Keep track of document's path and
filename
'4) Exit Word.
'5) Create New Excel Workbook
'6) On "Sheet1"
'7) From Menu execute: Insert|Object|Create From File
'8) Enter the full path and filename of document
created in step 3.
'9) Do not link to file.
'10) Select the Embedded Object using your mouse.
'11) Display Formula Toolbar if it is not already
visible
'12) The control on the left of the Formula Toolbar is
the Range Name/Object Name
' Box.
'13) Using your mouse, click in the box
'14) Rename the embedded object from "Object 1"
to "Object_Blah"
'15) Open the VBA Editor
'16) From Menu execute: Tools|References...|
'17) Scroll down to: "Microsoft Word *.0 Object Library"
' (where * is either 7, 8, 9, 11 etc...; Word 2000
uses the 9.0 Object Library)
'18) Open the VB Project Explorer [Ctrl]+[R]
'19) Double click "ThisWorkbook" to open the code window
'20) Copy this code into the code window
'21) Go back to the Excel Workbook and execute the
Macro "Populate"
Public Sub Populate()
Dim o_Embedded_Word_Document As OLEObject
Dim o_App_Word As Word.Application
Dim o_Word_Document As Word.Document
Dim s_Insert_Text As String
On Error GoTo Populate_Error
Application.Cursor = xlWait
Application.ScreenUpdating = False
'Remove comment from this line when you no longer need
to debug procedure.
'Application.EnableCancelKey = xlDisabled
'Grab a handle to the embedded MS Word Document
Set o_Embedded_Word_Document = ThisWorkbook.Worksheets
("Sheet1").OLEObjects("Object_Blah")
'The Activate method launches an instance of MS Word
in the background
o_Embedded_Word_Document.Activate
'Sets a pointer to MS Word
Set o_App_Word =
o_Embedded_Word_Document.Object.Application
'Sets a pointer to the ActiveDocument. This is the
actual Word document file
'inserted in step 7
Set o_Word_Document = o_App_Word.ActiveDocument
'For demonstration purposes only.
'Request value to place in the word document field.
While Not IsNumeric(s_Insert_Text)
s_Insert_Text = InputBox("Enter number to insert
into Word field: ")
Wend
'Place value in field. Update fields
With o_Word_Document
.Fields(1).Code.Text = "=" & Val(s_Insert_Text)
& "\# ""$#,##0.00;($#,##0.00)"""
.Fields.Update
End With
Populate_Exit:
On Error Resume Next
'Selecting Range("A1") removes Object_Blah from Focus
and closes the Word
'Document and Application
ThisWorkbook.Worksheets("Sheet1").Range("A1").Select
'Clean up
Set o_Word_Document = Nothing
Set o_App_Word = Nothing
Set o_Embedded_Word_Document = Nothing
Application.ScreenUpdating = True
Application.Cursor = xlDefault
Exit Sub
Populate_Error:
MsgBox Err.Number & ": " & Err.Description, vbOKOnly +
vbCritical, "Error..."
GoTo Populate_Exit:
End Sub
'2004/08/11
'
'Peter Graney
'Carillon Consulting, Inc.
'(e-mail address removed)
'
'Acknowledgements: Sam <[email protected]>
'
'Definitive Example
'Control a Word Document that is embedded in Excel using
VBA
'Keywords: Microsoft
' Excel
' Word
' Embed, Embedded
' Document
' Automate
' Control
'
'
'
'Steps:
'1) Create a new Word Document.
'2) Insert a single word field by typing [Ctrl]+[F9].
'3) Save document. (Keep track of document's path and
filename
'4) Exit Word.
'5) Create New Excel Workbook
'6) On "Sheet1"
'7) From Menu execute: Insert|Object|Create From File
'8) Enter the full path and filename of document
created in step 3.
'9) Do not link to file.
'10) Select the Embedded Object using your mouse.
'11) Display Formula Toolbar if it is not already
visible
'12) The control on the left of the Formula Toolbar is
the Range Name/Object Name
' Box.
'13) Using your mouse, click in the box
'14) Rename the embedded object from "Object 1"
to "Object_Blah"
'15) Open the VBA Editor
'16) From Menu execute: Tools|References...|
'17) Scroll down to: "Microsoft Word *.0 Object Library"
' (where * is either 7, 8, 9, 11 etc...; Word 2000
uses the 9.0 Object Library)
'18) Open the VB Project Explorer [Ctrl]+[R]
'19) Double click "ThisWorkbook" to open the code window
'20) Copy this code into the code window
'21) Go back to the Excel Workbook and execute the
Macro "Populate"
Public Sub Populate()
Dim o_Embedded_Word_Document As OLEObject
Dim o_App_Word As Word.Application
Dim o_Word_Document As Word.Document
Dim s_Insert_Text As String
On Error GoTo Populate_Error
Application.Cursor = xlWait
Application.ScreenUpdating = False
'Remove comment from this line when you no longer need
to debug procedure.
'Application.EnableCancelKey = xlDisabled
'Grab a handle to the embedded MS Word Document
Set o_Embedded_Word_Document = ThisWorkbook.Worksheets
("Sheet1").OLEObjects("Object_Blah")
'The Activate method launches an instance of MS Word
in the background
o_Embedded_Word_Document.Activate
'Sets a pointer to MS Word
Set o_App_Word =
o_Embedded_Word_Document.Object.Application
'Sets a pointer to the ActiveDocument. This is the
actual Word document file
'inserted in step 7
Set o_Word_Document = o_App_Word.ActiveDocument
'For demonstration purposes only.
'Request value to place in the word document field.
While Not IsNumeric(s_Insert_Text)
s_Insert_Text = InputBox("Enter number to insert
into Word field: ")
Wend
'Place value in field. Update fields
With o_Word_Document
.Fields(1).Code.Text = "=" & Val(s_Insert_Text)
& "\# ""$#,##0.00;($#,##0.00)"""
.Fields.Update
End With
Populate_Exit:
On Error Resume Next
'Selecting Range("A1") removes Object_Blah from Focus
and closes the Word
'Document and Application
ThisWorkbook.Worksheets("Sheet1").Range("A1").Select
'Clean up
Set o_Word_Document = Nothing
Set o_App_Word = Nothing
Set o_Embedded_Word_Document = Nothing
Application.ScreenUpdating = True
Application.Cursor = xlDefault
Exit Sub
Populate_Error:
MsgBox Err.Number & ": " & Err.Description, vbOKOnly +
vbCritical, "Error..."
GoTo Populate_Exit:
End Sub