Open embedded OLEObject (Word Doc) Hidden

  • I am trying to write a macro that will take data from a worksheet, open an embedded MS Word mail merge document (OLEObject in a worksheet), and perform the merge. I have code that will do all this fine, however, I want the screen to not show anything it's doing until the macro is complete.

    I know how to set Excel ScreenUpdating to False, and I know how to create the MS Word application object and set its Visible property to false. The problem is using an embedded OLEObject on a worksheet that is a word document. The only way I know how to "open" the document is to use the .Activate method, however, this immediatly shows the file on the screen. I can then hide it, perform the merge, and then close the embedded mail merge document, leaving only the merged data word file visible to the user. Is there any way to "open" the embedded file so other code can manipulate it (i.e. perform the merge), without displaying the original file on the screen?

    Here is some of the code I've been using:

    Sub MakeRpt()

    Set WDObj = Sheets("Admin").OLEObjects("TestLabels")

    WDObj.Object.Application.Visible = False

    Call fnLinkData(WDObj) 'This function changes the mail merge data source
    Call fnMergeData(WDObj) 'This function performs the merge in a new doc

    Word.Documents("Document in " & ActiveWorkbook.Name).Close savechanges:=wdDoNotSaveChanges
    Word.Application.Visible = True
    Set WDObj = Nothing

    End Sub

    I am using Windows 2000, Excel 2000, and Word 2000. I plan on distributing this file to several users, and it needs to be as user freindly as possible. That is why I have chosen to embed the mail merge document vice leaving it seperate or linking it, therefore eliminating 'file not found' errors. Let me know if I can provide any other information. Thanks for the help!

    Ezra Bowman

  • Re: Open embedded OLEObject (Word Doc) Hidden

    If you don't activate the object you get:


    Run-time error '1004':

    Unable to get the Object property of the OLEObject class

    It looks like the object's properties are not loaded into memory until it is activated. I would think there should be some sort of option on the .Activate method to make Word open minimized (or hidden), or possibly an alternative to the .Activate method that would load the object properties without displaying the object.


    Ezra Bowman

  • Re: Open embedded OLEObject (Word Doc) Hidden

    I am also facing the same issue.. Is there any alternative without using .Activate method to load the object?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!