Copy content of word OLE object without opening, in Excel with VBA

Question

I have an Excel sheet with Microsoft Word OLE objects embedded.

My embedded Word documents have some fields that should been updated with specific cells.

I need to print the content of that embedded document, without visible document opening and "update document prompts".

My problem is in copying the content of the Word-embedded OLE object in an invisible Word document without update prompting.

I try this:

This code paint a box around the embedded word document in destination printable document.

Please help me copy content of embedded document or ..., and print an embedded document without updating prompt and visible window.

Sub PrintIt(P As String, w, h As Double)
    Dim objWord As Object
    Dim ObjDoc As Object

    Application.ScreenUpdating = False

    Set objWord = CreateObject("Word.Application")
    objWord.Visible = False

    ActiveSheet.OLEObjects(P).Copy

    Set ObjDoc = objWord.Documents.Add
    ObjDoc.PageSetup.PageWidth = objWord.CentimetersToPoints(w)
    ObjDoc.PageSetup.PageHeight = objWord.CentimetersToPoints(h)
    ObjDoc.Content.Paste

    ObjDoc.PrintOut Background:=False
    ObjDoc.PrintOut

    objWord.Quit SaveChanges:=False
    Application.ScreenUpdating = True
End Sub 'Print it

I am not sure if you can do that without opening it? What's wrong with Opening it? — Siddharth Rout
I'm preparing that for print some information on a form. for beauty I need get information from specific cells and print in appropriate place on a form, as invisible and not shown open embedded document and update message. — mgae2m
o form? BTW did you see the link that I posted in the first comment — Siddharth Rout
Yes, I checked that link. I need copy content of my embedded Word document and past that, without opening that embedded OLE. greatly problem in above code is in 'ActiveSheet.OLEObjects(P).Copy'. I need copy content of document, invisible. — mgae2m

mgae2m mgae2m · Accepted Answer · 2017-10-16T11:10:07

Sub PrintIt(P As String)
Dim objWord As Object
Dim ObjDoc As Object
Dim Oshp As Object

Application.ScreenUpdating = False

ActiveSheet.OLEObjects(P).Activate
Set objWord = GetObject(, "Word.Application")
objWord.Visible = False

Set ObjDoc = objWord.ActiveDocument

ObjDoc.Fields.Update
For Each Oshp In ObjDoc.Content.ShapeRange
    Oshp.TextFrame.TextRange.Fields.Update
Next

ObjDoc.PrintOut Background:=False
ObjDoc.PrintOut

objWord.Quit SaveChanges:=False
Application.ScreenUpdating = True
End Sub 'Print it

Copy content of word OLE object without opening, in Excel with VBA

1 Answers