I’ve got this app that’s driving me nuts. Excel is automating Word to do a mail merge. The problem is after doing the merge and quitting excel, the task manager show excel still running in the back ground. Can one of you wonder Office guru’s point me in the right direction.

Here is what I’ve pretty much proven to myself. The excel file has a bunch of forms and only 2 sheets. If I don’t do the mail merge there is no problem when quitting excel. So I’m sure the problem lies in the mail merge somewhere. If I use my normal code to open word and a non mail merge doc, there is no problem. If only happens when I do the merge.

Here is what I think is the possible code problem areas.

In each word mail merge doc that excel opens the following code is in the doc open.
(I did this so they can look at any of the docs without having to go through my code.)

Private Sub Document_Open()
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:= _
        "C:\mydir\mydata.xls" _
        , ConfirmConversions:=False, ReadOnly:=True, LinkToSource:=True, _
        AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
        WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
        Format:=wdOpenFormatAuto, Connection:= _
        "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\mydir\mydata.xls;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet O" _
        , SQLStatement:="SELECT * FROM `PrintOut`", SQLStatement1:="", _
        OpenExclusive:=False, SubType:=wdMergeSubTypeAccess
End Sub

I’ve tried adding the following code to the word document close with no effect.

Private Sub Document_Close()
   ActiveDocument.MailMerge.DataSource.Close
End Sub

Here is the excel code that launches word and automates it. I’m guessing that this is ok because if I have it run using a non-mail merge doc I don’t see the problem. But at this point I’m really not sure.

Sub StepOne()

    strpath = "C:\mydir\"

'    'Start Word

    Dim ox As Object
     Set ox = CreateObject("word.application")
     ox.Visible = True
     ox.Options.DefaultOpenFormat = 1

     'Open MyMailmerge.com

     ox.Documents.Open Filename:=strpath + "mymailmerge.doc"

     'ox.Documents.Open Filename:=strpath + "mynonmailmerge.doc"

     ox.Selection.WholeStory
     ox.Selection.Copy
     ox.activedocument.Close
     'Open A new blank doc
     Set docNew = ox.Documents.Add
     tempstr = strpath + "mydata.xls"
     docNew.mailmerge.MainDocumentType = 0
     'Set Data source in new doc
     docNew.MailMerge.OpenDataSource Name:="" + tempstr + "", _
     ReadOnly:=True, _
     Connection:= _
     "Provider=MSDASQL.1;Persist Security Info=True;Extended   Properties=""DSN=Excel Files;DBQ=C:\mydir\mydata.xls;DefaultDir=C:\mydir;DriverId=790;MaxBuf" _
     , SQLStatement:="SELECT * FROM `PrintOut`", SQLStatement1:=""

'     'Paste Check set to new doc

     Set Range2 = docNew.Content
     Range2.Collapse Direction:=0
     Range2.Paste

     With ox.activedocument.MailMerge
         .Destination = 1
         .SuppressBlankLines = True
         With .DataSource
             .FirstRecord = 1
             .LastRecord = -16
          End With
         .Execute Pause:=False
     End With

     ox.activedocument.Close SaveChanges:=0
     Set docNew = Nothing
     Set Range2 = Nothing
     
     ox.Quit
     Set ox = Nothing
End Sub