Posts by ender193

Re: Open embedded OLEObject (Word Doc) Hidden

If you don't activate the object you get:

Quote

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.

Thanks.

Ezra Bowman

Re: Open embedded OLEObject (Word Doc) Hidden

Bump.... really?? no one knows how to do this?

Re: Macro to open Word and Start Mail Merge

Peter,

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)

Quote

a) Open the specific Word document directly from excel

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

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

Quote

b) Run the Mail Merge

Assuming the data source has already been set, you need to do something like this:
[vba]
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:=""
[/vba]

Quote

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.

[vba]
appWD.ActiveDocument.PrintOut
[/vba]

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:
[vba]

Sub MakeRpt()

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

WDObj.Activate
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
[/vba]

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