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