Edit OLE Embedded Word Document In Excel (1 Viewer)

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 07:02
Joined
Jan 20, 2009
Messages
12,542
I am trying to edit a Word form letter embedded in an Excel 2010 spreadsheet.

I seem to be ending up in a state somewhere between what happens when selecting Edit and Open from the Document Object context menu on the embedded object.

With the code below I can get the document into a Word session and initiate either manual or automated editing.

Manual edit updates in both the Word session document and the embedded document but the moment the focus moves away from the document the Word session stops responding. The only way to get back in is to click edit in the Excel object context menu.

With automated editing as shown in the code, the updates appear in the embedded document but not the Word session. It leaves the Word session open and unresponsive despite the Quit. The VBA code appears to complete.

The session will Quit if I leave out the document editing section of the code.

Any ideas most welcome.

Code:
 Dim WordApp As Word.Application
Dim WordDoc As Word.Document
Dim oleObj As OLEObject
  
     Set oleObj = Sheets("Letter").OLEObjects(1)
    oleObj.Verb xlVerbPrimary
    ActiveSheet.Range("A1").Select
       
     Set WordApp = oleObj.Object.Application
 
    With WordApp
        .Visible = True
        .Activate
        Set WordDoc = .Documents(1)
    End With
    
 'do some editing
    With WordDoc
        .Sentences(1).Select
        .ActiveWindow.Selection.TypeText "whatever"
    End With    
    
    WordApp.Quit
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 07:02
Joined
Jan 20, 2009
Messages
12,542
The penny dropped on the way home last night. Amazing how formulating a question here so often points to the solution. Many times I have answered my own question before I hit post. Not quite this time because I had to leave.

This line:
Code:
oleObj.Verb xlVerbPrimary
needed to be:
Code:
oleObj.Verb xlVerbOpen
Primary means the OLEObject is edited in place, the equivalent of Edit on the Document Object context menu.

Combining this with a Word session does indeed leave it stuck between two different states.
 

Users who are viewing this thread

Top Bottom