Word Merge Report

[Wayne311]

I am creating reports usint Word merge. The code opens Word, merges the data and saves the report and opens the merged document. When the document is closed by the user, the instance of WINWORD.EXE continues to run in the Task Manager with no visible document in the task bar. "End Process" in the Task Manager will close it. How can I kill that instance with code?

Here is my code:

Dim strFileNamePath As String
Dim strFileSavePath As String
Dim wdApp As Word.Application
'Dim WordDoc As Object
'Dim strActiveDoc As String
'Dim strDataSourcePath
 
strFileNamePath = "C:\Users\Wayne\Documents\Civilsys\Civis Sys v6\RptMergeTemplates\Merge-Receipt.docx"
strFileSavePath = "C:\Users\Wayne\Documents\Civilsys\Civis Sys v6\RptMergeTemplates\Receipt.docx"
'strDataSourcePath = "C:\Users\Wayne\Documents\Civilsys\Civis Sys v6\CivilSysReceipts.accdb"
 
Set wdApp = CreateObject("Word.Application")
 
wdApp.Documents.Open strFileNamePath 'Open Document in Word
wdApp.Visible = False
 
wdApp.ActiveDocument.MailMerge.OpenDataSource _
Name:=Application.CurrentProject.FullName, _
OpenExclusive:=False, _
LinkToSource:=True, _
Connection:="TABLE tblPrintReceiptTemp", _
SQLStatement:="SELECT tblPrintReceiptTemp.* FROM tblPrintReceiptTemp;"
 
wdApp.ActiveDocument.MailMerge.Execute
wdApp.ActiveDocument.SaveAs strFileSavePath
 
 
 
wdApp.Documents.Open strFileSavePath 'Open saved, merged document
 
Set wdApp = Nothing

Thanks


Wayne

[ajetrumpet]

are you sure your code is not creating 2 instances? have you checked that? check it before the doc is closed.

this might cause it: Dim wdApp As Word.Application

[Wayne311]

With the document open (Receipt.docx), in Task Manager there is only one instance of WINWORD.EXE. Closing the document with the "X" in the upper right leaves the instance of WINWORD.EXE still running making access to the document impossible from then on. If the user uses the menu (upper left) "close" option it closes the document, but not Word. It remains without any document open. If you then close Word with the red "X", Word asks to save the merge template (Merge-Receipt.docx). I don't want users to alter that document.

I have tried the VB "close" command...it does nothing. I need a forced kill command that does not save the document.

Wayne

[ajetrumpet]

my point was that you should NOT have to do this. something is off.

but at any rate, search for it Wayne, and you'll most likely find it: http://www.google.com/#hl=en&sugexp=...1127d0c6f645aa

[Wayne311]

I agree. I was hoping someone had delt with this before and was familar with Word merge reports.

Wayne

[Wayne311]

Adam,

Why do you think that Dim wdApp As Word.Application is the problem? I tried declaring it as an Object with no better results.

Thanks
Wayne

[ajetrumpet]

I don't know Wayne. I don't even work with Access anymore, so I can't remember if a simple dim statement using early binding like that produces an instance of the winword.exe or not. That's why I mentioned it. Using CreateObject() would obviously not create an instance of Word until you got to the SET statement (at least).

Does it create the process after that line executes? If it does, that's the problem, obviously.

[Wayne311]

That Dim statement also has a susequent set statement that creates the Word instance. Set wdApp = CreateObject("Word.Application")

I noticed that my statement: wdApp.Documents.Count has 2 documents with only one instance of WINWORD.EXE and I still cannot get either to close with the "Close" command....the plot thickens.

Wayne

[ajetrumpet]

Wayne,

the 2 document count is probably the one that you tell it to open, plus the "normal.dot" template, which is opened every time you open a word document, regardless of what you open. I'm 99% that's THAT issue, which isn't an issue. It's normal.


Actually, come to think of it, your coding is a little bit off. Usually people use either this:

Dim wdApp As Word.Application
set wdApp as new word.application

OR this:

Dim wdApp As object
set wdApp = createobject("word.application")

You've got those both mixed. You've got a mixture of early binding and late binding.

But regardless, did you step it to make sure I was absolutely wrong? Step over the 'dim' line and check your task mgr.

[Wayne311]

Hmmm...Interesting.

But I just got it resolved. The command: "wdApp.ActiveDocument.Close" did nothing. This one worked: "wdApp.Documents.Close" worked. Here is the code that works.

 Dim strFileNamePath As String
Dim strFileSavePath As String
Dim wdApp As Word.Application
 
strFileNamePath = "C:\Users\Wayne\Documents\Civilsys\Civis Sys v6\RptMergeTemplates\Merge-Receipt.docx"
strFileSavePath = "C:\Users\Wayne\Documents\Civilsys\Civis Sys v6\RptMergeTemplates\Receipt.docx"
 
Set wdApp = CreateObject("Word.Application")
 
wdApp.Documents.Open strFileNamePath 'Open Document in Word
wdApp.Visible = False
 
wdApp.ActiveDocument.MailMerge.OpenDataSource _
Name:=Application.CurrentProject.FullName, _
OpenExclusive:=False, _
LinkToSource:=True, _
Connection:="TABLE tblPrintReceiptTemp", _
SQLStatement:="SELECT tblPrintReceiptTemp.* FROM tblPrintReceiptTemp;"
 
wdApp.ActiveDocument.MailMerge.Execute
wdApp.ActiveDocument.SaveAs strFileSavePath
 
MsgBox (wdApp.Documents.Count)
wdApp.ActiveDocument.Close
 
 
 
' If the wdApp object exists
If Not wdApp Is Nothing Then
 
' If the Document exists
If wdApp.Documents.Count > 0 Then
 
' Close the Document(s)
wdApp.Documents.Close
 
End If
End If
 
wdApp.Documents.Open strFileSavePath 'Open saved, merged document
 
'Set wdApp = Nothing

Nothing is left hanging in the Task Manager.

Only the "Set wdApp = Nothing" is not working.

Wayne

[Wayne311]

Here is the source I was using for the Dim and Set statements:

And here is a VBA method, which does support all datatypes using Bookmarks:
Private Sub cmdMergeLetter_Click()' © 1999 Arvin Meyer' This code was written by Arvin Meyer' It is not to be altered or distributed,' except as part of an application.' You are free to use it in any application,' provided the copyright notice is left unchanged.

On Error GoTo Err_cmdMergeLetter_Click 
Dim WordTemplate As String 
Dim strFullName As String 
Dim objWord As Word.Application 
Set objWord = CreateObject("Word.Application")

[ajetrumpet]

well good for you! =) I guess the only thing left is that "nothing" stuff. don't forget to kill the vars with that, unless the app is small and memory usage is not an issue.

[Wayne311]

So we should kill our vars...that sound cruel!

You mean something like: Set YourVar = Nothing

I have not seen that much.

Wayne

[ajetrumpet]

not seen what much?

You'd be surprised how much memory large VBA scripts take up when lots of vars are decked.

Yes, kill the vars once you've used them up, but dec them first. Wow...I'm almost afraid someone's gonna chime in here and say they're filing a lawsuit for intolerable cruelty to non-human species!