Process 'excel.exe' doesn't release (Merge procedure from Word)

[SuperMaximus]

I have read all of the similar threads and haven't found a solution of the problem of Excel.exe process running in Task Explorer after closure of both Word & Excel Applications.

As you see in the code below I've set WordApp and WordDoc both to 'Nothing' at the end of macro.

Option Explicit 
 
Sub RunContract_Automation() 
     
     'Dim Fs As Variant
    Dim ContractPath As String 
    Dim WordApp As Word.Application 
    Dim WordDoc As Word.Document 
    Dim CType As String, CFile As String 
     
     
    Set WordApp = CreateObject("Word.Application") 
    Set WordDoc = WordApp.Documents.Open _ 
    (ThisWorkbook.Path & "\" & CFile) 
    WordApp.Visible = True 
     
     
    With WordApp.Application 
         
         '=========macro of Merge procedure==========
         
        .ActiveDocument.MailMerge.MainDocumentType = wdFormLetters 
        .ActiveDocument.MailMerge.OpenDataSource name:=ActiveDocument.Path & "\WORK.xls", _ 
        ConfirmConversions:=False, ReadOnly:=False, 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=WORK.xls;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=35;Je" _ 
        , SQLStatement:="SELECT * FROM Import$`", SQLStatement1:="", SubType:= _ 
        wdMergeSubTypeAccess 
         
        .ActiveDocument.MailMerge.Destination = wdSendToNewDocument 
        .ActiveDocument.MailMerge.SuppressBlankLines = True 
        .ActiveDocument.MailMerge.DataSource.FirstRecord = ActiveDocument.MailMerge.DataSource.ActiveRecord 
        .ActiveDocument.MailMerge.DataSource.LastRecord = ActiveDocument.MailMerge.DataSource.ActiveRecord 
        .ActiveDocument.MailMerge.Execute Pause:=False 
         '=============================================================
         
         
         
        With .Dialogs(wdDialogFileSaveAs) 
            .name = "D:\NEW\" & ContractName 
            .Show 
        End With 
         
         '    .Quit SaveChanges:=wdDoNotSaveChanges
         
    End With 
     
    WordApp.Quit SaveChanges:=wdDoNotSaveChanges 
     
    Set WordApp = Nothing 
    Set WordDoc = Nothing 
     
End Sub

This macro invokes the procedure of Merge Data (from also opened in this macro Merge-source DOC file with mergefields), asks user to save the result of Merge and then closes both source and result files.
This macro is being called from Excel.

As the result, I always see 'Excel.exe' process running in the memory even when I close Excel and Word Applications either.

If I change all the code related to Word Merge Procedure to smth like MsgBox "Hello", everything goes right and excel.exe process terminates when applications close...
Consequently, the problem hides in impossibility of explicit Word file closure. This is my assumption.

You can try to invoke the Merge procedure yourself by the similar scheme and you'll be surprised when you face the same problem.

Please, help me to comprehend this difficulty and to get rid of unnecessary 'Excel.exe' running...


p.s. I also used early binding instead Set WordApp = CreateObject("Word.Application") - it didn't help.


Thanks in advance!

 

[PHV]

First implicit instantiation of word found here:
.ActiveDocument.MailMerge.OpenDataSource name:=[highlight].[/highlight]ActiveDocument.Path & "\WORK.xls", _

Lot of corrections to do ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SuperMaximus]

PHV, unfortunately... it didn't help.
I've made everything look explicit

    .Documents("CONTRACT.doc").MailMerge.MainDocumentType = wdFormLetters
    .Documents("CONTRACT.doc").MailMerge.OpenDataSource name:="D:\AWB\WORK.xls", _
        ConfirmConversions:=False, ReadOnly:=False, 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=WORK.xls;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=35;Je" _
        , SQLStatement:="SELECT * FROM `??????$`", SQLStatement1:="", SubType:= _
        wdMergeSubTypeAccess

        .Documents("CONTRACT.doc").MailMerge.Destination = wdSendToNewDocument
        .Documents("CONTRACT.doc").MailMerge.SuppressBlankLines = True
        .Documents("CONTRACT.doc").MailMerge.DataSource.FirstRecord = .Documents("CONTRACT.doc").MailMerge.DataSource.ActiveRecord
        .Documents("CONTRACT.doc").MailMerge.DataSource.LastRecord = .Documents("CONTRACT.doc").MailMerge.DataSource.ActiveRecord
        .Documents("CONTRACT.doc").MailMerge.Execute Pause:=False

