Posts by ender193

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    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: Macro to open Word and Start Mail Merge


    First, to be able to control Word from Excel you need to add the MS Word Object Library Reference in the VB editor.

    1) Open the excel file and Visual Basic Editor
    2) in the VB Editor, go to Tools - References
    3) find and check the Microsoft Word 11.0 Object Library (or something close)


    a) Open the specific Word document directly from excel

    Here is some code to do this:
    Set appWD = CreateObject("Word.Application")
    appWD.Visible = True

    appWD.Documents.Open Filename:="C:/My Documents/myDoc.doc"


    b) Run the Mail Merge

    Assuming the data source has already been set, you need to do something like this:
    appWd.MailMerge.OpenDataSource Name:="C:\My Documents\mydata.csv" _
    , ConfirmConversions:=False, _
    ReadOnly:=False, LinkToSource:=False, AddToRecentFiles:=False, _
    PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
    WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
    Connection:="", SQLStatement:="", SQLStatement1:=""


    c) Print the results of the document

    Try using something like this. It might bring up the Print dialog, but it'll point you in the right direction.


    Hope this helps!

    Ezra Bowman

    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