Excel.exe still "hangs" in the memory when I close all apps.
I wonder if You could possibly point my nose directly at my mistake(s). I'm so stuck with this problem, I can't find the solution.

 

[PHV]

The end of the connection string seems suspect:
;Jet OLEDB:Engine Type=35;[!]Je[/!]" _

You don't have any On Error Resume Next instruction ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SuperMaximus]

No, I don't.

This

;Jet OLEDB:Engine Type=35;Je" _

was automatically recordered by MS Word.
I have no idea of what "Je" can be...

But after recording the macro of Merge procedure I got
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=D:\AWB\WORK.xls;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDBatabase Password="""";Jet OLEDB:Engine Type=3" _
, SQLStatement:="SELECT * FROM `??????$`", SQLStatement1:="", SubType:= _
wdMergeSubTypeAccess

without that suspicious ending "Je"... Nevertheless Excel.exe still stays in the memory
It's not a particular case.
It happens everytime I call the Word Merge procedure from Excel macro.

 

[SuperMaximus]

I Found the reason for excel.exe process running !!!!

Here, again, I'll post the code of a completed sub:

Sub RunContract_Automation()

  Dim ContractPath As String
  Dim WordApp As Word.Application
  Dim WordDoc As Word.Document
  Dim wrdMailMerge As Word.MailMerge
  Dim wrdDialogs As Word.Dialogs
  
  
  Set WordApp = CreateObject("Word.Application")
  WordApp.Visible = True
  Set WordDoc = WordApp.Documents.Open _
      (ThisWorkbook.Path & "\" & CFile)
  Set wrdMailMerge = WordDoc.MailMerge
  Set wrdDialogs = WordApp.Dialogs

    
    wrdMailMerge.MainDocumentType = wdFormLetters
    wrdMailMerge.OpenDataSource name:="D:\AWB\WORK.xls", _
        ConfirmConversions:=False, ReadOnly:=False, 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=D:\AWB\WORK.xls;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=3" _
        , SQLStatement:="SELECT * FROM `Import$`", SQLStatement1:="", SubType:= _
        wdMergeSubTypeAccess
    
        wrdMailMerge.Destination = wdSendToNewDocument
        wrdMailMerge.SuppressBlankLines = True
        wrdMailMerge.DataSource.FirstRecord = wrdMailMerge.DataSource.ActiveRecord
        wrdMailMerge.DataSource.LastRecord = wrdMailMerge.DataSource.ActiveRecord
        wrdMailMerge.Execute Pause:=False


    With wrdDialogs(wdDialogFileSaveAs)
         .name = "D:\INS\" & ContractName
         .Show
    End With

    
    ContractPath = WordApp.ActiveDocument.Path

  
  Set wrdMailMerge = Nothing
  Set wrdDialogs = Nothing
  Set WordDoc = Nothing
  Set WordApp = Nothing

End Sub

It hides HERE:

    With wrdDialogs(wdDialogFileSaveAs)
         .name = "D:\INS\" & ContractName
         .Show
    End With

When I exclude this code from my subroutine, excel.exe doesn't disturb me anymore in TaskManager.
However, The procedure of Saving the document is obligatory and i need SaveAs menu to appear! But how can I do that without letting excel.exe appear again?
I think now every declaration and addressing is explicit.

Please help me to solve that!
Thanks in advance!

 

[PHV]

I don't see a line like this:
WordApp.Quit

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SuperMaximus]

PHV,
What if I don't want to close Word Application?
It the case described above (without calling .Dialogs) when I close Excel application the corresponding process excel.exe disappears from memory.

Anyway, adding WordApp.Quit along with the usage of

With wrdDialogs(wdDialogFileSaveAs)
         .name = "D:\INS\" & ContractName
         .Show
    End With

doesn't help. I see annoying excel.exe in TaskManager after closing Excel.

Any ideas on invoking SaveAs dialog in another way?

 

[SuperMaximus]

The problem hides in ".Show" command.
If I use ".Execute" I don't see annoying excel.exe process in TaskManager anymore, nevertheless I need to see the dialog (which can't be seen when I use ".Execute").

I can't believe there's no way out.